ORA-18008: cannot find OUTLN schema

最新推荐文章于 2021-04-13 19:54:45 发布

转载最新推荐文章于 2021-04-13 19:54:45 发布 · 170 阅读

文章标签：

#数据库

本文提供了一个脚本，用于在Oracle 10.2环境中重新创建被意外删除的OUTLN用户及其相关表。此脚本必须由SYSDBA执行，并确保OUTLN账号被锁定。

今天在10g中不小心把OUTLN schema刪除了﹐導致不能執行SQL語句。錯誤如下

ERROR:
ORA-18008: cannot find OUTLN schema

Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.

Script. to create user OUTLN in 10.2 [ID 422983.1]

		修改时间 24-NOV-2009 类型 SCRIPT 状态 PUBLISHED

In this Document
  Purpose
  Software Requirements/Prerequisites
  Configuring the Script
  Running the Script
  Caution
  Script
  Script. Output
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1
Information in this document applies to any platform.
The scope of this document is for RDBMS release 10.2.X only.

Purpose

This script. is to create the OUTLN user in Oracle 10.2 if accidently dropped with CASCADE option.

See also note 240478.1 script. to create user OUTLN in 9i for comparision, for Oracle 10.2, we add a new column hint_string clob to outln.ol$hints table (this does not apply to 10.1) and NODE_NAME VARCHAR2(64) to OL$NODES.

Software Requirements/Prerequisites

Oracle 10.2

Configuring the Script

This script. MUST be run when connected AS SYSDBA. Make sure to lock the OUTLN account also.

Running the Script

Run the script.

Caution

This script. is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected.

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','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;

Script. Output

SQL> @cre_outln102
OUTLN CREATION SUCCESSFUL

PL/SQL procedure successfully completed.

References

NOTE:1071358.6 - What is the OUTLN User?
NOTE:240478.1 - Script. to create user OUTLN in 9i
NOTE:98572.1 - Script. to create user OUTLN in 8i
ORA-04063: view "SYS.USER_OUTLINE_HINTS" has errors

显示相关信息 相关的