Home » RDBMS Server » Server Administration » reclaiming space from tablespace (os: windows xp, oracle 9.2.0.1)
reclaiming space from tablespace [message #302521] Tue, 26 February 2008 00:48 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Hello sir..

I have one tablespace whose size is 1430MB out of which only 645 MBs are used.
Rest is free.
So i want the free space back to allocate it for other purpose.
I searched our site.
I rebuild all the indexes and move all the tables on this tablespace. Then i have also tried to coalesce the tablespace..
All this operations had completed successfully.

But when i try to reduce space allocated to tablespace (even when i try to make it 1400 MB), it returns the error ora-03297.

I view the mapping of tablespace from OEM. all the tables and indexes are at the one side and the following blocks are free in tablespace mapping graph.

Now is there any other way, i can use to get the free space back
(other than export and reimport option).
Please help me to point out if i am wrong anywhere.

Thanking you

Dipali
Re: reclaiming space from tablespace [message #302527 is a reply to message #302521] Tue, 26 February 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just search for "shrink" there are many topics on the subject.

Regards
Michel
Re: reclaiming space from tablespace [message #302546 is a reply to message #302527] Tue, 26 February 2008 02:08 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Michel,

I already had done it..
and follow the things found in it.
I have specified those in above posts..
But still, out of 1430, i am able to resize only upto 1426. while the used space in it is near 650.
So , any other solution to resize the tablespace/datafile to reduced size other than exp-imp..?
Re: reclaiming space from tablespace [message #302556 is a reply to message #302546] Tue, 26 February 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think all the ways has been listed in these topics.

Regards
Michel
Re: reclaiming space from tablespace [message #302650 is a reply to message #302546] Tue, 26 February 2008 08:12 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
vithalani_dipali wrote on Tue, 26 February 2008 03:08

But still, out of 1430, i am able to resize only upto 1426. while the used space in it is near 650.



I wouldn't trust a GUI tool to tell you that there is not an onject at the end of your datafile. I'd use this:
set pages 9999
spool map.lst
col FILE_ID print
col BLOCKS print
col BLOCK_ID print
col FILE_ID format 999 head "file"
col EXTENT_ID format 999 head "ext"

select owner,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,FILE_ID,
        BLOCK_ID,BYTES,BLOCKS
from dba_extents
where tablespace_name = ('whatever tablesapce you want')
UNION
select '-','free',TABLESPACE_NAME,0,FILE_ID,BLOCK_ID,BYTES,BLOCKS
from dba_free_space
where tablespace_name = ('whatever tablesapce you want')
order by FILE_ID,BLOCK_ID
/

Re: reclaiming space from tablespace [message #307352 is a reply to message #302546] Tue, 18 March 2008 09:54 Go to previous message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
Hey Dipali,

There is one option you could try with...

Create new tablespace and move all the objects from old to new tablespace and resize your old tablespace and again move back from new tablespace to old tablespace. Drop new tablespace...

I hope this will resolve your problem.



Thanks,
Harshad.
Previous Topic: Best Practice When Table Dropped
Next Topic: 10g and 9i Oracle on Windows 2003
Goto Forum:
  


Current Time: Mon Sep 16 21:57:30 CDT 2024