Unable to pin to shared pool

[bgill]

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.

[supower]

You need to run
$ORACLE_HOME/rdbms/admin/dbmspool.sql from SYS (not system)

[clio_usa]

That's right - when creating the db, and lately running catalog.sql and catproc.sql, one has to be logged as SYS (INTERNAL since 8i). Most of the data dictionary scripts require access to SYS packages, views or tables, otherwise not accesible with other accounts.


Hope that helps,

clio_usa - OCP 8/8i/9i DBA

Visit our Web Site

[bgill]

Thanks Supower and clio, everything ok now.