Monday, January 11, 2016

DBMS_REDEFINITION was killed or stopped

How to resolve this issue even the DBMS_REDEFINITION was killed or stopped :
If we start the REDEFINITION with below

SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname      => 'TESTFLP',
orig_table => 'TAP_AUDIT_TRAIL',
int_table  => 'TAP_AUDIT_TRAIL_TMP');
END;
/   2    3    4    5    6    7

suddenly realised something is wrong or taking to long time.
If we kill the session next time when you start the REDEFINITION it will throw the below error
BEGIN
*
ERROR at line 1:
ORA-23539: table "TESTFLP"."TAP_AUDIT_TRAIL" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 2

To remove the error first try with the below:

BEGIN
DBMS_REDEFINITION.abort_redef_table(
uname      => 'TESTFLP',
orig_table => 'TAP_AUDIT_TRAIL',
int_table  => 'TAP_AUDIT_TRAIL_TMP');
END;
/   2    3    4    5    6    7

=====
In case it is done on partition table

DBMS_REDEFINITION.ABORT_REDEF_TABLE (
   uname       IN VARCHAR2,
   orig_table  IN VARCHAR2,
   int_table   IN VARCHAR2,
  part_name    IN  VARCHAR2 := NULL);

 ABORT_REDEF_TABLE Procedure Parameters

Parameter    Description
uname

The schema name of the tables.

orig_table

The name of the table to be redefined.

int_table

The name of the interim table.

part_name

The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.

================
If nothing works then below

drop materialized view TESTFLP.TAP_AUDIT_TRAIL_TMP;
DROP SNAPSHOT LOG ON TESTFLP.TAP_AUDIT_TRAIL;
drop table TESTFLP.TAP_AUDIT_TRAIL_TMP;

5 comments:

Anonymous said...

Thanks, saved me completely ... :)

Cheers

Jürgen

(Berlin, Germany)



Anonymous said...

Thanks a lot...

Anonymous said...

Very useful and Saved my time. Thanks a lot.

Unknown said...

Can it continue from the time of crash?

Unknown said...

DB crashed as archive is full. After 24 hours. Is there anyway job can be continued.