RE: DBMS_REDEF - How online is it, really?
- From: TJ Kiernan <tkiernan@xxxxxxxxxxx>
- To: "'SUzzell@xxxxxxxxxx'" <SUzzell@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Feb 2014 16:38:01 +0000
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).
HTH,
T. J.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Uzzell, Stephan
Sent: Tuesday, February 11, 2014 10:03 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: Uzzell, Stephan
Subject: DBMS_REDEF - How online is it, really?
11.2.0.3.7 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.
Thanks!
Stephan Uzzell
Other related posts:
