| reclaiming space from tablespace [message #302521] |
Tue, 26 February 2008 00:48  |
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 #302546 is a reply to message #302527] |
Tue, 26 February 2008 02:08  |
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 #302650 is a reply to message #302546] |
Tue, 26 February 2008 08:12 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
