RE: Tablespace growing like anything and free blocks not reutilized.

  • From: "Powell, Mark" <mark.powell2@xxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 28 May 2010 15:16:38 +0000

By any chance are the inserts being done via Direct Loads?

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Vamshi Damidi
Sent: Friday, May 28, 2010 11:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Tablespace growing like anything and free blocks not reutilized.

Hi All,

I have below problem let me first give you some information.

Environment : DatawareHouse.
Version : Oracle Enterprise all the patches applied.
Blocksize : 16k
Tablespace : extent management local uniform 100m segment management auto.
Disk : Raid 5 of 1tb space with 8 disks.
OS : RHEL 5 .Runnning LVM.

I have above environment and some tables have lob and long columns and millions 
of rows and some tables are 0 rows tables.
We are in testing phase and this is dev env and ppl keep deleting millions of 
rows and reload them.

When they reload i am expecting that table should use extents also allocated to 
it but

The extents already allocated are not being used and it looking for new space 
and tablespace is growing  like anything.

when i check the dba free space i get every thing less than 100mb avaliable.

when i perform below actions.

alter table <table name > enable row movement;
alter table < table name > move;
alter table < table name > shrink space;
rebuild all the indexes.

i get around 250gb of space as free in dba_free_space.

so i performed a test case to see whats happenning

One of my table is having clobs which when imported takes 20g of size .

After developers deleted that table i still see the same space occupied in 
dba_extents which is obvious.
and i check the rows which is 0 so i truncated that table and now all the space 
is released and now its taking only 100m in dba_extents.

But when i developer try to insert into same table its trying to take new 
extents when truncate is not performed and shrink is not done instead of 
existing ones already allocated to that.

Masters I need your ideas.

Vamshi .D

Other related posts: