-
I am getting the following error when doing a full export:
EXP-00008: ORACLE error 4045 encountered
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQ_IMPORT_INTERNAL
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1118
ORA-06512: at "SYS.DBMS_SQL", line 316
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 84
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 135
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
I have tried other suggestions involving running the catexp.sql (ran under sys) script, but to no avail. Can I import the SYS.AQ$_SUBSCRIBERS from another database that has no errors with a full export? What does the AQ$_SUBSCRIBER object hold? Any suggestions?!!!! TIA!!
-
Version? There is a bug in 8.1.6.1 (I think) that will give similar errors...
-
I know nothing about this but here is what I dug up, maybe something will click.
The DBMS_AQ package provides an interface to the operational tasks of Oracle AQ. To use these programs, you must have been granted the new role AQ_USER_ROLE.
The subscriber list is used to enqueue a message to a list of subscribers for a given queue. You will call the DBMS_AQADM.QUEUE_SUBSCRIBERS function to obtain the subscript list for a queue. The subscripter list table type is, therefore, defined in the DBMS_AQADM package. As you can see, both of these table types are identical in structure; only their names differ.
The QUEUE_SUBSCRIBERS function returns the list of subscribers associated with the specified queue. This list is an index table, as shown in the header:
FUNCTION DBMS_AQADM.QUEUE_SUBSCRIBERS
(queue_name IN VARCHAR2)
RETURN DBMS_AQADM.AQ$_SUBSCRIBER_LIST_T;
Parameters are summarized below:
queue_name Name of the queue
Example
The following procedure encapsulates the steps needed to obtain this list and then display it:
CREATE OR REPLACE PROCEDURE showsubs (qname IN VARCHAR2)
IS
sublist DBMS_AQADM.AQ$_SUBSCRIBER_LIST_T;
v_row PLS_INTEGER;
BEGIN
/* Retrieve the list. */
sublist := DBMS_AQADM.QUEUE_SUBSCRIBERS (qname);
v_row := sublist.FIRST;
LOOP
EXIT WHEN v_row IS NULL;
DBMS_OUTPUT.PUT_LINE (v_row);
DBMS_OUTPUT.PUT_LINE (sublist(v_row).name);
v_row := sublist.NEXT (v_row);
END LOOP;
END;
/
Now let's put the procedure to use. First of all, you can only associate a set of subscribers with a queue which supports multiple consumers. These steps are shown below:
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE
(queue_table => 'multicons',
queue_payload_type => 'message_type',
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE
(queue_name => 'multiconsqueue',
queue_table => 'multicons');
DBMS_AQADM.START_QUEUE (queue_name => 'multiconsqueue');
END;
/
We can then add subscribers to the multiconsqueue and display the results:
DECLARE
v_queue VARCHAR2(10) := 'multiconsqueue';
BEGIN
DBMS_AQADM.ADD_SUBSCRIBER
(v_queue, SYS.AQ$_AGENT ('Danielle', NULL, NULL));
DBMS_AQADM.ADD_SUBSCRIBER
(v_queue, SYS.AQ$_AGENT ('Benjamin', NULL, NULL));
DBMS_AQADM.ADD_SUBSCRIBER
(v_queue, SYS.AQ$_AGENT ('Masada', NULL, NULL));
DBMS_AQADM.ADD_SUBSCRIBER
(v_queue, SYS.AQ$_AGENT ('Timnah', NULL, NULL));
showsubs (v_queue);
END;
/
Hope some of this helps
MH
-
We are running Oracle 8.1.7 on Sun Solaris 8
I noticed if I do the following:
select object_name, status from dba_objects
where owner = 'SYS' and object_name = 'AQ$_SUBSCRIBERS';
It pulls up as the status being invalid. Anyone know how I can change this to valid?
Thanks again chums!
-
Hey all,
Just ran the catproc.sql script on the db that was giving full export errors. The errors are gone, and I am able to do a full export no sweat.
My question is, what does the catproc.sql do?? It seems like if I run the catalog.sql script, it causes my export woes, and the catproc.sql script fixes everything.
-
Here are some more excepts I found.
17. Finish converting the catalog to a full ORACLE8 catalog by running cat8000.sql, usually located in the $ORACLE_HOME/rdbms/admin subdirectory on UNIX. Then run catalog.sql located in the same place. Finally, run catproc.sql to rebuild the PL/SQL and utility packages. If needed, also run any other "cat".sql scripts to install any purchased options as required.
Scripts run by CATPROC.SQL
All of these scripts are located in the $ORACLE_HOME/rdbms/admin directory or its equivalent on your platform. These scripts build virtually the entire set of Oracle utilities including PL/SQL.
Scripts run by catproc.sql are:
SCRIPT PURPOSE
catprc.sql This creates data dictionary views for types, stored procedures, and triggers. This script must be run while connected to sys or internal.
catjobq This script creates the catalog views for the job queue. This script must be run while connected as SYS or INTERNAL.
catrpc This script creates internal views for RPC (Remote Procedural Calls). These views are needed only for databases with the procedural option that are accessed by remote databases. This script must be run as SYS.
dbmsstdx.sql This package provides kernel extensions to package standard. Routines in this package do not need to be qualified by the owner or package name, similar to the behavior of package 'standard'. This package mostly contains utility routines for triggers.
pipidl.sql This creates the package Portable IDL.
pidian.sql This creates the package Diana.
diutil.sql This creates Diana application routines.
pistub.sql This script generates the subprogram stub generator.
plitblm.sql This is PL/sql Index-TaBLe Methods Package for index-table operations. This package must be loaded by catproc.sql script. This file needs to be kept in sync with its .pls version (icd/plitblm.pls) currently.
MH
