Create table as select (CTAS)
                                                 Last update (2008-04-01 10:45:58)
                                                                                                                   Date added (2008-02-24 19:26:50)

Summary
Another option to reorganize fragmented tables is CTAS (Create Table As Select). In the following steps we reset the HWM for table named OBJECTS.

How to
1. Create a new table
CREATE TABLE OBJECTS_NEW TABLESPACE EXAMPLES 
AS SELECT /*+ parallel(A 8) */ * FROM OBJECTS A;
Tip: If you have SMP server, use parallel to speed up.

2. Save all DDL statements for indexes, triggers, grants of the old table OBJECTS.

3. Drop the old table
DROP TABLE OBJECTS;
4. Rename new table to old one;
RENAME OBJECTS_NEW TO OBJECTS;
5. Run the DDL statements to recreate indexes, triggers, grants

Advantages
No advantage
Disadvantages
1. You have a downtime to the part of application using the specific table
2. It is not fast
3. It is not very simple (especially when recreating indexes, triggers, etc)
4. You don't have the old statistics, so you have to create them again!
5. You'll get invalid procedures, packages, etc. you have to compile them.
It is not the best way to reset HWM of a table, but you can use it in case you want a quick backup of the table, or when there are no indexes, no depending objects(triggers, packages, etc) so you have less steps

Other links
Move tables
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