There are several options you can consider.
- alter table .. move
- alter table ... shrink
- Export / Import
- ONLINE redefinition using dbms_redefinition
- datafile resize
Depending on the business and downtime you have , you can consider on any one of them.
A simple search on google with "reclaim unused space" should give you all the procedures of the methods mentioned above.
1. Most efficient: export table (with exp or expdp), truncate table or recreate table, reimport table (with imp or impdp)
Export /import is an ideal method..But it all depends on your environment and other things
How to see the CREATE TABLE statement that was used to create the table in the past:
set long 100000
select dbms_metadata.get_ddl('TABLE','add_tablename_here') from dual;
2. ALTER TABLE ... MOVE
move table to a different tablespace with different storage clauses ... to force the recreation of the extents
Note.147356.1 How to Move Tables from One Tablespace to Another
Note.130814.1 How to move LOB Data to Another Tablespace
Note.761388.1 How To Move Or Rebuild A Lob Partition
Alter table would be an ideal option but you need space for the table which will be doubled during the move and then you have truncate the old table
Rebuild Index on that
3. For smaller tables, create a new table (create table as select = CTAS):
create table newtable as select * from oldtable;
Then drop oldtable and rename newtable like oldtable (Note.115948.1 How to Rename a Table)
or recreate oldtable with CTAS:
create table oldtable as select * from newtable;
CTAS has it own draw backs which misses out so may values
4. You can also use the DBMS_REDEFINITION package which will allow you to-reorganize the tables onlline.
Note.149564.1 DBMS_REDEFINITION ONLINE REORGANIZATION OF TABLES
See also the 11.2.x online documentation: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables007.htm#ADMIN11668
(in RDBMS 10g and later we offer a Segment Advisor tool in Enterprise Manager, and a new command ALTER TABLE ... SHRINK for online table reorganizsation, see e.g. Note.242090.1 SEGMENT SHRINK and details )
Hope this helps.
Your table is already compressed. Have you gathered any information to know how much space you can reclaim from this compressed table?
Also note, only direct path operations will have their data compressed when inserting into this table. Conventional DMLs will not have their data compressed.
Even if you perform a alter table .. nocompress it will not uncompress your existing data, it will only uncompress your new data.
Having said this, you can use online DBMS_REDEFINITION package to reclaim space which you do not have downtime.
best way to reclaim unused space in datafile
If you are looking to reclaim unused space in datafile, then you can use below views
you can get space utilised in a tablespace by querying sum(bytes/1024/1024/1024) from dba_data_files.
then you can get unused space of tablespace by querying sum(bytes/1024/1024/1024) from dba_free_space.
you can closely conclude as to how much datafile size can be reduced & you can
alter database datafile 'data/file/path.dbf' resize to 10g; --- example 10g
Hope it helps.
please correct me if I am wrong.
Can i still reclaimed space in compressed table? Aside from resize.
See what Tom says on ur above question - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30403834755164