how to delete free datafile from tablespace
sherkhanDec 19 2010 — edited Dec 20 2010Dear All,
we having one Oracle RAC database , having 13 tablespaces.
TABLESPACE STATUS TOTAL_MB USED_MB FREE_MB PCT_USED GRAPH (X=5%)
-------------------- ---------- ------------------- ------------------- ---------------
TEST_DATA_SPACE ONLINE 94,210.00 85,521.50 8,688.50 90.78 [XXXXXXXXXXXXXXXXXX--]
TEST_DATA_SPACE2 ONLINE 4,096.00 657.25 3,438.75 16.05 [XXX-----------------]
TEST_IDX_SPACE ONLINE 98,304.00 61,324.06 36,979.94 62.38 [XXXXXXXXXXXX--------]
TEST_IDX_SPACE2 ONLINE 8,192.00 .13 8,191.88 .00 [--------------------]
AUDIT_DATA_SPACE ONLINE 53,248.00 2,768.81 50,479.19 5.20 [X-------------------]
MSM_DATA_SPACE ONLINE 6,144.00 5,920.75 223.25 96.37 [XXXXXXXXXXXXXXXXXXX-]
SYSAUX ONLINE 4,096.00 3,833.56 262.44 93.59 [XXXXXXXXXXXXXXXXXX--]
SYSTEM ONLINE 4,096.00 1,035.25 3,060.75 25.27 [XXXXX---------------]
TEMP TEMP 9,500.00 9,486.00 14.00 99.85 [XXXXXXXXXXXXXXXXXXX-]
UNDOTBS1 ONLINE 8,192.00 6,721.81 1,470.19 82.05 [XXXXXXXXXXXXXXXX----]
UNDOTBS2 ONLINE 7,500.00 6,759.75 740.25 90.13 [XXXXXXXXXXXXXXXXXX--]
UNDOTBS3 ONLINE 1,024.00 59.75 964.25 5.83 [X-------------------]
USERS ONLINE 512.00 .56 511.44 .11 [--------------------]
------------------- ------------------- --------------- ------------------------------------------------------
sum 299,114.00 184,089.19 115,024.81
13 rows selected.
AUDIT_DATA_SPACE tablespace is 53 GB out of which 50 Gb table was truncated now we have 50 GB free space which want to delete it(means we want to minimize the size of tablespace by deleting the free space) but database not allowing us to delete it. it saying the space is already used and can't be minimized.
NOte: first the database all tablespaces was used 90%. but now we truncated the bigest tables ,now we have free space on tablespace but we need this free space to allocate to another tablespace. how can we minimize the size of tablespace.
Thanks & regards,
sher khan