| Table reorganization [message #605751] |
Tue, 14 January 2014 04:57  |
kanis
Messages: 61
Registered: November 2006
|
Member |
|
|
We are using oracle 10g. some of our Tables contains clob data and size is huge >700GB.
and huge space is fragmented in these tables. We need to reorg these tables.
Currently We are going with partitioning these tables. There are 3 approach to copy the data from the non partition table to partition table.
1. Create a blank partition table and select clob data from no partition table and insert that using script
2. Create a blank single partition table and do exchange partition and then split the single partition into multiple partition
3. Partitioning an Existing Table using DBMS_REDEFINITION
We need to know which can be done at the quickest . We have to complete it with 8-10 hr outage . Any opinion?
|
|
|
|
| Re: Table reorganization [message #605752 is a reply to message #605751] |
Tue, 14 January 2014 05:43  |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
What is your full database version? i.e. output from:
SQL> select * from v$version;
I had issues using DBMS_REDEFINITION in 10.2.0.4 I think, but I'd have to check my notes to be sure.
|
|
|
|
| Re: Table reorganization [message #605754 is a reply to message #605751] |
Tue, 14 January 2014 06:11 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
DBMS_REDEFINITION is the slowest one and is to be used only if you have no (or very short) downtime.
Splitting partitions in also very slow.
INSERT SELECT (1) in append and parallel mode (if your hardware can support it) is the best way, I think.
[Updated on: Tue, 14 January 2014 06:11] Report message to a moderator |
|
|
|
| Re: Table reorganization [message #605879 is a reply to message #605754] |
Wed, 15 January 2014 14:23 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
If you do have downtime, another option is datapump using parallel threads.
I reorg'd a 2TB size table (18+ million rows) in less than 8 hours using parallel 8.
The caveat is you need disk space to do it.
Also what helps response time for queries, is to use separate tablespaces for the data and the LOB's
Good luck!
[Updated on: Wed, 15 January 2014 14:23] Report message to a moderator |
|
|
|
| Re: Table reorganization [message #605880 is a reply to message #605879] |
Wed, 15 January 2014 14:45 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>Also what helps response time for queries, is to use separate tablespaces for the data and the LOB's
only if underlying data files reside on different physical disks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
