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

Thread: Pinning Automation.!

  1. #1

    Unhappy

    Has any one seen the attached before?

    I have created the stored procs and the last two I get errors on as follows:

    PROC_PKG_LIST :=
    Line # = 16 Column # = 20 Error Text = PLS-00201: identifier 'SYS.LIST_TAB' must be declared
    Line # = 16 Column # = 8 Error Text = PL/SQL: SQL Statement ignored

    PROC_PKG_KEEP :=
    Line # = 5 Column # = 9 Error Text = PL/SQL: SQL Statement ignored
    Line # = 5 Column # = 16 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Line # = 6 Column # = 14 Error Text = PLS-00201: identifier 'SYS.LIST_TAB' must be declared
    Line # = 10 Column # = 8 Error Text = PL/SQL: SQL Statement ignored


    Can anyone help me out here? I am no PLSQL expert which leaves me a shade stuck!
    I am working on Oracl8.1.5 on solaris 5.6 fyi.

    PURPOSE
    This note explains how to automatically pin the most often loaded procedures
    and packages in the shared pool at database startup.


    SCOPE & APPLICATION
    This example uses the 'AFTER STARTUP ON DATABASE' and 'BEFORE SHUTDOWN ON
    DATABASE' triggers to automate this administrative task.


    How to Automate Pinning Objects in the Shared Pool at Database Startup:
    =======================================================================

    ********************************************************
    1/ Create the procedures to be executed by the triggers
    ********************************************************

    a. The procedure proc_create creates a table to store the
    names of packages and procedures that had to be reloaded
    several times during the instance life.

    SQL> CREATE OR REPLACE PROCEDURE sys.proc_create
    (tablename varchar2, cols varchar2) AS
    2 cursor1 INTEGER;
    3 BEGIN
    4 cursor1 := dbms_sql.open_cursor;
    5 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||
    6 ' ( ' || cols || ' )', dbms_sql.native);
    7 dbms_sql.close_cursor(cursor1);
    8 END;
    9 /
    Procedure created.

    b. The procedure proc_drop drops the table before reselecting
    the procedures and package names to be stored in the table.

    SQL> CREATE OR REPLACE PROCEDURE sys.proc_drop (tablename varchar2) AS
    2 cursor1 INTEGER;
    3 BEGIN
    4 cursor1 := dbms_sql.open_cursor;
    5 dbms_sql.parse(cursor1, 'DROP TABLE ' || tablename, dbms_sql.native);
    6 dbms_sql.close_cursor(cursor1);
    7 END;
    8 /
    Procedure created.

    c. The procedure proc_pkgs_list retrieves the names of the packages
    and procedures that will be kept in the shared pool at startup
    and insert the names in the table.

    SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_list AS
    2 own varchar2(64);
    3 nam varchar2(100);
    4 cursor pkgs is
    5 select owner,name
    6 from SYS.v_$db_object_cache
    7 where type in ('PACKAGE','PROCEDURE')
    8 and loads > 1 or KEPT='YES';
    9 BEGIN
    10 sys.proc_drop('SYS.LIST_TAB');
    11 sys.proc_create('SYS.LIST_TAB','OWNER VARCHAR2(64), NAME VARCHAR2(100)');
    12 open pkgs;
    13 loop
    14 fetch pkgs into own, nam;
    15 exit when pkgs%notfound;
    16 insert into sys.list_tab values (own , nam);
    17 commit;
    18 end loop;
    19 end;
    20 /
    Procedure created.

    d. The procedure proc_pkgs_keep retrieves the procedures and
    package names and keeps the objects in the shared pool.
    In order to use the dbms_shared_pool package procedures,
    execute the dbmspool.sql script first.

    SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
    2 own varchar2(64);
    3 nam varchar2(100);
    4 cursor pkgs is
    5 select owner ,name
    6 from sys.list_tab;
    7 BEGIN
    8 open pkgs;
    9 loop
    10 fetch pkgs into own, nam;
    11 exit when pkgs%notfound;
    12 SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
    13 end loop;
    14 sys.dbms_shared_pool.keep('SYS.STANDARD');
    15 sys.dbms_shared_pool.keep('SYS.DIUTIL');
    16 END;
    17 /

    Procedure created.




    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  2. #2
    Hi.
    Execute the first one.
    SQL> execute sys.proc_create;
    Then create the last 2 procs.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    hi,

    Just exec the first one as follows:

    SQL> execute sys.proc_create;
    BEGIN sys.proc_create; END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'PROC_CREATE'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    >>>>>>>>??????????????<<<<<<<<<<

    What am I doing wrong here?

    cheers for the help!
    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  4. #4
    Sorry, it wasn't you, It was me
    SQL>exec sys.proc_create('SYS.LIST_TAB','OWNER VARCHAR2(64), NAME VARCHAR2(100)');
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Ok, It compiles now I am wondering why I get the following error:

    SQL> exec sys.proc_pkgs_list;

    PL/SQL procedure successfully completed.

    SQL> exec sys.proc_pkgs_keep;
    BEGIN sys.proc_pkgs_keep; END;

    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04063: package body "SYS.DBMS_SHARED_POOL" has errors
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at "SYS.PROC_PKGS_KEEP", line 12
    ORA-06512: at line 1

    I have checked the sys.dbms_shared_pool and there is no errors in this package?
    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  6. #6
    There is something wrong with:
    12 SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
    See what rows are in the table SYS.LIST_TAB
    Then
    execute SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
    replacing own and name with data from the previous table.
    Maybe is something with quotation marks
    Ramon Caballero, DBA, rcaballe@yahoo.com

  7. #7
    After a quick look it appears the package body for dbms_share_pool is invalid! I have tryied to recompile but still get errors. Do you know which script reruns this? I am on ORACLE 8.1.5
    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

  8. #8
    DBMSPOOL.SQL located in %ORACLE_HOME%(Win) or $ORACLE_HOME(U*x) /rdbms/admin can recreate it.
    I guess you should be connected as SYS or INTERNAL(obsolete)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  9. #9

    Wink

    Thanks MAN!.

    You have helped me out loads there.
    The brain is a wonderful organ; it starts working the moment you get up in the morning and does not stop until you get into the office.

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