Re: Pinning Packages in the Shared Pool
Date: 23 Feb 95 18:57:32 GMT
Message-ID: <1995Feb23.135732.1592_at_rcwusr>
In article <3igiv4$30l_at_newsserver.trl.OZ.AU>, TCook_at_vtrlmel1.trl.oz.au (Tony Cook) writes:
>
> Has anyone successfully pinned a package into the shared pool ?
>
> It is mentioned briefly in the documentation, but does not outline how to do it.
> I did find the dbms_shared_pool package supplied by Oracle though.
> (Very little documentation on this)
> It has a procedure called "keep" that should do the job but I cannot get it to work.
>
> Example follows:
>
> 16:15:54 SQL+> begin
> 16:15:54 2 dbms_shared_pool.keep('HUSD2.PG_SEC_ADMIN');
> 16:15:54 3 end;
> 16:15:54 4 /
> begin
> *
> ERROR at line 1:
> ORA-06564: object HUSD2.PG_SEC_ADMIN does not exist
> ORA-06512: at "SYS.DBMS_UTILITY", line 28
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 17
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 24
> ORA-06512: at line 2
>
>
> For some reason my package cannot be found.
> I have tried a few permutations of running this as system or the package owner.
>
> Am I doing something wrong?
> Is there another way to pin objects?
>
> We are using 7.1.3 on HP-UX.
>
> Thanks
> Tony Cook
Tony,
Here is a script I developed to pin packages in the shared pool. We run it at database startup time.
I had quite a bit of trouble getting it to work initially under various owners. It seems to work pretty consistently when using 'SYS' (internal).
It's also worth noting that we were never able to pin procedures or functions.
Hope this helps!
Harley Green
sqldba lmode=y<<EOF
connect internal;
alter system flush shared_pool;
execute dbms_shared_pool.keep('rmf.rmf_pkg_02');
execute dbms_shared_pool.keep('rmf.rmf_pkg_03');
execute dbms_shared_pool.keep('rmf.rte_pkg');
execute dbms_shared_pool.keep('ddb.ddb_pkg_01');
execute dbms_shared_pool.keep('pricing.fpr_pkg_dlr_support');
execute dbms_shared_pool.keep('pricing.fpr_pkg_margin');
execute dbms_shared_pool.keep('pricing.fpr_pkg_release_price');
execute dbms_shared_pool.keep('pricing.fpr_tax_pkg');
EOF
Received on Thu Feb 23 1995 - 19:57:32 CET