cancel
Showing results for 
Search instead for 
Did you mean: 

relasing free space in tablespace

sukhdev_kaloor
Participant
0 Kudos

Hello All,

We have more than than 40% free space in one of the table space (Ora11.2.0.3.0).

How do we release the free space ?

1) Is a re-organization mandatory ?

2) Can we shrink the data files without a re-organization ?. Does a Coalesce free extents help?.

We don't have free space available on the disk.

thx,

Sukhdev

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member188883
Active Contributor
0 Kudos

Hi Sukhdev,

There is a concept of deallocate space in the segments.

Refer link for difference between coalesce and deallocate.

http://www.dba-oracle.com/t_coalesce_deallocate_unused_space.htm

Few more details available in the help file

http://help.sap.com/saphelp_nw04/helpdata/en/29/581630f72011d2952900a0c930df15/content.htm

Hope this helps.

Regards,

Deepak Kori

Former Member
0 Kudos

Hi Sukhdev,

You can shrink the datafile, if it has been newly created. You can perform this operation by using brtools. Please find the further information in the document, below;

http://help.sap.com/saphelp_nw04/helpdata/en/f1/5e863f06593a17e10000000a114084/content.htm

On the other hand, if it has been used by the database it will not work to shrink the datafile. At this stage, reorganization is required.

Best regards,

Orkun Gedik

sukhdev_kaloor
Participant
0 Kudos

Hello Orkun Gedik,

We did try a resize of datafile using brtools. But this was not success saying the file is already occupied at that level.

We dont have the flexibility to do a full table space re-org because of disk space limitations.

So at a datafile level do we have any other options ? Thatz what we are exploring.

Thx,

Sukkhdev



Former Member
0 Kudos

Hi,

I suggest you read the Note 821687 - FAQ: Space utilization and fragmentation in Oracle

Best regards,

Orkun Gedik

shaun_wimpory2
Participant
0 Kudos

Hi Sukhdev,

I'd recommend having some temporary disk allocated so you can create a new tablespace and reorg the contents of the old tablespace into the new smaller tablespace.  You can then delete the physical datafiles of the old tablespace, and if you really want to, recreate the old tablespace and reorg back to the original (just so your tablespace names are back to standard).

Cheers

Shaun