Simple script for finding a leftover FK constraint to an interim table after a redefinisjon

After a redefinition of a table, you may be stuck with some FK constraints from other tables, to the old interim tables.

This simple script fixed my problem:

alter session set nls_language='american';
set lines 200
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
col status format a20

SELECT  TABLE_NAME, CONSTRAINT_NAME
FROM    DBA_CONSTRAINTS
WHERE   OWNER = '&&owner'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table');

prompt copy and paste output above as input to owner and table_name:
prompt
ALTER TABLE &&owner..&&table_name DROP CONSTRAINT &constraint_name;
DROP TABLE &&owner..&&interim_table PURGE;
exit

Result:

Enter value for owner: SCOTT
old   3: WHERE   OWNER = '&&owner'
new   3: WHERE   OWNER = 'SCOTT'
Enter value for interim_table: ORDERS_INTERIM
old   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table')
new   5: AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='ORDERS_INTERIM')

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
ORDERROWS                      TMP$$_FK__ORD__ENT0

copy and paste output above as input to owner and table_name:
Enter value for table_name: ORDERROWS
Enter value for constraint_name: TMP$$_FK__ORD__ENT0
old   1: ALTER TABLE &&owner..&table_name DROP CONSTRAINT &constraint_name
new   1: ALTER TABLE SCOTT.ORDERROWS DROP CONSTRAINT TMP$$_FK__ORD__ENT0

Table altered.

old   1: DROP TABLE &&owner..ORDERS_INTERIM PURGE
new   1: DROP TABLE SCOTT.ORDERS_INTERIM PURGE

Table dropped.

How to add a different looking index to your interim table during online redefinition

The procedure REGISTER_DEPENDENT_OBJECT in the DBMS_REDEFINITION package lets you add a dependent object to your interim table during online redefinition, and then "plug it into" your redefintion process.

In my case, I was redefining an incorrectly partitioned table. The interim table was also partitioned, but this time correctly. During the phase where I call the COPY_TABLE_DEPENDENTS procedure to transfer the existing dependant objects (triggers, indexes, constraints etc) from the original table to the interim table, it failed with the message

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2761

This was because I was using the directive dbms_redefinition.cons_orig_params in my code:

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SALES',
orig_table=>'DAILYSALES',
int_table=>'DAILYSALES_INTERIM',
copy_indexes=>dbms_redefinition.cons_orig_params,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
END;
/

As such, the redefinition process attempted to transfer the partitioned index from the original table which had a different partitioning scheme, to the new table who was partitioned differently and had fewer partitions.

As a workaround, I recreated the index directly on the interim table:

CREATE INDEX SALES.SEARCHIX1_INTERIM ON SALES.DAILYSALES_INTERIM
(COL1, COL2 DESC)
TABLESPACE DATA2
LOCAL
PARALLEL ( DEGREE 5 INSTANCES 1 );

Then, "plug it into" the redefinition procedure:

exec DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SALES','DAILYSALES','DAILYSALES_INTERIM',2,'SALES','SEARCHIX1','SEARCHIX1_INTERIM');

Finally, change the directive in your COPY_TABLE_DEPENDENTS procedure from

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

And run the COPY_TABLE_DEPENDENTS procedure again.
When done, finish the redefinition by calling the FINISH_REDEF_TABLE procedure, and you'll see that the interim index on the interim tables has been nicely transfered during the switch.

The Oracle 18c documentation can be found here
Another good source is HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (Doc ID 1304838.1 from Oracle Support)

New parameter in dbms_redefinition.finish_redef_table in 12c

Oracle 12c adds a potentiall very useful feature to the dbms_redefinition package, in the procedure finish_redef_table: dml_lock_timeout

It specifies a time limit in seconds for how long a DML statment waits in a DML lock queue, before the procedure terminates gracefully.

By default, this parameter is set to 0 which means no waiting at all; the procedure will simply error out if it cannot get a lock on the table.

The maximumm value is 1000000 and will cause any DML statements to wait in perpetuity to aquire a DML lock.

Here is an example of its usage, where I have set the limit to 5 minutes (300 seconds):

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'SCOTT',
                                     orig_table=>'EMP',
                                     int_table=>'EMP_INTERIM', 
                                     dml_lock_timeout=>300);
END;
/

