9 Replies Latest reply: Feb 15, 2013 11:56 AM by Aman.... RSS

    Reclaim Space From Table via Datapump Export Import

    990992
      Hi All,

      I want to rebuild SCOTT.EMP table and reclaim space as large number of data has been purged from this table.

      Total size of this table is 77 GB and index associated with this tables is of 114 GB approx. Please refer below logs for the same.

      1)     Tables size.

      OWNER TABLESPACE_N SEGMENT_NAME GB
      ------------
      ----------

      SCOTT USERS EMP 77 GB

      2)     Index associated with the above tables and size of the index.
      OWNER INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS

      ------------------------------
      --------------------
      SCOTT XUHSDP0 EMP IDX01 VALID
      SEGMENT_NAME SEGMENT_TYPE GB

      ------------------
      XUHSDP0 INDEX 114
      Total size of USERS tablespace is 456 GB and 70 GB of space is free in this tablespace. While total size of IDX01 tablespace is 336 GB and 58 GB is free.
      There are 3 check constraints (NOT NULL) associated with this table.

      Database version is 11.1.0.7


      Below is the approch which i will be following , Please advise .

      Export data of table
      1. disable all constraints.
      2. create a script to drop and re-create indexes
      3. drop the indexes
      4. disable constraints
      Truncate Table
      5. impdp CONTENT=DATA_ONLY
      6. run the script to create indexes
      7. enable constraints
      8. enable triggers

      Edited by: 987989 on Feb 13, 2013 10:36 PM