Move tables
                                                 Last update (2008-02-24 16:46:29)
                                                                                                                   Date added (2008-02-24 15:54:15)

Summary
To move a table named for example OBJECTS you have first to reassure some things.

One is enough free space at the tablespace that you will move it. For example if you have a table with size 1GBytes that after reseting HWM, with move, will be 300MBytes, you must have at least 300MBytes free space at the tablespace.

The other very important is if table is used by a session.

If you have the required free space and nobody is using it, you can move the table for example to the same tablespace with the command.
ALTER TABLE OBJECTS MOVE PARALLEL 8 NOLOGGING;
Tip: If you have SMP server and setup parallel processing, you can speed up moving using parallel slaves.

Moving the table makes unusable the underlying indexes, so you have to rebuild them. Here is the script to generate the rebuild statements for indexes.
SELECT 'alter index ' || owner || '.' || index_name || ' rebuild parallel 8 nologging;' rebuild_sql 
FROM dba_indexes 
WHERE table_name LIKE 'OBJECTS';
The same prerequisites for enough space at indexe's tablespace follow. If the index from 1GBytes with rebuild, will be 300MBytes, you must have at least 300MBytes free space at the index tablespace.

Don't forget to reduce the parallel degree of the rebuild indexes back to one.
SELECT 'alter index ' || owner || '.' || index_name || ' noparallel;' rebuild_sql 
FROM dba_indexes 
WHERE table_name LIKE 'OBJECTS';
Advantages
1. It is very fast
2. It is very simple (2 commands)
3. You still have the old statistics (same explain plans)
4. You don't get invalid procedures, packages, etc
Disadvantages
1. You have a downtime to the part of application using the specific table
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search