RE: How to coalesce an LMT TS to drop extra datafiles?

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Fri, 1 Feb 2013 12:39:03 -0600
Message-ID: <3DC440E2338A24439D527C2E64E1ECBCA85387_at_deeds.pti-nps.com>

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_at_gmail.com] Sent: Friday, February 01, 2013 12:02 PM To: TJ Kiernan
Cc: ORACLE-L; rjoralist2_at_society.servebeer.com 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2013 - 19:39:03 CET