Question
Are you using Oracle Streams?
Yes, actively in production
Yes, for test or less important systems
No, but we use GoldenGate
No
No idea or not applicable
Answer and see the results
DBA Top 10
1 M. Cadot 98900
2 B. Vroman 43800
3 A. Kavsek 39600
4 T. Boles 37300
5 P. Wisse 27500
6 Y. Naguib 23500
7 A. Khan 16550
8 J. PĂ©ran 16300
9 A. Hudspith 15750
10 F. Pachot 15150
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 54 now available

Feb 04, 2014
(recommended update: build 1403)
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered46923
Total active users29467
Act. users last 24h44
Act. users last hour1
Hits last week2071
Hits last month7740
Go up

LOBsegment exploded want to shrink it
Next thread: Priviledges missing in Connect role in Oracle 11G
Prev thread: Connection to a specific instance using scan

Message Score Author Date
Recently I did a full export from windows with com...... Simon Acruri Feb 08, 2011
Simon, Lob segments have lots of storage attrib...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Guy Lambregts Feb 08, 2011
Thank you for your response. I see what you are...... Simon Acruri Feb 09, 2011
Hi Simon Since your lobsegment is built in a 16...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Guy Lambregts Feb 09, 2011
First of all Thanx a million for you very informat...... Simon Acruri Feb 10, 2011
Hi Simon Thanks for appreciation. So you hav...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Guy Lambregts Feb 10, 2011
Oh thank you so much. It worked. I was able to ...... Simon Acruri Feb 11, 2011
Hi Simon, you should set your db_8K_cache_size ...... Dieter Henig Feb 15, 2011
Thanx you Dieter... Simon Acruri Feb 16, 2011
Hi, Whether there is a real need to have a big ...... Guy Lambregts Feb 16, 2011

Follow up by mail Click here


Subject: LOBsegment exploded want to shrink it
Author: Simon Acruri, Bahamas
Date: Feb 08, 2011, 1268 days ago
Os info: windows 2003 to hpux 11.31
Oracle info: 10.2.0.4
Message: Recently I did a full export from windows with compress=N
then imported that in hpunix database

source DB tablespace for lobsegment in windows is 50GB .. DB block size is 8k

Target tablespace is precreated before the import in the new blank database

Target DB tablespace for logsegment in Unix becomes 100 GB .. Db block size is 16K

Now how to fix this size issue. How to shrink the lobsegment tablespace .
This is actually coming from a Table which has lob column which goes to the logsegment tablespace.

Thanx
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Guy Lambregts, Belgium
Date: Feb 08, 2011, 1268 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Simon,

Lob segments have lots of storage attributes. Despite that lots of DBA' s never throw an eye on it. I guess the moment is there for you to do it now.

One of the attributes is disable storage in row
this means that no matter what the size of the lob data is about, the lob data will be stored in dedicated blocks in a dedicated lob segment.

Another storage attribute is the chunk size. The minimum chunk size is the size of a block no matter what the size of the lob data is about. In your case the minimum size will be 16K.

If I read your posting it is as if in your source db you had x records with lob data of which the actual length (dbms_lob.getlength) was below a 8K block size, but per record there was the minimum of 8K / 1 block allocated.

Now you have x records with lob data of which the actual length (dbms_lob.getlength) is below a 16K block size, but per record there is the minimum of 16K / 1 block allocated.

This explains why the space lost has doubled

I am afraid I am right that your choice to move the lobs towards a bigger block size tablespace was wrong ( it is human to be wrong now an then )

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_performance.htm

I am afraid you are in for another reorg

Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Simon Acruri, Bahamas
Date: Feb 09, 2011, 1267 days ago
Message: Thank you for your response.

I see what you are saying.
I am ready to do anything on earth to shrink this seperate LOG segment.

You mentioned "I am afraid you are in for another reorg "

Can you please suggest some ways to do this.

I tried using the new 10g shrink compact command nothing happened.
ALTER TABLE tname SHRINK SPACE CASCADE.

Should I try to recreate the table with chunk size 8k instead of the db block size of 16 K ?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Guy Lambregts, Belgium
Date: Feb 09, 2011, 1267 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hi Simon

Since your lobsegment is built in a 16K tablespace, the minimum space allocation per lob record is 16K since 16K is the minimum chunk size.

If you can afford downtime I would suggest you

