This discussion is archived
9 Replies Latest reply: Feb 15, 2013 9:56 AM by Aman.... RSS

Reclaim Space From Table via Datapump Export Import

990992 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points