Oracle Database Export ORA

Error Type and Description: an error occurs when the expdp export table is in Table analysis. ORA-39127: unexpectederrorfromcalltoexport_string: SYS. Capacity (CAPTURE_QUEUE_T_ALL1, STRMADMIN, 11.02.00.00.00, newblock) ORA-01403: noda

Error Type and Description: an error occurs when the expdp export table is in Table analysis. ORA-39127: unexpected error from call to export_string: = SYS. Loads ('capture _ QUEUE_T_ALL1 ', 'strmadmin', '11. 02.00.00.00 ', newblock) ORA-01403: no da

Error Type and description:

An error occurs when an expdp export table is in Table analysis.
ORA-39127: unexpected error from call to export_string: = SYS. Loads ('capture _ QUEUE_T_ALL1 ', 'strmadmin', '11. 02.00.00.00', newblock)
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SYS. DBMS_AQ_SYS_EXP_ACTIONS", line 123
ORA-06512: at "SYS. DBMS_AQ_EXP_QUEUE_TABLES", line 20
ORA-06512: at line 1
ORA-06512: at "SYS. DBMS_METADATA", line 9081

Error cause

1. user SYS user transaction is invalid
2. user SYS user's transaction is deleted

3. An invalid object exists.

4. The previous stream replication has not been cleaned up, and a lot of residual garbage results in invalid objects.

Solution:

1. delete from exppkgact $ where schema = 'exfsys ';

2. log on to the database with TOAD and delete invalid objects.

3. Delete residual stream copy Spam

Object Owner Cleanup
Log in as the user who owns the queue_table. (This username will replace Value where referred to below .)

Within sqlplus


SQL> SELECT object_name, object_type FROM user_objects WHERE object_name like '% % ';
Where Is replaced with the appropriate value.
Depending on what parts of the queue table remain you cocould see a number of different objects.

SELECT object_name, object_type FROM user_objects
WHERE object_name like '% ALL1 %' or object_name like '% ALL3 %' or object_name like '% ALL2 % ';

1. Drop any views associated Via drop view ....
Drop view aq $ CAPTURE_QUEUE_T_ALL1_R;
Drop view aq $ CAPTURE_QUEUE_T_ALL1;
Drop view aq $ _ CAPTURE_QUEUE_T_ALL1_F;
Drop view aq $ CAPTURE_QUEUE_T_ALL1_S;


Drop view aq $ CAPTURE_QUEUE_T_ALL2_R;
Drop view aq $ CAPTURE_QUEUE_T_ALL2;
Drop view aq $ _ CAPTURE_QUEUE_T_ALL2_F;
Drop view aq $ CAPTURE_QUEUE_T_ALL2_S;


Drop view aq $ CAPTURE_QUEUE_T_ALL3_R;
Drop view aq $ CAPTURE_QUEUE_T_ALL3;
Drop view aq $ _ CAPTURE_QUEUE_T_ALL3_F;
Drop view aq $ CAPTURE_QUEUE_T_ALL3_S;

2. Drop any sequences associated Via drop sequence ....
Drop sequence AQ $ _ capture_queue_t_alllimit N;
Drop sequence AQ $ _ CAPTURE_QUEUE_T_ALL2_N;
Drop sequence AQ $ _ CAPTURE_QUEUE_T_ALL3_N;


3. alter session set events '2017 trace name context forever, level 2 ';


4. DROP TABLE Cascade constraints;
Drop table CAPTURE_QUEUE_T_ALL1 cascade constraints;
Drop table CAPTURE_QUEUE_T_ALL2 cascade constraints;
Drop table CAPTURE_QUEUE_T_ALL3 cascade constraints;


5. Repeat for all the following tables
Drop table aq $ _ _ Cascade constraints;
Where Is replaced by the appropriate value.


Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_H cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_G cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_C cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_D cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_ I CASCADE CONSTRAINTS;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_S cascade constraints;
Drop table aq $ _ capture_queue_t_allt t cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_L cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL1_P cascade constraints;


Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_H cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_G cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_C cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_D cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_ I CASCADE CONSTRAINTS;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_S cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_T cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_L cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL2_P cascade constraints;


Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_H cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_G cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_C cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_D cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_ I CASCADE CONSTRAINTS;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_S cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_T cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_L cascade constraints;
Drop table aq $ _ CAPTURE_QUEUE_T_ALL3_P cascade constraints;

SYSTEM User Cleanup
Connect as system
1. SELECT schema, name, objno from system. AQ $ _ QUEUE_TABLES WHERE name LIKE '% % ';
Take a note of the schema, objno, and name, for the relevant schema, if any is returned.


