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

From: Warren Puziewicz <warren.puziewicz_at_realtimeworlds.com>
Date: Fri, 28 May 2010 16:14:50 +0100
Message-ID: <B0078105498B4A4E99D4908A84AB096BCF93603DD2_at_RTW-UK-MX01.realtimeworlds.com>

Is the ETL code doing INSERT /*+ APPEND */ ? That will always insert above the high water mark, never using existing empty space.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vamshi Damidi Sent: 28 May 2010 16:08
To: oracle-l_at_freelists.org
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 : 10.2.0.3 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.

Thanks,
Vamshi .D

---

This email has been scanned by the MessageLabs Email Security System

---

DISCLAIMER This message and any attachments contain privileged and confidential information intended for the use of the addressee named above. If you are not the intended recipient of this message, you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. Please note that we cannot guarantee that this message or any attachment is virus free or that it has not been intercepted and amended. The views of the author may not necessarily reflect those of Realtime Worlds Ltd.  

Realtime Worlds Ltd is registered in Scotland, number 225628. Registered Office: 152 West Marketgait, Dundee, DD1 1NJ.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 28 2010 - 10:14:50 CDT