DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-18008 cannot find OUTLN schema

  1. #1
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145
    !hello

    Accidentaly I drop OUTLN Schema .

    Is there is any script to create OUTLN Schema. or else any other solution
    Please help.

  2. #2
    Join Date
    Sep 2001
    Posts
    62




    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.

    Script:
    ***************** Begin 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') 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;
    /

    This is a script from Oracle metalink, it should do the job.

    Shaz

  3. #3
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145

    Thank's




    Thank's a lot to SAZZADUR.

    u gave correct and right solution for me .It work perfectly on Oracle 9i also.

    Onces again thank'U

    it my id :: akram_tri@yahoo.co.in


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width