This content has been marked as final. Show 3 replies
1. Can move it online with DBMS_REDEFINITION
and also see MOS note 149564.1
2. If you have IO due to a problem with the disks it wont solve anything, if you have contention due to chaining or row migration, the table could do with with re-organising and this method can fix that. Prove that first for yourself though with plans pre and post move for any queries hitting that table. A Re-org can also save space as the new table should only have blocks required at the time of the move.
3. Moving it to its own tablespace will allow you run commands on the tablespace level that you could do otherwise. RMAN or TTS for example.
Moving it to its own TS can be good, can be bad, depends on the situation. It doesnt look to be bad here.
1) Consider partitioning the table
2) Move the table to a new tablespace
3) Store the CLOB's in another new tablespace(s).
For example, I moved a 600+G table as follows:
1) Create new tablespaces: one for table and one for each partition's CLOB's.
2) Create partitioned table "AUDIT_NEW"
3) Use datapump (parallel=4) to export old and import into new.
4) Synchronize latest partition with latest data from old table
5) Rename old table to "AUDIT_OLD"
6) Rename new table to "AUDIT"
7) Create the local indexes
Or if you prefer DBMS_REDEFINITION, read this article.
The new tablespace for the audit data may have to be located on the same physical disk, do you feel this would still be a worth while excerise as the new tablespace will be located on the same physical disk so there is no reduction in disk IO to the main tablespace?Having this TS on same HDD may even slow I/O down. Because most likely the new file will be located on cilinders that are more distant from others tables location. This may cause HHD head to move here and there more then previously. I said may because you cannot predict how Oracle will lay table extents in datafile(s) and how OS will lay out file block on HDD. Theoretically it will slow I/O down. A little bit.
If you want to separate this table to improve I/O, consider to place it on another HDD or SSD.