RE: DBMS_REDEF - How online is it, really?

From: TJ Kiernan <>
Date: Tue, 11 Feb 2014 16:38:01 +0000
Message-ID: <>

You have some control over this. The finish procedure will lock the tables briefly in order to maintain transactional integrity.

The process I've used is,

  1. Create the partitioned table,
  2. Start redefinition
  3. Copy/register dependents (indexes, constraints & grants)
  4. Sync the table repeatedly until the amount of time it takes to sync is not significant
  5. Finish redefinition. This will sync the tables one last time & exchange their names, along with the copied/registered dependents' names.

For a table this large, I'd probably finish the process at a time where there's relatively few transactions going on. I'd also consider the storage footprint that this will leave. I hope that this table and its indexes are in their own tablespace, so you can drop it when you're done. I'd define the partitioned table into a new tablespace (possibly compressing old partitions).

T. J.

From: [] On Behalf Of Uzzell, Stephan Sent: Tuesday, February 11, 2014 10:03 AM To:
Cc: Uzzell, Stephan
Subject: DBMS_REDEF - How online is it, really? on OEL 6, in case that matters.

We've been looking into partitioning some large tables (5 trillion rows, 1TB) so that we can better manage them (drop old data, &c.). Right now I'd say dbms_redefinition may be the leading candidate, because Oracle says it is an online operation. I've been burned by Oracle's idea of "online" operations before - alter index rebuild online, if the code is optimized / hinted to use that index, may be online as far as Oracle is concerned, but it sure wasn't for the end users.

So I'm wondering - has anyone used dbms_redefinition to partition large tables? Is it truly online? Can Oracle keep up with inserts in the meantime?

We are thinking about using RAT to try to play back a typical workload, but before we open another can of Oracle worms, I figured a question to the list might be a good place to start.


Stephan Uzzell

Received on Tue Feb 11 2014 - 17:38:01 CET

Original text of this message