Unable to pin to shared pool
Hi everyone.
Oracle 8.0.6
I am trying to pin some packages into shared pool. here is an example of the error I get. (Running sql as SYSTEM user)
CODE
--------------------------------------
SQL> SET SERVEROUTPUT ON SIZE 262144
SQL> declare
2 own varchar2(100);
3 nam varchar2(100);
4 cntr number;
5 cursor pkgs is
6 select owner, object_name
7 from dba_objects
8 where object_type = 'PACKAGE'
9 and owner = 'OWNERNAME'
10 and status = 'VALID';
11 begin
12 open pkgs;
13 cntr:=0;
14 loop
15 fetch pkgs into own, nam;
16 exit when pkgs%notfound;
17 sys.dbms_shared_pool.keep(own || '.' || nam, 'P');
18 cntr:=cntr+1;
19 end loop;
20 sys.dbms_shared_pool.keep('SYS.DIANA', 'P');
21 sys.dbms_shared_pool.keep('SYS.DUTIL', 'P');
22 sys.dbms_shared_pool.keep('SYS.STANDARD', 'P');
23 sys.dbms_shared_pool.keep('SYS.DBMS_SYS_SQL', 'P');
24 sys.dbms_shared_pool.keep('SYS.DBMS_SQL', 'P');
25 sys.dbms_shared_pool.keep('SYS.DBMS_UTILITY', 'P');
26 sys.dbms_shared_pool.keep('SYS.DBMS_DESCRIBE', 'P');
27 sys.dbms_shared_pool.keep('SYS.DBMS_JOB', 'P');
28 sys.dbms_shared_pool.keep('SYS.DBMS_STANDARD', 'P');
29 sys.dbms_shared_pool.keep('SYS.DBMS_OUTPUT', 'P');
30 sys.dbms_shared_pool.keep('SYS.PIDL', 'P');
31
32 dbms_output.put_line('Number of Ownername packages pinned : '||cntr);
33 end;
34 /
-------------------------------------------------------------
ERROR
declare
*
ERROR at line 1:
ORA-06550: line 17, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 17, column 5:
PL/SQL: Statement ignored
ORA-06550: line 20, column 2:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 20, column 2:
PL/SQL: Statement ignored
ORA-06550: line 21, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 21, column 5:
PL/SQL: Statement ignored
ORA-06550: line 22, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 22, column 5:
PL/SQL: Statement ignored
ORA-06550: line 23, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 23, column 5:
PL/SQL: Statement ignored
ORA-06550: line 24, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 24, column 5:
PL/SQL: Statement ignored
ORA-06550: line 25, column 5:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 25, column 5:
PL/SQL: State
--------------------------------------------------------------
I did some checking and it seems that I need to run Oracle supplied
'dbmspool.sql'. I ran this sql (as SYSTEM user) and got the following error:
----------------------------------------------------
SQL> start /oracle/8.0.6/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
from dba_object_size
*
ERROR at line 4:
ORA-00942: table or view does not exist
Warning: Package Body created with compilation errors.
------------------------------------------------------
Yet when I do a desc on dba_object size it shows that view exists
SQL> desc dba_object_size;
Name Null? Type
------------------------------- -------- ----
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
SOURCE_SIZE NUMBER
PARSED_SIZE NUMBER
CODE_SIZE NUMBER
ERROR_SIZE NUMBER
--------------------------------------------------------
I would be grateful if anyone could advise me on where I am going wrong.
