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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

  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

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>';
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
johnsoneSenior 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.