Unable to pin to shared pool
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Unable to pin to shared pool

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Question Unable to pin to shared pool

    Hi everyone.
    Oracle 8.0.6
    I am trying to pin some packages into shared pool. here is an example of the error I get. (Running sql as SYSTEM user)

    CODE
    --------------------------------------
    SQL> SET SERVEROUTPUT ON SIZE 262144
    SQL> declare
    2 own varchar2(100);
    3 nam varchar2(100);
    4 cntr number;
    5 cursor pkgs is
    6 select owner, object_name
    7 from dba_objects
    8 where object_type = 'PACKAGE'
    9 and owner = 'OWNERNAME'
    10 and status = 'VALID';
    11 begin
    12 open pkgs;
    13 cntr:=0;
    14 loop
    15 fetch pkgs into own, nam;
    16 exit when pkgs%notfound;
    17 sys.dbms_shared_pool.keep(own || '.' || nam, 'P');
    18 cntr:=cntr+1;
    19 end loop;
    20 sys.dbms_shared_pool.keep('SYS.DIANA', 'P');
    21 sys.dbms_shared_pool.keep('SYS.DUTIL', 'P');
    22 sys.dbms_shared_pool.keep('SYS.STANDARD', 'P');
    23 sys.dbms_shared_pool.keep('SYS.DBMS_SYS_SQL', 'P');
    24 sys.dbms_shared_pool.keep('SYS.DBMS_SQL', 'P');
    25 sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY', 'P');
    26 sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE', 'P');
    27 sys.dbms_shared_pool.keep('SYS.DBMS_JOB', 'P');
    28 sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD', 'P');
    29 sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT', 'P');
    30 sys.dbms_shared_pool.keep('SYS.PIDL', 'P');
    31
    32 dbms_output.put_line('Number of Ownername packages pinned : '||cntr);
    33 end;
    34 /
    -------------------------------------------------------------

    ERROR
    declare
    *
    ERROR at line 1:
    ORA-06550: line 17, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 17, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 20, column 2:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 20, column 2:
    PL/SQL: Statement ignored
    ORA-06550: line 21, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 21, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 22, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 22, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 23, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 23, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 24, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 24, column 5:
    PL/SQL: Statement ignored
    ORA-06550: line 25, column 5:
    PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
    ORA-06550: line 25, column 5:
    PL/SQL: State


    --------------------------------------------------------------

    I did some checking and it seems that I need to run Oracle supplied
    'dbmspool.sql'. I ran this sql (as SYSTEM user) and got the following error:



    ----------------------------------------------------
    SQL> start /oracle/8.0.6/rdbms/admin/dbmspool.sql

    Package created.


    Grant succeeded.

    from dba_object_size
    *
    ERROR at line 4:
    ORA-00942: table or view does not exist



    Warning: Package Body created with compilation errors.

    ------------------------------------------------------

    Yet when I do a desc on dba_object size it shows that view exists

    SQL> desc dba_object_size;
    Name Null? Type
    ------------------------------- -------- ----
    OWNER NOT NULL VARCHAR2(30)
    NAME NOT NULL VARCHAR2(30)
    TYPE VARCHAR2(12)
    SOURCE_SIZE NUMBER
    PARSED_SIZE NUMBER
    CODE_SIZE NUMBER
    ERROR_SIZE NUMBER


    --------------------------------------------------------

    I would be grateful if anyone could advise me on where I am going wrong.
    Share on Google+

  2. #2
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    You need to run
    $ORACLE_HOME/rdbms/admin/dbmspool.sql from SYS (not system)
    Share on Google+

  3. #3
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Smile

    That's right - when creating the db, and lately running catalog.sql and catproc.sql, one has to be logged as SYS (INTERNAL since 8i). Most of the data dictionary scripts require access to SYS packages, views or tables, otherwise not accesible with other accounts.


    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

    Visit our Web Site
    Share on Google+

  4. #4
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Thanks Supower and clio, everything ok now.
    Share on Google+

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