今天在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
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
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
相关的 产品
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7608831/viewspace-681655/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7608831/viewspace-681655/