-
Has any one seen the attached before?
I have created the stored procs and the last two I get errors on as follows:
PROC_PKG_LIST :=
Line # = 16 Column # = 20 Error Text = PLS-00201: identifier 'SYS.LIST_TAB' must be declared
Line # = 16 Column # = 8 Error Text = PL/SQL: SQL Statement ignored
PROC_PKG_KEEP :=
Line # = 5 Column # = 9 Error Text = PL/SQL: SQL Statement ignored
Line # = 5 Column # = 16 Error Text = PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line # = 6 Column # = 14 Error Text = PLS-00201: identifier 'SYS.LIST_TAB' must be declared
Line # = 10 Column # = 8 Error Text = PL/SQL: SQL Statement ignored
Can anyone help me out here? I am no PLSQL expert which leaves me a shade stuck!
I am working on Oracl8.1.5 on solaris 5.6 fyi.
PURPOSE
This note explains how to automatically pin the most often loaded procedures
and packages in the shared pool at database startup.
SCOPE & APPLICATION
This example uses the 'AFTER STARTUP ON DATABASE' and 'BEFORE SHUTDOWN ON
DATABASE' triggers to automate this administrative task.
How to Automate Pinning Objects in the Shared Pool at Database Startup:
=======================================================================
********************************************************
1/ Create the procedures to be executed by the triggers
********************************************************
a. The procedure proc_create creates a table to store the
names of packages and procedures that had to be reloaded
several times during the instance life.
SQL> CREATE OR REPLACE PROCEDURE sys.proc_create
(tablename varchar2, cols varchar2) AS
2 cursor1 INTEGER;
3 BEGIN
4 cursor1 := dbms_sql.open_cursor;
5 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename ||
6 ' ( ' || cols || ' )', dbms_sql.native);
7 dbms_sql.close_cursor(cursor1);
8 END;
9 /
Procedure created.
b. The procedure proc_drop drops the table before reselecting
the procedures and package names to be stored in the table.
SQL> CREATE OR REPLACE PROCEDURE sys.proc_drop (tablename varchar2) AS
2 cursor1 INTEGER;
3 BEGIN
4 cursor1 := dbms_sql.open_cursor;
5 dbms_sql.parse(cursor1, 'DROP TABLE ' || tablename, dbms_sql.native);
6 dbms_sql.close_cursor(cursor1);
7 END;
8 /
Procedure created.
c. The procedure proc_pkgs_list retrieves the names of the packages
and procedures that will be kept in the shared pool at startup
and insert the names in the table.
SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_list AS
2 own varchar2(64);
3 nam varchar2(100);
4 cursor pkgs is
5 select owner,name
6 from SYS.v_$db_object_cache
7 where type in ('PACKAGE','PROCEDURE')
8 and loads > 1 or KEPT='YES';
9 BEGIN
10 sys.proc_drop('SYS.LIST_TAB');
11 sys.proc_create('SYS.LIST_TAB','OWNER VARCHAR2(64), NAME VARCHAR2(100)');
12 open pkgs;
13 loop
14 fetch pkgs into own, nam;
15 exit when pkgs%notfound;
16 insert into sys.list_tab values (own , nam);
17 commit;
18 end loop;
19 end;
20 /
Procedure created.
d. The procedure proc_pkgs_keep retrieves the procedures and
package names and keeps the objects in the shared pool.
In order to use the dbms_shared_pool package procedures,
execute the dbmspool.sql script first.
SQL> CREATE OR REPLACE PROCEDURE sys.proc_pkgs_keep AS
2 own varchar2(64);
3 nam varchar2(100);
4 cursor pkgs is
5 select owner ,name
6 from sys.list_tab;
7 BEGIN
8 open pkgs;
9 loop
10 fetch pkgs into own, nam;
11 exit when pkgs%notfound;
12 SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
13 end loop;
14 sys.dbms_shared_pool.keep('SYS.STANDARD');
15 sys.dbms_shared_pool.keep('SYS.DIUTIL');
16 END;
17 /
Procedure created.
-
Hi.
Execute the first one.
SQL> execute sys.proc_create;
Then create the last 2 procs.
-
hi,
Just exec the first one as follows:
SQL> execute sys.proc_create;
BEGIN sys.proc_create; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROC_CREATE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
>>>>>>>>??????????????<<<<<<<<<<
What am I doing wrong here?
cheers for the help!
-
Sorry, it wasn't you, It was me :D
SQL>exec sys.proc_create('SYS.LIST_TAB','OWNER VARCHAR2(64), NAME VARCHAR2(100)');
-
Ok, It compiles now I am wondering why I get the following error:
SQL> exec sys.proc_pkgs_list;
PL/SQL procedure successfully completed.
SQL> exec sys.proc_pkgs_keep;
BEGIN sys.proc_pkgs_keep; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_SHARED_POOL" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.PROC_PKGS_KEEP", line 12
ORA-06512: at line 1
I have checked the sys.dbms_shared_pool and there is no errors in this package?
-
There is something wrong with:
12 SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
See what rows are in the table SYS.LIST_TAB
Then
execute SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '');
replacing own and name with data from the previous table.
Maybe is something with quotation marks
-
After a quick look it appears the package body for dbms_share_pool is invalid! I have tryied to recompile but still get errors. Do you know which script reruns this? I am on ORACLE 8.1.5
-
DBMSPOOL.SQL located in %ORACLE_HOME%(Win) or $ORACLE_HOME(U*x) /rdbms/admin can recreate it.
I guess you should be connected as SYS or INTERNAL(obsolete)
-
Thanks MAN!.
You have helped me out loads there.