SELECT schema, name, objno from system. AQ $ _ QUEUE_TABLES WHERE name IN ('capture _ QUEUE_T_ALL1 ', 'capture _ QUEUE_T_ALL2', 'capture _ QUEUE_T_ALL3 ')


2. SELECT oid, eventid, table_objno, name from system. AQ $ _ queues where name IN (' ',' ',....);
Take a note of the oids, table_objnos and names. the oid values, I. e, OID_1, etc ., eventid and names, e.g. QUEUE_NAME_1, etc ., will be used in the Data Dictionary Cleanup p.


SELECT oid, eventid, table_objno, name from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E ');


3. delete from system. AQ $ _ QUEUE_TABLES where objno = ;


Delete from system. AQ $ _ QUEUE_TABLES where objno IN (SELECT objno from system. AQ $ _ QUEUE_TABLES WHERE name IN ('capture _ QUEUE_T_ALL1 ', 'capture _ QUEUE_T_ALL2', 'capture _ QUEUE_T_ALL3 ')
)


4. delete from system. AQ $ _ queues where table_objno = ;


Delete from system. AQ $ _ queues where table_objno IN (SELECT table_objno from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


Commit;


Data Dictionary Cleanup
Connect as sys as sysdba


Find the owner # corresponding to the relevant From


Select distinct o. owner # from obj $ o, user $ u where o. owner # = u. user # and u. name =' ';
This result will be referred to In the statements below.


Select distinct o. owner # from obj $ o, user $ u where o. owner # = u. user # and u. name = 'strmadmin ';


86


Then


1. (a) and (B) are valid for all versions. (c) and (d) are valid only
9.2.0.1 and above.






Queues (type #24)
(A) SELECT obj #, name from sys. OBJ $ where type # = '24' AND owner # = AND name IN (' ',' ',....);
Where , Etc., are those found from the select of queue names.


SELECT obj #, name from sys. OBJ $ where type # = '24' AND owner # = '86'
And name in ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ upload ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E ')


Rule Sets (type #46)
(B) SELECT obj #, name FROM SYS. OBJ $ WHERE type # = 46 AND owner # = AND name LIKE '% QUEUE_NAME_1 % ';
Where , Etc., are those found from the select of queue names.


SELECT obj #, name from sys. OBJ $ where type # = 46 AND owner # = 86
And name in ('capture _ QUEUE_ALL1_N ', 'capture _ QUEUE_ALL1_R', 'capture _ done', 'capture _ QUEUE_ALL3_N ', 'capture _ QUEUE_ALL3_R ')


Rules (type #59)
(C) SELECT obj #, name from sys. OBJ $ WHERE type # = 59 AND owner # = AND name LIKE '% QUEUE_NAME_1 % ';
Where , Etc., are those found from the select of queue names.


SELECT obj #, name from sys. OBJ $ WHERE type # = 59 AND owner # = 86 AND ctime> = to_date ('000000', 'yyyymmdd') and ctime <to_date ('20170101', 'yyyymmdd ')
Order by ctime


Evaluation Context (type # = 62)
(D) SELECT obj #, name from sys. OBJ $ WHERE type # = 62 AND owner # = AND name LIKE '% QUEUE_TABLE_NAME % ';
Where Is that found previusly.


SELECT obj #, name from sys. OBJ $ WHERE type # = 62 AND owner # = 86 AND name IN ('aq $ _ CAPTURE_QUEUE_T_ALL1_V ', 'aq $ _ CAPTURE_QUEUE_T_ALL2_V', 'aq $ _ Region ');




(B) DELETE FROM SYS. OBJ $ WHERE obj # IN ( , ,...);
Where , Etc are all the obj # found from Step 1 ().


Delete from sys. OBJ $ where obj # IN (SELECT obj # from sys. OBJ $ where type # = '24' AND owner # = '86'
And name in ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ upload ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E ')
)


Using the name values for Step 1 (c) abve
Execute DBMS_RULE_ADM.DROP_RULE (' . ', TRUE );


SELECT 'execute DBMS_RULE_ADM.DROP_RULE (''' | 'strmadmin. '| name | ''', TRUE);' from sys. OBJ $ WHERE type # = 59 AND owner # = 86 AND ctime> = to_date ('000000', 'yyyymmdd') and ctime <to_date ('20170101', 'yyyymmdd ')
Order by ctime
For example:
Execute DBMS_RULE_ADM.DROP_RULE ('strmadmin. CWREPORT_NAME74761 ', TRUE );




Using the name values for Step 1 (d) abve
-- If the error ORA-24170 error is reported if it cannot be cleared, execute the following statement before clearing:
-- Alter session set events = '25475 trace name context forever, LEVEL 2 ';


Execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT ('strmadmin. AQ $ _ CAPTURE_QUEUE_T_ALL1_V ', TRUE );
Execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT ('strmadmin. AQ $ _ CAPTURE_QUEUE_T_ALL2_V ', TRUE );
Execute DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT ('strmadmin. AQ $ _ CAPTURE_QUEUE_T_ALL3_V ', TRUE );




3. delete from sys. EXPACT $ WHERE owner =' AND name LIKE '% % ';
4. delete from sys. EXPACT $ WHERE owner =' AND name LIKE '% % ';
5. delete from sys. NOEXP $ WHERE owner =' AND name LIKE '% % ';
6. delete from sys. NOEXP $ WHERE owner =' AND name LIKE '% % ';


Commit;


Select * from SYS. NOEXP $ WHERE owner = 'strmadmin' and name in ('aq $ _ CAPTURE_QUEUE_T_ALL1_D ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_P', 'aq $ _ comment', 'aq $ _ comment ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_D ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_P ')


DELETE from SYS. NOEXP $ WHERE owner = 'strmadmin' and name in ('aq $ _ CAPTURE_QUEUE_T_ALL1_D ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_P', 'aq $ _ comment', 'aq $ _ comment ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_D ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_P ')
Commit;


Propagation Cleanup [Optional]
1. delete from sys. AQ $ _ schedules where oid IN (' ',....);
Where Values are those obtained from the SYSTEM Cleanup stage.


SELECT * from sys. AQ $ _ schedules where oid IN (SELECT oid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


Delete from sys. AQ $ _ schedules where oid IN (SELECT oid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));




2. delete from sys. AQ $ _ MESSAGE_TYPES WHERE queue_oid IN (' ',....);
Where Values are those obtained from the SYSTEM Cleanup stage.


SELECT * from sys. AQ $ _ MESSAGE_TYPES WHERE queue_oid IN (SELECT oid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


Delete from sys. AQ $ _ MESSAGE_TYPES WHERE queue_oid IN (SELECT oid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


3. select min (sequence), MAX (sequence) from SYS. AQ $ _ PROPAGATION_STATUS WHERE queue_id IN ('eventid ',....);
Where Values are those obtained from the SYSTEM Cleanup stage.


SELECT * from SYS. AQ $ _ PROPAGATION_STATUS WHERE queue_id IN (SELECT eventid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


Select min (sequence), MAX (sequence) from SYS. AQ $ _ PROPAGATION_STATUS WHERE queue_id IN (SELECT eventid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


4. delete from sys. AQ $ _ PROPAGATION_STATUS WHERE queue_id IN (' ',....);
Where Values are those obtained from the SYSTEM Cleanup stage.


Delete from sys. AQ $ _ PROPAGATION_STATUS WHERE queue_id IN (SELECT eventid from system. AQ $ _ queues where name IN ('capture _ QUEUE_ALL1 ', 'capture _ QUEUE_ALL2', 'capture _ QUEUE_ALL3 ', 'aq $ _ CAPTURE_QUEUE_T_ALL1_E', 'aq $ _ all ', 'aq $ _ CAPTURE_QUEUE_T_ALL3_E '));


5. delete from sys. AQ $ _ PENDING_MESSAGES WHERE sequence ( , Where , Are the minimum and maximum sequence values found in step 3.




Commit;


EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 0_GA1 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ policgy1 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('pagation _ policgy2 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('pagation _ policgy3 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 2_LPS1 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 2_LPS2 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 2_LPS3 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('pagation _ 4_TR1 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('pagation _ 4_TR2 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('pagation _ 4_TR3 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 8_QDN1 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 8_QDN2 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 8_QDN3 ');
EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION ('propagation _ 9_QN1 ');


Exec DBMS_RULE_ADM.DROP_RULE_SET (rule_set_name => 'capture _ QUEUE_ALL2_R ', delete_rules => true );
Exec DBMS_RULE_ADM.DROP_RULE_SET (rule_set_name => 'capture _ QUEUE_ALL2_N ', delete_rules => true );


Alter session set events '2017 trace name context forever, level 8 ';


Event 10046 trace is disabled at session level using
Alter session set events '10046 trace name context off ';

Alter session set events '10851 trace name context off ';
Alter session set events '25475 trace name context off ';

After the above processing, the expdp will not go wrong.