DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Export error!! Please help!!

Hybrid View

  1. #1
    Join Date
    Feb 2002
    Posts
    14

    Unhappy

    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!!


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Version? There is a bug in 8.1.6.1 (I think) that will give similar errors...
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    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
    I remember when this place was cool.

  4. #4
    Join Date
    Feb 2002
    Posts
    14
    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!

  5. #5
    Join Date
    Feb 2002
    Posts
    14
    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.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,134
    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
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width