linux18008端口无法访问,ORA-18008: cannot find OUTLN schema问题处理

腊月二十，快过年了接到某用户现场同事的电话，数据库无法使用，感觉很揪心，年前问题不断。。。

处理过程做个记录。

微信发过来的图片如下

据说是操作系统重启后就成了这个样子，可以登录，但是操作数据即会报这个错误。

根据错误提示是OUTLN用户神秘失踪了，现场是数据库版本为10.2

随远程协助其进行了处理，重建OUTLN用户

mos上解决办法如下：

Solution

Disable the problem trigger.

alter trigger MDSYS.SDO_ST_SYN_CREATE disable;https://www.cndba.cn/hehdba/article/3993

Create OUTLN user. (use Note 422983.1 or pull sql from sql.bsq file, in 11.2 the doptim.bsq file)

create user outln identified by outln;

Re-enable the trigger.

alter trigger MDSYS.SDO_ST_SYN_CREATE enable;

https://www.cndba.cn/hehdba/article/3993https://www.cndba.cn/hehdba/article/3993

按照文档10.2版本需要使用Doc ID 422983.1中的脚本

脚本如下：

set serveroutput on https://www.cndba.cn/hehdba/article/3993

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’; https://www.cndba.cn/hehdba/article/3993

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; https://www.cndba.cn/hehdba/article/3993

END;

/

参考Recreating Dropped Outln Schema Errors With Ora-00604 And Ora-18008 (Doc ID 790116.1)

参考Script to Create User OUTLN in 10.2 (Doc ID 422983.1)https://www.cndba.cn/hehdba/article/3993https://www.cndba.cn/hehdba/article/3993

版权声明：本文为博主原创文章，未经博主允许不得转载。