Home » RDBMS Server » Server Utilities » Table reorganization (oracle 10g)
Table reorganization [message #605751] Tue, 14 January 2014 04:57 Go to next message
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 Go to previous messageGo to next message
gazzag
Messages: 332
Registered: November 2010
Location: Bristol, 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 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 440
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 Go to previous message
BlackSwan
Messages: 22911
Registered: January 2009
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
Previous Topic: Passing parameter to control file in SQLLDR (SQL LOADER)
Next Topic: Batch job running slow
Goto Forum:
  


Current Time: Fri Oct 24 11:24:11 CDT 2014

Total time taken to generate the page: 0.12241 seconds