Solved

Reclaiming Space in Tablespace

Posted on 2007-04-10
13
1,222 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:imran_fast
13 Comments
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 18882654
Hi,
  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.
HTH
Regards
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 18882733
Hello  imran_fast

here you have a perfect explanation from Tom Kyte
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1040031916667

HTH
!I!
0
 
LVL 35

Expert Comment

by:johnsone
ID: 18882939
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>';
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Author Comment

by:imran_fast
ID: 18888100
@gvsbnarayana
let me check if it works


@ivostoykov
Thanks for links

@johnsone
sorry there is no downtime.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18888165
hi gvsbnarayana,

How can if defrag a table inside database.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18888302
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;
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18888901
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.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 18889385
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.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18909887
What about lond datatypes
0
 
LVL 35

Expert Comment

by:johnsone
ID: 18917350
If the table contains a LONG, then your only option will be exp/imp.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18923392
hi johnsone,

Thats the only option export import. It requies downtime.
0
 
LVL 28

Author Comment

by:imran_fast
ID: 18923428
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.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 18923875
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.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question