1) capture the table DDL using dbms_metadata, indexes, constraints, triggers
2) capture privileges, which users, user roles have grants on the table concerned
3) export the table using exp or expdp
4) create a tablespace of 8K and assure you have a db_8K_cache_size
5) adjust the table DDL in order to built your lob segment in the 8K tablespace, and precreate the table
6) import using imp or impdp

There are commands to move log segments only towards another tablespace but given the size, even though much of lost space, I would not use those commands to move such a big segments in production databases. The move is supposed to be online but you should monitor the undo tablespace which could grow that much you better don' t do it.



SQL> create table student1.t_LOB ( col1 number, col2 clob );

Table created.

SQL> select table_name,column_name,tablespace_name from dba_lobs where table_name='T_LOB' and owner='STUDENT1';

T_LOB
COL2
USERS


SQL> alter table student1.t_lob move lob(COL2) store as T_LOB_LOBDATA (tablespace TS1_LOB chunk 8K);

Table altered.


SQL> select table_name,column_name,tablespace_name from dba_lobs where table_name='T_LOB' and owner='STUDENT1';

T_LOB
COL2
TS1_LOB




In the above example TS1_LOB is a 8K block size tablespace.

You may want to list what the avg lob length is about



SQL> select avg(dbms_lob.getlength(col2)) from student1.t_lob;




if the average is below 4K, then you could consider
1) to configure a small db_4K_cache_size
2) to create a 4K block size tablespace at you choice
3) to move your lob segment towards that tablespace with the below syntax



SQL> alter table student1.t_lob move lob(COL2) store as T_LOB_LOBDATA (tablespace TS1_LOB chunk 4K);



Not that difficult, if you would prefer the move syntax I would take an export of the table concerned before. I lost once lob data with the move syntax. This was buggy 9.2.0.1 behaviour.

Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Simon Acruri, Bahamas
Date: Feb 10, 2011, 1266 days ago
Message: First of all Thanx a million for you very informative and educative response.
Never worked with Lobs and this is a good learning experience.

Question about

db_8K_cache_size .What should it be set to ?


I think no matter what size I set it to it takes the default DB block size which is 16K in my case.

I hope that should not be an issue.

Only after setting the db_8K_cache_size I was able to create a tablespace of blocksize 8K.

But I didnt understand why it defaulted to 16K

SQL> alter system set db_8k_cache_size=8K SCOPE=BOTH ;

System altered.

SQL> SHOW PARAMETER db_8k_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

db_8k_cache_size big integer 16777216
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Guy Lambregts, Belgium
Date: Feb 10, 2011, 1266 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Simon

Thanks for appreciation.

So you have configured a db_cache_size which is the amount in bytes for your 16K cache ( since db_block_size = 16K you sized the default (16K) buffer cache using db_ccahe_size in stead of db_16K_cache_size )

As you pointed out yourself you first need to configure a cache for the non default block size before you can create a tablespace with that block size

In your case you first had to specify a db_8K_cache_size prior to you 8K lob tablespace creation.

According to the documentation as well according to the test I did the minumuml amount of memory per pool = 4M * cpu_count

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams037.htm


I did a small test to confirm


SQL> alter system set db_16K_cache_size=4K scope=memory;

System altered.

SQL> show parameter db_16K_cache_size

db_16k_cache_size
big integer 8M

SQL> show parameter cpu_count

cpu_count
integer 2



What I learn from your output is that you must have a cpu_count = 2

Note 16M of memory should not be too much if you ask me.

Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Simon Acruri, Bahamas
Date: Feb 11, 2011, 1265 days ago
Message: Oh thank you so much.

It worked. I was able to reduce the LOB segment into half.

It saved me almost 100GB of space. Just imagine how my backups would have been with this extra 100 GB.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Dieter Henig, Switzerland
Date: Feb 15, 2011, 1261 days ago
Message: Hi Simon,

you should set your db_8K_cache_size much higher than 16M. It is a part of the buffer cache.
If this is too small, you will massively increase your disk access.

b.r.
Dieter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Simon Acruri, Bahamas
Date: Feb 16, 2011, 1260 days ago
Message: Thanx you Dieter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: LOBsegment exploded want to shrink it
Author: Guy Lambregts, Belgium
Date: Feb 16, 2011, 1260 days ago
Message: Hi,

Whether there is a real need to have a big db_8K_cache_size depends on other lob attributes.
Lob segments can be created with the cache or with the nocache attribute.

If they are created with the nocache attribute then retrieving lob data bypass the buffer cache, in stead there are direct path read and writes.

You may want to check this out, see all_lobs

Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here