ORA-18008: cannot find OUTLN schema(一)

今天无缘无故的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 o