【故障处理】ORA-18008: cannot find OUTLN schema暨OUTLN用户的创建

有同事反映，在sqlplus中使用connect连接到具体用户时连续报下面的错误：“ORA-18008: cannot find OUTLN schema”和“SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.”。
现将这个问题的现象、原因和处理方法记录在此。

1.问题现象
当使用sqlplus登陆数据库之后，无法使用connect连接到具体的用户中，报错信息如下
ora10g@secDB /home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 18 10:35:54 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.



Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options


sys@ora10g>
sys@ora10g> conn sec/sec
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
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.
Connected.
sec@ora10g>
使用oerr工具查看一下上面两个报错信息的解释
sys@ora10g> !oerr ora 18008
18008, 00000, "cannot find OUTLN schema"
// *Cause:  The database creation script. that creates this schema must not
//          have been executed.
// *Action: Review the log files to see what happened when the database
//          was created.

sys@ora10g> !oerr sp2 0575
00575,0, "Use of Oracle SQL feature not in SQL92 %s Level.\n"
// *Cause:  A SQL statement was attempted that is not FIPS compliant.
//          May also occur if a SQL*Plus feature e.g. SET AUTOTRACE
//          that uses Oracle-specific SQL was turned on when you are
//          using FIPS flagging.
// *Action: Use SET FLAGGER and turn FIPS compliance checking OFF,
//          or rewrite the statement.


2.问题原因
OUTLN用户被误删除。

3.处理方法
手工创建这个OUTLN用户，并赋予需要的权限。
1）创建OUTLN用户
sys@ora10g> create user outln identified by outln default tablespace system temporary tablespace temp profile default account unlock;

User created.

2）授予角色
sys@ora10g> grant resource to outln;

Grant succeeded.

sys@ora10g> alter user outln default role all;

User altered.

3）授予系统权限
sys@ora10g> grant unlimited tablespace, execute any procedure, create session to outln;

Grant succeeded.

4）授予对象角色，将SYS用户下的OUTLN_PKG包的执行权限授权给OUTLN
sys@ora10g> grant execute on sys.outln_pkg to outln;

Grant succeeded.

创建完这个用户后，也可以考虑将另外一个正常库的OUTLN用户下的数据库对象迁移到这个用户中。

再次测试连接性，问题已经得到有效解决。
sys@ora10g> conn sec/sec
Connected.

4.除了上面的方法外，创建OUTLN用户也可以参考Metalink上的脚本来完成
1）10gR2上的创建脚本可以从Metalink的这个文章中得到
Subject:     Script. to create user OUTLN in 10.2
      Doc ID:     422983.1     Type:     SCRIPT
      Modified Date :     04-SEP-2008     Status:     PUBLISHED

2）整理后的脚本如下：
ora10g@secDB /home/oracle$ cat cre_outln102.sql
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;
/      

3）使用上面的脚本进行创建
sys@ora10g> @cre_outln102.sql
OUTLN CREATION SUCCESSFUL

PL/SQL procedure successfully completed.

这种创建方法既高效又完整。并且其中也蕴含着很多小技巧，慢慢体会吧。

4）如果您的数据库版本是8i和9i的，可以参考Metalink上关于8i和9i创建OUTLN用户的脚本。
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      

5.就这个OUTLN用户扩展一下，有没有想知道OUTLN这个用户存在意义是什么呢？任何事物都是有存在意义的。
摘录Metalink上有关OUTLN的一系列疑问的Q&A，其中描述的非常的清楚，我就不再赘述了。请阅……
Subject:     What is the OUTLN User?
      Doc ID:     1071358.6     Type:     BULLETIN
      Modified Date :     30-MAR-2009     Status:     PUBLISHED

What is the OUTLN user?
=======================
 
This document will address these topics:

1. Why is the user OUTLN created by Oracle?
2. What are stored outlines and what are they good for?
3. What does OUTLN user own?
4. If it is dropped by accident, can it be created on the fly?


1. Why is the user OUTLN created by Oracle?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The OUTLN user is created automatically during installation of Oracle.
The create user outln statement appears in SQL.BSQ that is run at database
creation time to initiate the datadictionary, as such the OUTLN user is an
integral part of the database. This user is granted connect, resource, and
execute any procedure privileges. It is also set to locked and expired
since no end-user connections should be made to this acount except for
maintenance. The database administrator should change the password for
the OUTLN schema just as for the SYS and SYSTEM schemas, also make sure the
account is locked and only unlock it in case a DBA needs access to it for
maintenance operations.


2. What are stored outlines and what are they good for?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Stored outlines support a very usefull feature: Optimizer Plan Stability

After carefully tuning an application, you might want to ensure that the
optimizer generates the same execution plan whenever the same SQL statements
are executed. Plan stability allows you to maintain the same execution plans
for the same SQL statements, regardless of changes to the database such as
re-analyzing tables, adding or deleting data, modifying a table's columns,
constraints, or indexes, changing the system configuration, or even upgrading
to a new version of the optimizer.

The CREATE OUTLINE statement creates a stored outline, which contains a set of
attributes that the optimizer uses to create an execution plan. Stored outlines
can also be created automatically by setting the system parameter
CREATE_STORED_OUTLINES to TRUE.

The system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or a
category name to indicate whether to make use of existing stored outlines for
queries that are being executed. The OUTLN_PKG package provides procedures used
for managing stored outlines.
 
Oracle adds the OUTLN user schema to support Plan Stability. The OUTLN user
acts as a place to centrally manage metadata associated with stored outlines.
 
You cannot create this user on the fly. The user OUTLN makes use of the package
OUTLN_PKG which is used to manage stored outlines and their outline categories.

The package sys.outln_pkg is created by script. "dbmsol.sql" in the
$ORACLE_HOME/rdbms/admin directory. The "dbmsol.sql" script. is called from
"catproc.sql". "prvtol.plb" creates the body of "outln_pkg"; it is also called
from catproc.sql.


3. What does OUTLN user own?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The outline user ows some tables that are the stored outlines metadata
repository, typically these are OL$, OL$HINTS and OL$NODES and some indexes.

In case of an upgrade or migration, the upgrade or migration scripts will take
care of any changes to the OUTLN respository tables and associated package.

4. If it is dropped by accident, can it be created on the fly?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

First of all: you should never try to drop the OUTLN user (in the same way as you
should not drop the SYSTEM user) but if you accidently did you can recreate
it by running the same commands from the file sql.bsq again to create the outln
user (do NOT run sql.bsq directly!), issue grants, create tables and indexes,
note that "M_IDEN" stands for the length of an identifier which is 30 for current
releases, "M_CSIZ" is 2000. For some versions support has created an automated
script. to restore the outln schema and its contents.

Naturally you will have lost all stored outlines when you drop the OUTLN schema,
also, since the kernel makes implicit assumptions on its existence , you will get
the error ORA-18009 "one or more outline system tables do not exist" when you try
to issue outline related sql statements when the OUTLN schema is not there.

6.小结
通过这么一个小小的问题就可以引申出那么多有趣的知识，所以说：技术是一个自我陶醉的过程。
这里只是抛个砖，更多的知识请朋友们一同来发掘。

故障处理结论：千万不要再有意人为的将OUTLN用户drop掉了。     

Goodluck to you.

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-615000/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-615000/