Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: pin frequently used packages at database startup

    I need pin frequently used packages, at database startup time (with an ON DATABASE STARTUP trigger)
    using dbms_shared_pool.keep.This below article published by "Donald K. Burleson"
    I got the below error when executing

    Thanks in advance
    mohan

    sql> conn internal;
    connected

    SQL> create or replace trigger
    2 pin_packs
    3 after startup on database
    4 begin
    5 execute dbms_shared_pool.keep('MAIN_PACK');
    6 execute dbms_shared_pool.keep('OTHER_PACK');
    7 execute dbms_shared_pool.keep('DBMS_ALERT');
    8 execute dbms_shared_pool.keep('DBMS_DDL');
    9 execute dbms_shared_pool.keep('DBMS_DESCRIBE');
    10 execute dbms_shared_pool.keep('DBMS_LOCK');
    11 execute dbms_shared_pool.keep('DBMS_OUTPUT');
    12 execute dbms_shared_pool.keep('DBMS_PIPE');
    13 execute dbms_shared_pool.keep('DBMS_SESSION');
    14 execute dbms_shared_pool.keep('DBMS_STANDARD');
    15 execute dbms_shared_pool.keep('DBMS_UTILITY');
    16 execute dbms_shared_pool.keep('STANDARD');
    17 end;
    18 /

    Warning: Trigger created with compilation errors.

    SQL> show err
    Errors for TRIGGER PIN_PACKS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/17 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    3/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    LINE/COL ERROR
    -------- -----------------------------------------------------------------

    4/17 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    5/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    continue.

    6/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    7/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    8/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    9/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    10/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    11/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

    12/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    13/9 PLS-00103: Encountered the symbol "DBMS_SHARED_POOL" when
    expecting one of the following:
    := . ( @ % ; immediate
    The symbol ":=" was substituted for "DBMS_SHARED_POOL" to
    continue.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pin frequently used packages at database startup

    The "execute" keyword is not valid. Your trigger should look like this:

    SQL> create or replace trigger
    2 pin_packs
    3 after startup on database
    4 begin
    5 dbms_shared_pool.keep('MAIN_PACK');
    6 dbms_shared_pool.keep('OTHER_PACK');
    7 dbms_shared_pool.keep('DBMS_ALERT');
    8 dbms_shared_pool.keep('DBMS_DDL');
    9 dbms_shared_pool.keep('DBMS_DESCRIBE');
    10 dbms_shared_pool.keep('DBMS_LOCK');
    11 dbms_shared_pool.keep('DBMS_OUTPUT');
    12 dbms_shared_pool.keep('DBMS_PIPE');
    13 dbms_shared_pool.keep('DBMS_SESSION');
    14 dbms_shared_pool.keep('DBMS_STANDARD');
    15 dbms_shared_pool.keep('DBMS_UTILITY');
    16 dbms_shared_pool.keep('STANDARD');
    17 end;
    18 /

Posting Permissions

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