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

Thread: Error while pinning pl/sql block

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Error while pinning pl/sql block

    Hello,

    I am getting an error while pinning a pl/sql block using dbms_shared_pool
    Code:
    SQL> l
      1  select sql_text, address, hash_value from v$sqlarea
      2* order by first_load_time desc
    SQL> 
    
    SQL_TEXT                                                          ADDRESS  HASH_VALUE 
    --------------------------------------------------------------------------------------
    declare ss varchar2(100); cursor c1 is select object_name         036FD9C0 2702567367 
    from all_objects where object_type like 'PROC%'; begin open c1;                       
    loop fetch c1 into ss; exit when c1%notfound; end loop;                               
    close c1; end;
    
    SQL>  exec dbms_shared_pool.keep('036FD9C0,2702567367');
    BEGIN dbms_shared_pool.keep('036FD9C0,2702567367'); END;
    
    *
    ERROR at line 1:
    ORA-00931: missing identifier
    ORA-06512: at "SYS.DBMS_UTILITY", line 68
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
    ORA-06512: at line 1
    Where I am going wrong? Is it possibe to load sql statement which is not in a pl/sql block or not in a funcion or procedure using the statement address and hash value? I get the same error if I try to pin a statement. I am able to pin procedure or function.

    Please advice

    Thanks

    Sameer

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    found following solution from metalink . hope this helps u.

    Check your current operating system parameter settings:
    % sysdef
    Increase the following operating system parameters:

    maxdsiz
    maxtsiz
    maxssiz
    maxdsiz64
    maxtsiz64
    maxssiz64

    An exact value cannot be given for these parameters, but a 1 GB value is a nice round number.

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Thanks for the reply.. It wasn't anyting with OS parameters. I didn't supply 'C' parameter for cursor while pinning it.

    Code:
    SQL> exec dbms_shared_pool.keep('036FD9C0,2702567367','C');
    
    PL/SQL procedure successfully completed.
    Thanks

    Sameer

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