Home » RDBMS Server » Server Administration » Shrink Tablespace
Shrink Tablespace [message #283042] Mon, 26 November 2007 00:23 Go to next message
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 Go to previous message
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.

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

Report message to a moderator

Previous Topic: Problem relating NLS parameter setting[Topic:Merged]
Next Topic: script
Goto Forum:

Current Time: Sun Aug 20 22:00:39 CDT 2017

Total time taken to generate the page: 0.16333 seconds