Database Administration (MOSC)

MOSC Banner

Reclaim Unused Space - alter table move, shrink, or expdp/impdp

edited Nov 9, 2012 6:49AM in Database Administration (MOSC) 10 commentsAnswered
We have a data warehouse database that has 5 TB of unused spaces. We would like to reclaim all the unused spaces. These are the segment types. Most of them are partition and subpartition tables. And most of them are very large FACT tables.

CACHE
CLUSTER
INDEX
INDEX PARTITION
INDEX SUBPARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
TABLE
TABLE PARTITION
TABLE SUBPARTITION

If I want to reclaim all the unused spaces, which one of the following is a better option:

1) alter table move to move all the objects from TABLESPACE_1 to TABLESPACE_2

drop TABLESPACE_1

rename TABLESPACE_2 to TABLESPACE_1

2) alter table shrink

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center