How to fix ORA-02449: unique/primary keys in table referenced by foreign keys after an online redefinition

When attempting to drop the interim table after a successful online redefinition, you may get:

ORA-02449: unique/primary keys in table referenced by foreign keys

This is easy to overlook - the online redef procedure does not disable the foreign key relationship from other tables to your (now) obsolete interim table.

To find these tables and their constraints:

SELECT  TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS
FROM    DBA_CONSTRAINTS
WHERE   OWNER = 'SH'
AND     CONSTRAINT_TYPE='R'
AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')

TABLE_NAME	CONSTRAINT_NAME	CONSTRAINT_TYPE	R_CONSTRAINT_NAME	STATUS
INVOICES	TMP$$_INVOICES_FK0	R	TMP$$_SALES_PK0	DISABLED
REFUNDS	TMP$$_REFUNDS_FK0	R	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	R	TMP$$_SALES_PK0	DISABLED

As can be seen from the table above, there are certainly constraints from other tables, pointing to the primary key on the interim table. They are disabled, but nevertheless preventing us from dropping the table.

Let's make sure that each of these "funny named" constraints have a sibling constraint, one that is named correctly after the redefintion:

SELECT TABLE_NAME,CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS
FROM DBA_CONSTRAINTS 
WHERE TABLE_NAME in (
                    SELECT  TABLE_NAME
                    FROM    DBA_CONSTRAINTS
                    WHERE   OWNER = 'Sh'
                    AND     CONSTRAINT_TYPE='R'
                    AND     R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
)
AND CONSTRAINT_TYPE='R'
AND CONSTRAINT_NAME LIKE '%REPLENISHMENT%' OR CONSTRAINT_NAME LIKE '%INVOICES%' OR CONSTRAINT_NAME LIKE '%REFUNDS%'
ORDER BY TABLE_NAME,CONSTRAINT_NAME;

Result:

TABLE_NAME	CONSTRAINT_NAME	R_CONSTRAINT_NAME	STATUS
INVOICES	INVOICES_FK	SALES_PK	ENABLED
INVOICES	TMP$$_INVOICES_FK0	TMP$$_SALES_PK0	DISABLED
REFUNDS	REFUNDS_FK	SALES_PK	ENABLED
REFUNDS	TMP$$_REFUNDS_FK0	TMP$$_SALES_PK0	DISABLED
REPLENISHMENT	REPLENISHMENT_FK	SALES_PK	ENABLED
REPLENISHMENT	TMP$$_REPLENISHMENT_FK0	TMP$$_SALES_PK0	DISABLED

As you can see, the constraints come in pairs and the ones with names starting with TMP$$ are leftovers from the redefinition.

You can safely drop these constraints. Put the following in a .sql script and run it as sysdba:

alter session set nls_language='american';
set lines 200
set pages 0
set heading off
set feedback off
set trimspool on
set verify off
set echo off
spool 7.alter_table.sql

select 'spool 7.alter_table.log' from dual;

SELECT    'alter table '
        || B.OWNER
        || '.'
        || B.TABLE_NAME
        || ' drop constraint '
        || B.CONSTRAINT_NAME
        || ';'
  FROM DBA_CONSTRAINTS A
        FULL OUTER JOIN DBA_CONSTRAINTS B
           ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
  WHERE A.OWNER = 'SH'
  AND A.TABLE_NAME='SALES_INTERIM'
  AND B.R_CONSTRAINT_NAME IS NOT NULL;
select 'exit' from dual;

exit

Run the script, and you will have a new script containing the drop-clauses:

alter table SH.INVOICES drop constraint TMP$$_INVOICES_FK0;
alter table SH.REFUNDS drop constraint TMP$$_REFUNDS_FK0;
alter table SH.REPLENISHMENT drop constraint TMP$$_REPLENISHMENT_FK0;

After these statements have been successfully executed, you can go ahead and drop the interim table.

How to solve ORA-14024 during execution of DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

Background: you are redefining a table and have successfully completed the DBMS_REDEFINITION.START_REDEF_TABLE procedure.

As you are executing the next step, DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, you receive the following error:

ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

Cause: Your interim table has been created with more partitions than the original table you're trying to redefine.

Verify like this:

