Solved

Reclaiming Space in Tablespace

Posted on 2007-04-10
13
1,215 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 34

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Cross Outer Join 4 52
update set column values in oracle 3 43
PL/SQL Two statements 6 50
SQL Retrieve Values 4 43
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now