-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|