ORA-18008: cannot find OUTLN schema

ORA-18008: cannot find OUTLN schema

Oracle 9 and over, method 1

CREATE_EDIT_TABLES Procedure

This procedure creates outline editing tables in calling a user's schema.
Syntax

DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;

Oracle 9 and over, ethod 2

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') and owner#=
        (select user# from user$ where name='OUTLN');  
 
  select count(*) into extra_outln_tables from obj$ where name not in
        ('OL$', 'OL$HINTS') 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, ' ||              
            'category          varchar2(30), ' ||        
            'version           varchar2(64), ' ||        
            'creator           varchar2(30), ' ||        
            'timestamp         date,  ' ||               
            'flags             number, ' ||              
            'hintcount         number)', 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)', 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!=2 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;
/
 
commit
/
 
$ORACLE_HOME/rdbms/admin/dbmsol.sql
 
REM
REM =========== BEGIN OF OUTLN tables upgrade ======================
REM
 
ALTER TABLE outln.ol$
add
(
  hash_value2       number,/* hash value on sql_text stripped of whitespace */
  spare1            number,                                 /* spare column */
  spare2            varchar2(1000)                          /* spare column */
)
/
 
ALTER TABLE outln.ol$hints
add  
(  
  ref_id            number,         /* node id that this hint is referencing */
  user_table_name   varchar2(64),   /* table name to which this hint applies */
                                      /* this field also contains the schema */
                                       /* name to which the table belongs to */
  cost              double precision,     /* optimizer estimated cost of the
                                                            hinted operation */
  cardinality       double precision,     /* optimizer estimated cardinality
                                                     of the hinted operation */
  bytes             double precision,      /* optimizer estimated byte count
                                                     of the hinted operation */
  hint_textoff      number,              /* offset into the SQL statement to
                                                     which this hint applies */
  hint_textlen      number,      /* length of SQL to which this hint applies */
  join_pred         varchar2(2000),      /* join predicate (applies only for 
                                                          join method hints) */
  spare1            number,          /* spare number for future enhancements */
  spare2            number           /* spare number for future enhancements */
)
/
create table outln.ol$nodes
(
  ol_name       varchar2(30),                              /* outline name  */
  category      varchar2(30),                           /* outline category */
  node_id       number,                              /* qbc node identifier */
  parent_id     number,      /* node id of the parent node for current node */ 
  node_type     number,                                    /* qbc node type */
  node_textlen  number,         /* length of SQL to which this node applies */ 
  node_textoff  number       /* offset into the SQL statement to which this
                                                               node applies */
)
/
 
REM =========== END OF outln tables upgrade ================================

Oracle 8.1.5

This script will only work if the user OUTLN has been dropped
'cascade' from the database.

This script MUST be run as the user INTERNAL.
This script was extracted from C0800050.sql

Version Testing:
This script was tested on Oracle V8.1.5.

Note:

After running this script, the user will need to run
catalog.sql and catproc.sql. These scripts must be run
as the user SYS or INTERNAL.

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') and owner#=
    (select user# from user$ where name='OUTLN'); 
 
    select count(*) into extra_outln_tables from obj$ where name not in
    ('OL$', 'OL$HINTS') 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, ' || 
            'category varchar2(30), ' || 
            'version varchar2(64), ' || 
            'creator varchar2(30), ' || 
            'timestamp date, ' || 
            'flags number, ' || 
            'hintcount number)', 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)', 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!=2 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;
/

See Also