RE: DBMS_REDEFINITION error ORA-01442 - how to proceed?
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <tkiernan@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Dec 2012 14:52:01 -0600
Thanks TJ - I went the manual cleanup route. Used Toad to generate the FK
scripts that pointed to the table in question and dropped them as they were
pointed to the interim table then recreated them.
Also recreated all my indexes with partitioning instead of local so I learned a
few things:
1.) Create index scripts for partitioned indexes before the redef
2.) Create FK scripts for tables that reference the table to be redefined
3.) Create Constraint Scripts for the table to be redefined
4.) Create Ref constraint scripts pointing to parent tables
5.) Drop all the above existing indexes, constraints.
6.) Create interim table with structure I want
7.) Load interim table with data from original
8.) Drop original table
9.) Rename interim table as original name
10.) Run all scripts generated before beginning :)
Much simpler than if redefinition fails LOL
Chris
-----Original Message-----
From: TJ Kiernan [mailto:tkiernan@xxxxxxxxxxx]
Sent: Wednesday, December 19, 2012 2:46 PM
To: Taylor Christopher - Nashville; oracle-l@xxxxxxxxxxxxx
Cc: TJ Kiernan
Subject: RE: DBMS_REDEFINITION error ORA-01442 - how to proceed?
I went through something similar just last week. Whether you can redefine back
to the original table depends on what, if any constraints, indexes, etc you
register vs copy during the redefinition. I did this inconsistently from one
table to another, as I wanted to clean up some novice mistakes (system-named
constraints, etc) I made a few years ago. If the goal is the partitioned
table, then I'd say clean up the mess. You're almost there.
Cleaning it up is not so bad (as long as you don't have too many referenced
constraints on your table). Something like this will write your SQL for you:
select 'ALTER table ' || lower(owner) || '.' || table_name || ' drop constraint
' || constraint_name || ';' cmd
from dba_constraints
where r_constraint_name = '&1'
;
select 'ALTER table ' || lower(owner) || '.' || table_name || ' add constraint
' || constraint_name || ' foreign key (<your FK here>) references <referenced
table>(<your PK here>);' cmd
from dba_constraints
where r_constraint_name = '&1'
;
And then this to recompile packages, functions, procedures views, etc. This
failed on me for synonyms, but I have so few of them that I just spotted the
errors & dropped & recreated them.
select distinct TYPE,
cmd
from (
select 'ALTER ' || lower(decode(dd.TYPE, 'PACKAGE BODY', 'PACKAGE', dd.TYPE))
|| ' ' || dd.OWNER ||'.' || dd.NAME || ' compile ' ||
decode(dd.TYPE, 'PACKAGE BODY', 'BODY', null) as cmd
from dba_dependencies dd
join dba_objects do
on dd.OWNER = do.OWNER
and dd.NAME = do.OBJECT_NAME
and dd.TYPE = do.OBJECT_TYPE
where dd.REFERENCED_OWNER = '%1'
and dd.REFERENCED_NAME in ('Your table names here')
and do.STATUS = 'INVALID')
order by TYPE
;
HTH,
T. J.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Wednesday, December 19, 2012 12:15 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: DBMS_REDEFINITION error ORA-01442 - how to proceed?
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: