DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, May 31, 2016

ERROR WHILE IMPORTING THE SCHEMA

ERROR WHILE IMPORTING

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20100810134502" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors

CAUSE:

Due to invalid object of sys

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

OBJECT_NAME
------------------------------
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTK+Hp6PazTJaPOReVq6YXqQ==
SYSNTsDyaSCdvTmySdAs5Ztvhbw==
SYSNTWBJ9iDeeSuyp/p6B7f1iEg==

8 rows selected.

SOLUTION:

Recreate queue tables,that were invalid

SQL> exec dbms_aqadm.drop_queue_table(queue_table =>'SYS.KUPC$DATAPUMP_QUETAB', force=> TRUE);

PL/SQL procedure successfully completed.




SQL> BEGIN
  2  dbms_aqadm.create_queue_table(queue_table =>                                               'SYS.KUPC$DATAPUMP_QUETAB',
  3    multiple_consumers => TRUE,
  4    queue_payload_type =>'SYS.KUPC$_MESSAGE',
  5    comment => 'DataPump Queue Table',
  6    compatible=>'10.1.0');
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      IF SQLCODE = -24001 THEN NULL;
 10      ELSE RAISE;
 11      END IF;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Let's check invalids again:

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

no rows selected


1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.