DBMS_REDEFINITION error ORA-01442 - how to proceed?
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2012 12:15:06 -0600
So I received the following error moving a non-partitioned table to a
partitioned one and now I have 2 tables in various states:
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 984
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1727
ORA-06512: at line 2
I found the solution on Metalink for the above, *however* now I have the
following situation:
1. Original Table is now partitioned correctly - the secondary table is now
no longer partitioned.
2. I'm missing constraints on my newly partitioned table, and the constraints
on the interim table have different names
3. FKs from other tables (lots of them) are pointing to the interim table,
not the newly partitioned table
What is the best way to:
a.) move the rest of the dependencies from the interim table over to my new
table? OR
b.) back out/redo/undo the redefinition so that I have my orginal tables back
so that I can run this again?
I was wondering what would happen now if re-ran my redefinition script with the
tables the way they are and set it to ignore errors? I have the distinct
feeling that is not going to work.
I'm almost betting I'm stuck with a manual process to finish cleaning this up.
Chris Taylor
Oracle DBA
Parallon IT&S
--
//www.freelists.org/webpage/oracle-l
Other related posts:
