OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Shrink Tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Shrink Tablespace [message #283042]

Mon, 26 November 2007 00:23

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Hi Experts,

I want to know can i able to shrink/resize/reduce tablespace in oracle.
I hope i will get helpful suggetions.

I have tablespace TEST_IDX which contains indexes.


TABLESPACE_NAME  Alloc MB    Free MB    Used MB    % Used
----------------------------------------------------------------
TEST_IDX          28760       2711      26049     90.5737135

I want to know that Suppose, I will add datafile in the tablespace.
In the future i needed more space in the disk.
And i find that TEST_IDX tablespace having lot of free space on the disk.

So will it be possible to shrink/resize/redule tablespace size.

Is it the same command what we fire to increase the datafile size?????

ALTER DATABASE DATAFILE '/u04/TEST/oracle/oradata/ora01/TEST_IDX/TEST_TAB_02.dbf' RESIZE 10112m;

If yes then please tell if it has any side effects.

Thanks in advance.

[Updated on: Mon, 26 November 2007 00:24]

Report message to a moderator

Re: Shrink Tablespace [message #283048 is a reply to message #283042]

Mon, 26 November 2007 00:33

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

You can resize your datafile upto HWM(high water mark)
you resize statement is correct.
you can find HWM value from below query and if your try to resize datafile below HWM then you get error message like "ORA-03297"

1 select
2 a.file_name,
3 a.bytes file_size_in_bytes,
4 (c.block_id+(c.blocks-1)) * &_BLOCK_SIZE HWM_BYTES,
5 a.bytes - ((c.block_id+(c.blocks-1)) * &_BLOCK_SIZE) SAVING
6 from dba_data_files a,
7 (select file_id,max(block_id) maximum
8 from dba_extents
9 group by file_id) b,
10 dba_extents c
11 where a.file_id = b.file_id
12 and c.file_id = b.file_id
13 and c.block_id = b.maximum
14* and c.tablespace_name = 'EXAMPLE'

If below HWM value is near to total datafile size then you have to reorganize your tablespace to reduce datafile size.
you can use below method for it.
1. alter table ... move tablespace "new tablespace" + rebuild all indexes.

Just search on forms/net for complete example.
Regards
Mohammed

[Updated on: Mon, 26 November 2007 00:34]

Report message to a moderator

Previous Topic:	Migrate Job Library to a New Server?
Next Topic:	Unable to connect using Net service name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 18 18:15:23 CDT 2024