今天无缘无故的outln不见了，后台alter日志也没有删除的信息。重新创建也创建不上

create user outln identified by outln


*


ERROR at line 1:


ORA-00604: error occurred at recursive SQL level 1


ORA-18008: cannot find OUTLN schema


很是郁闷，不见了居然还不让创建。


只能通过后台的dump文件来查看原因了


sqlplus / as sysdba


alter session set events '18008 trace name errorstack level 3';


alter session set events '10046 trace name context forever, level 12';


create user outln identified by outln;


通过跟踪后台日志查看。


知道是这个trigger的问题：MDSYS.SDO_ST_SYN_CREATE


alter trigger MDSYS.SDO_ST_SYN_CREATE disable;


create user outln identified by outln;


alter trigger MDSYS.SDO_ST_SYN_CREATE enable;


这个以后就不会出现ORA-18008: cannot find OUTLN schema这个问题了。


附上：OUTLN用户的创建语句


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','OL$NODES') and owner#= 
(select user# from user$ where name='OUTLN');


select count(*) into extra_outln_tables from obj$ where name not in 
('OL$', 'OL$HINTS','OL$NODES') 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, ' || 
'hash_value2 number, ' || 
'category varchar2(30), ' || 
'version varchar2(64), ' || 
'creator varchar2(30), ' || 
'timestamp date, ' || 
'flags number, ' || 
'hintcount number, ' || 
'spare1 number, ' || 
'spare2 varchar2(1000))', 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, '|| 
'ref_id number, '|| 
'user_table_name varchar2(64), '|| 
'cost FLOAT(126),'|| 
'cardinality FLOAT(126),'|| 
'bytes FLOAT(126),'|| 
'hint_textoff number, '|| 
'hint_textlen number,'|| 
'join_pred varchar2(2000),'|| 
'spare1 number, '|| 
'spare2 number, '|| 
'hint_string clob)', dbms_sql.native); 
dbms_sql.parse(DDL_CURSOR, 'create table outln.ol$nodes ( '|| 
'ol_name varchar2(30), '|| 
'category varchar2(30), '|| 
'node_id number, '|| 
'parent_id number, '|| 
'node_type number, '|| 
'node_textlen number, '|| 
'node_textoff number, '||
'node_name varchar2(64))', 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!=3 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; 
/


alter user outln account lock;


10g R2的outln用户的文档


Subject:    Script. to create user OUTLN in 10.2
      Doc ID:    422983.1    Type:    SCRIPT
      Modified Date :    04-SEP-2008    Status:    PUBLISHED


9i的参考文档如下：
Subject:    Script. to create user OUTLN in 9i
      Doc ID:    240478.1    Type:    SCRIPT
      Modified Date :    08-DEC-2008    Status:    PUBLISHED


8i的参考文档如下：
Subject:    Script. to create user OUTLN in 8i
      Doc ID:    98572.1    Type:    BULLETIN
      Modified Date :    10-JUN-2003    Status:    PUBLISHED