-
** How to cache objects in the Library Cache during INSTANCE STARTUP **
Can someone here tell me how to cache objects in the Library Cache during INSTANCE STARTUP. I know we have to use some package. I am not getting the names and procedures. Kindly help me.
-
Connect as svrmgrl and run (enter you own packages)
CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
BEGIN
sys.dbms_shared_pool.keep('SYS.STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
sys.dbms_shared_pool.keep('SYS.DBMS_EXPORT_EXTENSION');
sys.dbms_shared_pool.keep('SCOTT.PACKAGE','P');
END;
And then create a trigger
CREATE OR REPLACE TRIGGER db_startup_pin AFTER STARTUP ON DATABASE
BEGIN
sys.proc_pkgs_keep;
END;
/
Cheers
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
Connect as svrmgrl and run (enter you own packages)
CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
BEGIN
sys.dbms_shared_pool.keep('SYS.STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD');
sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT');
sys.dbms_shared_pool.keep('SYS.DBMS_EXPORT_EXTENSION');
sys.dbms_shared_pool.keep('SCOTT.PACKAGE','P');
END;
And then create a trigger
CREATE OR REPLACE TRIGGER db_startup_pin AFTER STARTUP ON DATABASE
BEGIN
sys.proc_pkgs_keep;
END;
/
Cheers
Many thanks. The last example: does it mean that Scott's package called "P" is loaded into the cache? Does it mean it if I have load say a procedure "X" of scott, then it would become "SCOTT.PROCEDURE","X"??
Kindly clarify.
-
sys.dbms_shared_pool.keep('SCOTT.X','P');
ie.
sys.dbms_shared_pool.keep('SCOTT.PACKAGE_NAME','P');
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
sys.dbms_shared_pool.keep('SCOTT.X','P');
ie.
sys.dbms_shared_pool.keep('SCOTT.PACKAGE_NAME','P');
Thanks. So what does the second parameter 'P' stand for then?
Regards.
Quester.
-
P is default.
flag:
(Optional) If this is not specified, then the package assumes
that the first parameter is the name of a
package/procedure/function and resolves the name.
Set to ’P’ or ’p’ to fully specify that the input is the name of a
package/procedure/function.
Set to ’T’ or ’t’ to specify that the input is the name of a type.
Set to ’R’ or ’r’ to specify that the input is the name of a trigger.
Set to ’Q’ or ’q’ to specify that the input is the name of a
sequence.
In case the first argument is a cursor address and hash-value,
the parameter should be set to any character except ’P’ or ’p’ or
’Q’ or ’q’ or ’R’ or ’r’ or ’T’ or ’t’.
Regards,
Jack
-
PL/SQL or PACKAGE.. take your pick, documentation uses both terms.
OCP 8i, 9i DBA
Brisbane Australia
-
Now I see two different answers (ofcourse from two different members). Thanks anyways for the time and patience.
-
In fact they are the same answers really.
What jack9190 has posted is correct. YOu actually don't have to specify 'P' if you are pinning a Package, Procedure or function since it the default. So P stands for
Package, Procedure, Fucntion, PL/SQL
How's that.. any clearer?
OCP 8i, 9i DBA
Brisbane Australia
-
Originally posted by grjohnson
In fact they are the same answers really.
What jack9190 has posted is correct. YOu actually don't have to specify 'P' if you are pinning a Package, Procedure or function since it the default. So P stands for
Package, Procedure, Fucntion, PL/SQL
How's that.. any clearer?
O yEA. Excellent and many thanks.
BTW, any idea whic system table hold the names and types of the objects cached? does V$LIBRARYCACHE indicate that?
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
|