Re: Tablespace growing like anything and free blocks not reutilized.
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: dbaprimatics@xxxxxxxxx
- Date: Fri, 28 May 2010 16:24:21 +0100
A pound to a penny its the lob management that is the trouble, especially if
they are being transactionally manipulated. It would be interesting to see a
sample bit of code, together with a business description of its aim. I know
that different folks mean different things by datawarehouse but I'm
intrigued as to why the lobs are there anyway.
Niall Litchfield
On May 28, 2010 4:10 PM, "Vamshi Damidi" <dbaprimatics@xxxxxxxxx> wrote:
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
Other related posts:
