RE: How to coalesce an LMT TS to drop extra datafiles?
- From: "TJ Kiernan" <tkiernan@xxxxxxxxxxx>
- To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- Date: Fri, 1 Feb 2013 12:39:03 -0600
I vaguely remember some bugs in an unpatched install of 11.2.0.3 that stopped
me dead in my tracks, but patches were available at the time. My test database
is running 11.2.0.3 patch 6 (Windows x64), and DBMS_REDEFINITION works fine on
tables with triggers for me.
Another gotcha that I just remembered though is foreign keys - if you move a
parent table, the children will need to be manually repointed to the new
(redefined) table. Really, you'll just want to watch for invalid objects that
depend on the table while you're running the finish_redefinition procedure and
compile/rebuild them as necessary. I think I posted the following a couple of
weeks ago, but it's useful to run in a second session while you're running the
finish_redefinition procedure:
DECLARE
l_LOOP_CNT number := 0;
cursor c1 is
select distinct TYPE,
cmd
from (
select dd.TYPE,
'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 = <your schema here>
and dd.REFERENCED_NAME in (<your table names here>)
and do.STATUS = 'INVALID')
order by TYPE
;
BEGIN
LOOP
EXIT WHEN l_LOOP_CNT = 20;
FOR rec in c1 LOOP
BEGIN
execute immediate rec.CMD;
dbms_output.PUT_LINE('Command succeeded: ' || rec.CMD);
EXCEPTION
when others then
dbms_output.PUT_LINE(rec.CMD);
dbms_output.PUT_LINE('--FAILURE due to ' || sqlerrm);
END;
END LOOP;
dbms_lock.SLEEP(20);
l_LOOP_CNT := l_LOOP_CNT + 1;
END LOOP;
dbms_output.PUT_LINE('Loop count: ' || l_LOOP_CNT);
END;
/
Thanks,
T. J.
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Friday, February 01, 2013 12:02 PM
To: TJ Kiernan
Cc: ORACLE-L; rjoralist2@xxxxxxxxxxxxxxxxxxxxx
Subject: RE: How to coalesce an LMT TS to drop extra datafiles?
We've run into a horrible bug with dbms_redefinition on 11.2 where a trigger
owned by sys for pl/sql scope breaks a unique constraint also owned by sys and
terminates the redefining session halfway through with a 604 IIRC we then got
the same error inserting into any table with a trigger on it. This may have
applied only where the table being redefined had a trigger on it.
DBMS_REDEFINITION will handle *most* of the rename/cleanup for you, if you let
it. Look at the copy_dependents & register_dependents procedures.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
