!hello
Accidentaly I drop OUTLN Schema .
Is there is any script to create OUTLN Schema. or else any other solution
Please help.
Printable View
!hello
Accidentaly I drop OUTLN Schema .
Is there is any script to create OUTLN Schema. or else any other solution
Please help.
This script will only work if the user OUTLN has been dropped
'cascade' from the database.
This script MUST be run as the user INTERNAL.
This script was extracted from C0800050.sql
Version Testing:
This script was tested on Oracle V8.1.5.
Note:
After running this script, the user will need to run
catalog.sql and catproc.sql. These scripts must be run
as the user SYS or INTERNAL.
Script:
***************** Begin Script ***********************
set serveroutput on
DECLARE
user_exists EXCEPTION;
outln_user number;
outln_tables number;
extra_outln_tables number;
DDL_CURSOR integer;
BEGIN
select count(*) into outln_user from user$ where name='OUTLN';
select count(*) into outln_tables from obj$ where name in
('OL$', 'OL$HINTS') and owner#=
(select user# from user$ where name='OUTLN');
select count(*) into extra_outln_tables from obj$ where name not in
('OL$', 'OL$HINTS') and type#=2 and owner#=
(select user# from user$ where name='OUTLN');
DDL_CURSOR := dbms_sql.open_cursor;
IF outln_user = 0 THEN
dbms_sql.parse(DDL_CURSOR, 'create user outln identified by outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR,
'grant connect, resource, execute any procedure to outln',
dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$ ( '||
'ol_name varchar2(30), ' ||
'sql_text long, ' ||
'textlen number, ' ||
'signature raw(16), ' ||
'hash_value number, ' ||
'category varchar2(30), ' ||
'version varchar2(64), ' ||
'creator varchar2(30), ' ||
'timestamp date, ' ||
'flags number, ' ||
'hintcount number)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$hints ( '||
'ol_name varchar2(30), '||
'hint# number, '||
'category varchar2(30), '||
'hint_type number, '||
'hint_text varchar2(512), '||
'stage# number, '||
'node# number, '||
'table_name varchar2(30), '||
'table_tin number, '||
'table_pos number)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$name '||
'on outln.ol$(ol_name)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$signature '||
' on outln.ol$(signature,category)', dbms_sql.native);
dbms_sql.parse(DDL_CURSOR, 'create unique index outln.ol$hnt_num '||
' on outln.ol$hints(ol_name, hint#)', dbms_sql.native);
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
ELSE
IF outln_tables!=2 or extra_outln_tables!=0 THEN
dbms_output.put_line('ERROR - OUTLN USER ALREADY EXISTS');
RAISE user_exists;
ELSE
dbms_output.put_line('OUTLN CREATION SUCCESSFUL');
END IF;
END IF;
EXCEPTION
WHEN user_exists THEN
RAISE;
END;
/
This is a script from Oracle metalink, it should do the job.
Shaz
Thank's a lot to SAZZADUR.
u gave correct and right solution for me .It work perfectly on Oracle 9i also.
Onces again thank'U
it my id :: akram_tri@yahoo.co.in