重建outln使用者指令碼(不知道以前在什麼地方看到的，現貼出共享)

-- createoutln.sql的詳細指令碼

[@more@]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;
/