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;
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:
Thanks, saved me completely ... :)
Cheers
Jürgen
(Berlin, Germany)
Thanks a lot...
Very useful and Saved my time. Thanks a lot.
Can it continue from the time of crash?
DB crashed as archive is full. After 24 hours. Is there anyway job can be continued.
Post a Comment