select 'original table: ' || MAX(PARTITION_POSITION) "number of partitions"
from dba_tab_partitions where table_name='SALES' 
union
select 'interim table: ' || MAX(PARTITION_POSITION) 
from dba_tab_partitions where table_name='SALES_INTERIM' 
;

number of partitions
interim table: 19
original table: 18

A simple solution here is avoiding to create the indexes as a part of the COPY_TABLE_DEPENDENTS, and create them afterwards instead.

Simply change the directive

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

when you execute DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.

When you're done redefining your table, make sure you recreate the indexes.

If this is not acceptable, you need to add another partition to your original table, so that the missing index partitions will be automatically created.


Update 21.06.2021: I faced a similar challenge today as I was trying to redefine a table with interval range partitioning. As I was testing my redefinition strategy in a test environment first, the table in question had 46 partitions with zero rows. This fact raised ORA-14024: number of partitions of LOCAL index must equal that of the underlying table, and I realized I could quite easily work around the problem by simply dropping the empty partitions. To be safe you drop any partitions that actually had rows in them, analyzed them first:

set lines 200
spool analyze_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool analyze_part.log' from dual;
select 'exec DBMS_STATS.GATHER_TABLE_STATS (OwnName=> ''TRACKER'', TabName => ''EVENTS'',partname =>''' || partition_name || ''' granularity => ''PARTITION'';' 
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';

Run the analyze-script:

sqlplus / as sysdba @analyze_part.sql

Afterwards, the num_rows column for the same partitions should show 0 (zero) rows. In other words, they can be safely dropped.

To generate a "drop partition" script, put the following in an sql-script:

set lines 200
spool drop_empty_part.sql
set   trimspool on
set   verify off
set   heading off
set   pages 0
set   echo off
set   feedback off
select 'alter session set nls_language=''american'';' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool drop_empty_part.log' from dual;

select 'ALTER TABLE ' || table_owner || '.' || table_name ||  ' DROP PARTITION ' || PARTITION_NAME ||  ' UPDATE INDEXES;'
from dba_tab_partitions
where table_name='EVENTS'
and num_rows = 0
and partition_name like 'SYS%';
select 'exit' from dual;
exit

Execut the script:

sqlplus / as sysdba @drop_empty_part.sql

The number of partitions in the original table and the interim table should now match. Now, execute the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS again. You shouldn't see the ORA-14024 this time around :-)

Potential solution for ORA-12008: error in materialized view or zonemap refresh path

During the synch-phase of an online redefintion, I received the error:

begin
ORA-42009: error occurred while synchronizing the redefinition
ORA-12008: error in materialized view or zonemap refresh path
ORA-01843: not a valid month
ORA-06512: at "SYS.DBMS_REDEFINITION", line 219
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5392

Solution:
Alter your session setting for NLS_TERRITORY. Add the line

alter session set nls_territory='';

to your script.


This seems to be due to the fact that I am partitioning on a virtual column, using a column that is in turn of datatype TIMESTAMP:

CREATE TABLE USER1.TAB1_INTERIM
(
ENTRY_ID             VARCHAR2(36 BYTE)        NULL,
DNAME                VARCHAR2(11 BYTE)        NULL,
DTYPE                VARCHAR2(64 BYTE)        NULL,
CREATED_TIME         TIMESTAMP(6)             NULL,
DYEAR                INTEGER                  NULL,
CREATED_DAY AS (
CAST(
TO_CHAR(CREATED_TIME,'DD.MM.YYYY')
AS DATE
)
) VIRTUAL
)
TABLESPACE MYTABLESPACE
PARTITION BY RANGE (CREATED_DAY)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('01.01.2017', 'DD.MM.YYYY'))
TABLESPACE MYTABLESPACE
);

Since the creator of the database didn't set the NLS_TERRITORY to NORWAY, it is using the default value "AMERICA".

Without any altering of the default NLS setting, the CREATED_TIME column will therefore contain data formatted according to US standard:

select created_time from user1.tab1 fetch first 1 rows only;

CREATED_TIME
--------------------------
26-SEP-17 09.30.17.040000 AM

I would like the data to be formatted according to Norwegian locale rules, so I use 'DD.MM.YYYY' in my virtual column. So I tested in sqlplus:

alter session set nls_territory='NORWAY';
select created_time from user1.tab1 fetch first 1 rows only;

CREATED_TIME
--------------------------
26.09.2017 09.30.17,040000

So my script would look like this:

alter session set nls_territory='NORWAY';
begin
   DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>'USER1',orig_table=>'TAB1',int_table=>'TAB1_INTERIM');
end;
/

and it completed successfully.

Solution for ORA-01442: column to be modified to NOT NULL is already NOT NULL during online redefinition

When trying to execute dbms_redefinition.copy_table_dependents, like this:

whenever sqlerror exit
set serveroutput on
set feedback off
set verify   off
set timing on

DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'USER1',
orig_table=>'DOCUMENTS',
int_table=>'DOCUMENTS_INTERIM',
copy_indexes=>0,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

You get the following error:

ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646

Cause:
Your interim table has NOT NULL constraints. This is easy to overlook, particulary if you create your interim table using CTAS ("Create Table As Select") statement.
Your interim table should not have any constraints before you execute the copy_table_dependents procedure.

Solution:
Drop the NOT NULL constraint, and retry the operation:

SQL> alter table user1.documents_interim drop constraint SYS_C0018782;

Table altered.

Note that you do not have to abort the redefinion procedure at this point, and start all over again.

Simply drop the constraint, and retry your operation.

A workaround for ORA-12008 and ORA-14400 during online redefinition

To introduce partitions in one of my tables, I was using online redefinition of a rather large table.
The following error was thrown after about 30 minutes after having started the redefinition with dbms_redefintion.start_redef:

begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P004
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

It turned out that there were NULL values in the column that would be the future partition key.
Everything was explained very well in Doc ID 2103273.1 "ORA-14400: inserted partition key does not map to any partition, with possible ORA-12008: error in materialized view refresh path".

You can solve this in two ways:

a) find the rows with null values, and update them

or

b) use an overflow partition which will work as a "catch all" basket for rows that can't be mapped to a specific partition. NULL values sort in this category.

Since I was using interval range partitioning, I had to choose the former of the two options. If this is not possible, you can't use interval partitioning, and need to explicitly define every partition + your overflow partition in your interim table.

How to work around ORA-12008 and ORA-01858 during an online redefinition

The following error occured during redefintion:

Move method (cons_use_pk or cons_use_rowid): cons_use_rowid
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_REDEFINITION", line 75
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3459
ORA-06512: at line 2

The reason:
One of the reasons why I wanted to redefine the table, was to make it partitioned.
One of the column in the interim table was therefore defined as DATE instead of VARCHAR2:

Original table:

CREATE TABLE SCOTT.MYTABLE
(
  SEQ_NUM              NUMBER Generated as Identity ( START WITH 9984 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) CONSTRAINT SYS_C0073650 NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  ENAME                VARCHAR2(100 BYTE)           NULL,
  CREATED_DT           TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  ACTIVE_YEAR          INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  CONDITION            VARCHAR2(50 BYTE)            NULL,
  FN_ID                VARCHAR2(11 BYTE)            NULL
)
TABLESPACE USERS;

Interim table:

CREATE TABLE SCOTT.MYTABLE_INTERIM
(
  SEQ_NUM              NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  ENAME                VARCHAR2(100 BYTE),
  CREATED_DT           TIMESTAMP(6),
  REVISION#            INTEGER,
  ACTIVE_YEAR          INTEGER,
  PERIOD               DATE,
  CONDITION            VARCHAR2(50 BYTE),
  FN_ID                VARCHAR2(11 BYTE)
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2015-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

The requirement from the Developers was that the partitions should be range partitioned on dates in the format 'YYYY-MM'.

When starting the online redefintion process, I hit the error at the top in this post.

I started to search for the root cause, and executed the following:

select seq_num,TO_DATE(PERIOD,'YYYY-MM') from Scott.mytable
fetch first 5 rows only;

which resulted in

ORA-01858: a non-numeric character was found where a numeric was expected

It turned out that the following SQL would return the data I wanted, in the correct format, defined with the correct data type:

select seq_num,to_date(to_char(TO_DATE(PERIOD),'YYYY-MM'),'YYYY-MM')
from Scott.mytable
fetch first 5 rows only;

This Expression had to go into the redefinition-command, like this:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'EVENT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'SEQ_NUM SEQ_NUM,ENTRY_ID ENTRY_ID,ENAME ENAME,CREATED_DT CREATED_DT,REVISION# REVISION#,ACTIVE_YEAR ACTIVE_YEAR,to_date(to_char(TO_DATE(PERIOD),''YYYY-MM''),''YYYY-MM'') PERIOD,CONDITION CONDITION,FN_ID FN_ID',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

After this change, the redefinition executed successfully.

How to redefine a table that is created with an identity column

You have the following non-partitioned table defined:

CREATE TABLE MYTABLE
(
  ENTRY_SEQ_NUM        NUMBER Generated as Identity ( START WITH 1180965539 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP) NOT NULL,
  ENTRY_ID             VARCHAR2(50 BYTE)            NULL,
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
);

The table is growing, and your customer want it partitioned.

Solution:

Create an interim table. Make sure to use a datatype of NUMBER for the column ENTRY_SEQ_NUM, instead of the Identity column:

CREATE TABLE MYTABLE_INTERIM
(
  ENTRY_SEQ_NUM        NUMBER,
  ENTRY_ID             VARCHAR2(50 BYTE),
  DOC_NAME             VARCHAR2(100 BYTE)           NULL,
  CREATED              TIMESTAMP(6)                 NULL,
  REVISION#            INTEGER                      NULL,
  APPLICABLE_YEAR      INTEGER                      NULL,
  PERIOD               VARCHAR2(10 BYTE)            NULL,
  DOCUMENT_STATUS      VARCHAR2(50 BYTE)            NULL,
  PHONE#               VARCHAR2(11 BYTE)            NULL
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
  PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01', 'YYYY-MM'))
)
TABLESPACE USERS
;

Start redefinition. Note that you need to use the TO_DATE function on the partition key column, together with proper masking of the date format string:

begin
DBMS_REDEFINITION.start_redef_table(uname=>'SCOTT',
orig_table=>'MYTABLE',
int_table=>'MYTABLE_INTERIM',
col_mapping =>'ENTRY_SEQ_NUM ENTRY_SEQ_NUM,ENTRY_ID ENTRY_ID,DOC_NAME DOC_NAME,CREATED CREATED,REVSION# REVSION#,APPLICABLE_YEAR APPLICABLE_YEAR,TO_DATE(PERIOD,''YYYY-MM'') PERIOD,DOCUMENT_STATUS DOCUMENT_STATUS,PHONE# PHONE#',
options_flag=>dbms_redefinition.cons_use_pk);
end;
/

Finish the redefinition:

begin
 DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'SCOTT',orig_table=>'MYTABLE',int_table=>'MYTABLE_INTERIM);
end;
/

Add a new column, of type "Identity":

alter session force parallel ddl;
alter session force parallel dml;
alter table mytable
add ENTRY_SEQ_NUM_X NUMBER Generated as Identity ( START WITH 1
                                                   MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20
                                                   NOORDER NOKEEP);

Drop the old column:

alter table mytable drop column ENTRY_SEQ_NUM;

Rename the Identity column back to the name of the column you just dropped:

alter table mytable rename column ENTRY_SEQ_NUM_X to ENTRY_SEQ_NUM;

How to partition a table using a virtual column and online redefinition

Consider the following table:

desc DOCUMENT_SUMMARY
Navn                                      Null?    Type
----------------------------------------- -------- ----------------------------
DOCUMENTNAME                              NOT NULL VARCHAR2(100)
SUMMARY                                            VARCHAR2(32)

A SELECT * FROM DOCUMENT_SUMMARY reveals contents of the following type (excerpt only):

DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014	9b37f5bcee4ce643058ee633359b3253
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009	17bf7170217960f303755bdd4c648676

The table is quite big, and expected to grow.
The customer would like to get it partitioned.

The column DOCUMENTNAME already contains a string that is partly generated from a date, as revealed by the query below:

SELECT SUBSTR(DOCUMENTNAME,32,7) "datepart" 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

datepart
2015-01
2015-01

We could most likely use a substring of the value to create a new, virtual column and use it as a partition key.

So let's do that:

CREATE TABLE DOCUMENT_SUMMARY_INTERIM
(
  DOCUMENTNAME  VARCHAR2(100 BYTE),
  SUMMARY       VARCHAR2(32 BYTE),
  PERIOD AS (
     CAST(
        TO_DATE(SUBSTR(documentname,32,7),'YYYY-MM') 
        AS DATE)
  ) VIRTUAL
)
PARTITION BY RANGE(PERIOD)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH') )
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2013-01','YYYY-MM') )
)
TABLESPACE USERS
;

Run through a normal online redefinition, and the tables should be switched seamlessly.

Selecting from the new table shows that the date is being generated as a virtual column for every row:

SELECT * 
FROM DOCUMENT_SUMMARY
FETCH FIRST 2 ROWS ONLY;

DOKUMENTNAME	SUMMARY	PERIOD
DOCUMENT1:2015-08:9200060665151:9000010707472:9000044000014	f1081c88eb379b736954641589d5715b	01.08.2015
DOCUMENT2:2016-01:9200060665151:8837773777371:9000088000009	051722d803b2fbaca4192a047b7689a6	01.01.2016

You can verify your partition key attributes with this SQL:

SELECT K.COLUMN_NAME, C.DATA_TYPE,C.VIRTUAL_COLUMN ,PT.INTERVAL
FROM DBA_PART_KEY_COLUMNS K JOIN DBA_TAB_COLS C
    ON (K.COLUMN_NAME = C.COLUMN_NAME) 
        JOIN DBA_PART_TABLES PT ON PT.TABLE_NAME = C.TABLE_NAME
WHERE  K.NAME='DOCUMENT_SUMMARY' ;

COLUMN_NAME	DATA_TYPE	VIRTUAL_COLUMN	INTERVAL
PERIODE	DATE	YES	NUMTOYMINTERVAL(1,'MONTH')

Preparations to make before running dbms_redefinition on a large table

To avoid ORA-01555 "snapshot too old", you should take the following precaution before starting an online redefinition of a large table:

1. If possible, create a new BIGFILE undo tablespace:

CREATE BIGFILE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/u02/oradata/proddb01/undotbs02_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE;

2. Make the new UNDO tablespace database default:

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

3. Alter the retention of UNDO data to GUARANTEED retention:

ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

4. If possible, create a new BIGFILE temporary tablespace:

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/u02/oradata/proddb01/temp2_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5. Make the new temporary tablespace the database default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

6. Empty the recyclebin. As sysdba:

PURGE RECYCLEBIN;

7. Adjust the parameter undo_retention
Set the parameter high enough to sustain operation of the database for the amount of time you expect the redefintion to last.
For example, if you expect the redefinition to run for 48 hours, set the undo_retention to 172800 seconds:

alter system set undo_retention = 172800 scope=memory;

When the online redefinition has completed, return the UNDO tablespace to its normal retention:

ALTER TABLESPACE UNDOTBS2 RETENTION NOGUARANTEE;

About RETENTION GUARANTEED:

... specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

About RETENTION NOGUARANTEE:

... returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default

Source: Oracle Documentation

More about undo retention can be found here

Setting the minimum undo retention period

Which privileges are needed to execute DBMS_REDEFINITION package?

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE.

In addition to having execute privileges on this package, you must be granted the following privileges:

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

LOCK ANY TABLE

SELECT ANY TABLE

In addition, the following privileges are required to execute COPY_TABLE_DEPENDENTS procedure:

CREATE ANY TRIGGER

CREATE ANY INDEX

Source: Oracle Documentation

How to perform an online reorganization of a table - step by step

First, check that your user is eligeble for usage of the DBMS_REDEFINITION package. It requires generous privileges.

Step 1: determine if the table can be reorganized:

whenever sqlerror exit
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept move_method prompt 'Move method (cons_use_pk or cons_use_rowid): '

set serveroutput on
EXEC DBMS_REDEFINITION.can_redef_table('&&owner','&&table_name',dbms_redefinition.&&move_method);
exit

Step 2: Create an interim table with the desirable layout:
If the outcome of step one was positive, you can proceed. You can for example create the interim table partitioned, while the original table is a normal table without partitions.

Important:
If you copy/paste DDL from SQL Developer, TOAD or similar tools, and use this as a template for your interim table, make sure *all* columns allow NULL values.
Otherwise, you will receive errors when running the COPY_TABLE_DEPENDENTS procedure in step 4

Step 3: start the redefinition:

set serveroutput on
set feedback off
set verify   off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table_name prompt 'Interim table name: '
accept move_method prompt 'Move method (cons_use_pk or cons_use_rowid): '

whenever sqlerror exit
set feedback off
set verify off
begin
DBMS_REDEFINITION.start_redef_table(uname=>'&&owner', 
orig_table=>'&&table_name', 
int_table=>'&&interim_table_name',
col_mapping=>NULL,
options_flag=>dbms_redefinition.&&move_method);
end;
/
exit

Step 4: Copy the dependent object to the interim table

If you want the indexes from the original table to be copied over along with triggers, constraints etc, use the directive

copy_indexes=>dbms_redefinition.cons_orig_params

If you prefer creating the indexes manuall after you have finished the redefinition, use

copy_indexes=>0

whenever sqlerror exit
set serveroutput on
set feedback off
set verify   off

accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '

DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'&&owner',
orig_table=>'&&table_name', 
int_table=>'&&interim_table',
-- copy_indexes=>dbms_redefinition.cons_orig_params,
copy_indexes=>0,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
exit

Step 5: synchronize the interim table with potential intermittent transactions:

whenever sqlerror exit
set serveroutput on
set feedback off
set verify   off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
begin
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>'&&owner',orig_table=>'&&table_name',int_table=>'&&interim_table');
end;
/
exit

Step 6: finish the redefinition:

whenever sqlerror exit
set serveroutput on
set feedback off
set verify   off
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>'&&owner',
orig_table=>'&&table_name', 
int_table=>'&&interim_table');
end;
/
exit

At any time before the execution of FINISH_REDEF_TABLE, you can chose to abort the procedure by calling the dbms_redefinition.abort_redef_table procedure:

whenever sqlerror exit
accept table_name prompt 'Table name: '
accept owner prompt 'Table owner: '
accept interim_table prompt 'Interim table name: '
set serverout on
exec dbms_redefinition.abort_redef_table(uname=>'&&owner',orig_table=>'&&table_name',int_table=>'&&interim_table');
exit

Tip: when you create your interim table, give it a short name, like MYTABLE.
I have had cases where longer names, even though they are below the 30 character limit, will create trouble during the execution of the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure.

Errors during the redefinition can be seen from the DBA_REDEFINITION_ERRORS view:

SET LONG 2000
SET LINES 300
COL OBJECT_TYPE FORMAT A20
COL OBJECT_OWNER FORMAT A10
COL OBJECT_NAME FORMAT A20
COL BASE_TABLE_NAME FORMAT A20
COL DDL_TXT FORMAT A50
COL ERR_TXT FORMAT A100
 

SELECT OBJECT_TYPE, OBJECT_OWNER, OBJECT_NAME,BASE_TABLE_NAME,DDL_TXT, ERR_TXT   
FROM DBA_REDEFINITION_ERRORS;

The reason for "ORA-12052: cannot fast refresh materialized view" during Online Redefinition

Online Redefinition of an object relies on Material View (MView) logs to keep track of all data changes in the table under redefinition *while* the redefintion is going on.

If there is any change in the definition (DDL), then there is no guarantee that the Mview logs can capture all changes (they can only track DML )

The internal process works like this:

- Set up Mview log to track any changes on the table and consider the temp table as an MView of the original table
- Transfer all the data in the original table to the temporary table
- WHILE this is going on, any DATA changes to the table will be logged in the MView log
- If you perform a SYNC, Oracle essentially performs a "MVIEW FAST REFRESH" using the MView log to replicating the data changes. The MView log is then cleared.
- Once the data transfer is complete, the MView log (if not empty) is processed - the "MView" / temp table is now up-to-date.
- Upon executing dbms_redefinition.finish_redef_table, the MView log is dropped, the MView is converted back to simple table.
- After that, the names of the original and temp table are switched automatically.

If at any point during this process, the structure of the table is changed or DDL in general is executed (say, TRUNCATE, adding a column, dropping a partition, exchanging a partition), then the MView log cannot in itself guarantee it has all the information needed to "replicate" this change of the original table.

Hence, the MView log is regarded as "unusable" and the error you observed occurs:


ORA-42009: error occurred while synchronizing the redefinition
ORA-12052: cannot fast refresh materialized view BWALM.TOMBA
ORA-06512: at "SYS.DBMS_REDEFINITION", line 119
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1740
ORA-06512: at line 2


Oracle therefore recommend to avoid all DDL on the original table while ONLINE REDEFINITION is ongoing.