Reclaiming Space in Tablespace

Recently a huge table (30gb) in size was dropped from the table space now the size of table space including all the datafile is 50 gb and used is only 15 GB i want to reclaim the space but when i try to reduce size of any datafile it gives me the following message
ORA-03297: file contains used data beyond requested RESIZE value

if this is the case how can i reduce the size of tablespace/datafile.
LVL 28
Who is Participating?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
I would drop the tablespace, delete the files at the OS level, then recreate the tablespace before doing the import.

Another option would be to coalesce the tablespace, then resize the data files after the import is complete.
  You can use the following query to find out the smallest size that you can resize a datafile to:
column value new_val blksize
select value from v$parameter where name = 'db_block_size'

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

If the objects are fragmented accross the data files, then you may need to reorganize the tables/indexes to claim back the full possible space.
Ivo StoykovCommented:
Hello  imran_fast

here you have a perfect explanation from Tom Kyte

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

johnsoneSenior Oracle DBACommented:
I would create a new tablespace that is sized for the remaining objects, then move the objects in the old tablesapce to the new tablespace using alter table move and alter index rebuild.

You can use sql to build the commands for you:

select 'atler table ' || table_name || ' move tablespace <new_ts>;'
from dba_tables
where tablespace_name = '<old_ts>';
select 'alter index ' || index_name || ' rebuild tablespace <new_ts>;'
from dba_indexes
where tablespace_name = '<old_ts>';
imran_fastAuthor Commented:
let me check if it works

Thanks for links

sorry there is no downtime.
imran_fastAuthor Commented:
hi gvsbnarayana,

How can if defrag a table inside database.
If you detect that a single tablespace has fragmented, you can quickly coalesce it with the following procedures:

1. Alter session by retrieving the tablespace number from sys.ts$:

select * from sys.ts$;

2. In SQL*DBA, issue the following command:

alter session set events ‘immediate trace name coalesce level &tsnum);
(where tsnum is the tablespace number from step 1)

3. Manual coalesce; from SQL*Plus enter:

alter tablespace <xxxx> coalesce;
imran_fastAuthor Commented:
hi ramumorla,

I used the query in your first comment and i am able to free only 2 GB out of 38 GB. and the data is scattered all over the tablespace  Is there any way i can reduce the size without exporting and importing.
johnsoneSenior Oracle DBACommented:
No downtime would be required to move the objects given the commands that I gave you.  You can use the online keyword on the moves and rebuilds which will help.  Additionally, the tables would never be unavailable, at worst, they would be locked for the amount of time it takes to do the moves.

I believe that is your only option.  Coalescing the tablespace will only put together contiguous free blocks into 1 free extent, it will not free any space or move blocks to allow you to resize the files.
imran_fastAuthor Commented:
What about lond datatypes
johnsoneSenior Oracle DBACommented:
If the table contains a LONG, then your only option will be exp/imp.
imran_fastAuthor Commented:
hi johnsone,

Thats the only option export import. It requies downtime.
imran_fastAuthor Commented:
One more thing While importing this export file should i delete the old tablespace create it again and then import it or i can import it on the existing file.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.