Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kasturi.

Asked: September 24, 2001 - 9:43 am UTC

Last updated: August 20, 2007 - 9:56 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hello Tom,

The below is the output of show_space.sql in my database for a table.


SQL> exec show_space('TABLE1');

Free Blocks.............................3
Total Blocks............................520
Total Bytes.............................2084864
Unused Blocks...........................25
Unused Bytes............................102400
Last Used Ext FileId....................8
Last Used Ext BlockId...................72325
Last Used Block.........................140

PL/SQL procedure successfully completed.

SQL> column segment_name new_val lob
SQL> select segment_name from user_lobs
2 where table_name = 'TABLE1';

SEGMENT_NAME
------------------------------
SYS_LOB0000034916C00004$$

SQL> exec show_space('&lob',user,'LOB');
Free Blocks.............................41149
Total Blocks............................437343
Total Bytes.............................1791356928
Unused Blocks...........................25855
Unused Bytes............................105902080
Last Used Ext FileId....................8
Last Used Ext BlockId...................363494
Last Used Block.........................17345

PL/SQL procedure successfully completed.

The Block size is 4K and the chunk size of the LOB column is also 4K.
I have around 8000 rows in the TABLE. My question is if the last used block is 17345 and unused blocks = 25855 and 41149 free blocks where are the remaing blocks??????? Why does this situation arise inspite of my chunk size being equal to 1 block size?

Q2) both my lob segment and table segments are on the same tablespace. ALTER TABLE ... MOVE with new storage specifications has table level options. How do i handle this LOB SEGMENTS???? If i were to handle LOB segments sizing only.


Thanks.

and Tom said...

Your lob segment is most likely in multiple extents. You might have extents in file 8, file 9, file 2, file N. Even if all extents are in file 8 -- you might have an extent from block 100000 to 100050, 500 to 600, 15000 to 18000. This is just telling you how far into the last extent you are.

You might want to ignore the "Last used". It is just helpful in identifying where the high water mark really physically exists (its in file 8 at that block -- block 363494+17345)

This is normal and does not reflect on the space utilization at all-- last used does not reflect on the space used. There would only be a correlation between the two if the object was in a single extent.



q2) this shows how:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y clob );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /

OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
LOB SYS_LOB0000018544C00002$$ SYSTEM

TABLE T SYSTEM



ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t move tablespace p1_s1 lob(y) store as ( tablespace p2_s2 );

Table altered.


ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name,
2 decode( status, 'INVALID', '*', '' ) status,
3 decode( object_type,
4 'TABLE', (select tablespace_name from user_tables where table_name = object_name),
5 'INDEX', (select tablespace_name from user_indexes where index_name = object_name),
6 'LOB', (select tablespace_name from user_segments where segment_name = object_name),
7 null ) tablespace_name
8 from user_objects a
9 order by object_type, object_name
10 /

OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
LOB SYS_LOB0000018544C00002$$ P2_S2

TABLE T P1_S1



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Chunk Size Estimations

Kasturi, September 24, 2001 - 11:30 am UTC

Hello Tom,

Thanks for the reply. I was trying to find out why this particular table is taking so much of space in the database with just 8000 rows in it.

So I used

alter table move ...
lob(y) store as (CHUNK 1024)... because the lob segment is storing email attachments in it. But How do i really estimate what the perfect size of the chunk is? But i cannot do this in a production environment.

Finally my question is How do i size a table with lob columns in it.

Tom Kyte
September 24, 2001 - 12:54 pm UTC

the chunk size is always an even multiple of the blocksize.. If you have a 4k block -- the minimum chunk is 4k. chunks are allocated in blocks -- the data from one lob will be on a block all by itself -- no other lob will be on the same block, ever.

If the lobs are so small, smaller then 4000 bytes, you can have them stored inline .

In order to estimate the perfect chunk size you would need to know exactly how big your data was. In order to use the least space, use a chunk size that is your blocksize.

To size a table -- see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:406873025890 <code>
...

Varchar2 to CLOB migration

Andre Whittick Nasser, October 31, 2001 - 2:49 pm UTC

Tom,

We are changing some character columns to CLOB columns. We are aware of the TO_LOB() function.

Regarding space management, we know about the ENABLE/DISABLE STORAGE IN ROW and STORAGE clauses, as well as the behavior regarding 4000-byte issues.

The problem is that varchar2 is self-managed in terms of compression and expansion.

However, with LOBs, it seems I have to approaches as to shrinking:

a) When the LOB is in-line, it will only shrink it by using the DBMS_LOB.TRIM procedure.

b) When it's out-of-line, I must regard the LOB as any other segment, having to explicitly deallocate extents.

Questions:

1) Am I correct as to a) and specially b) ?

2) I did not find a way to do this explicit deallocation. How is it done ?

3) Is there a chance that CLOB data will be "scattered" along it, so that there will be "holes", as when I delete rows from a table ?

4) In the latter case, should I reorganize the data ? How to do it ?

Thanks for your holy patience !


Tom Kyte
October 31, 2001 - 3:25 pm UTC

You don't need to_lob() to go from varchar2 to CLOB.  I simple update or insert will do it.  In 9i, an even simplier "alter table modify" will do it.

Think of a LOB just like an OS file (the underlying mechanisms are really a file system!).  When you write to a file and make it grow to 100m, it'll be 100m -- you can seek all over it and change bits and bytes here and there but its a 100m file.  If you want to "shrink" it you can, if you want to grow it you can.  But changing the DATA within has no effect on the size.  A lob is exactly the same in this regards.

You have to use trim or set it equal to a different lob or string all together:

DOC>create table t ( user_id int, username clob );
DOC>
DOC>insert into t select user_id, username from all_users;
DOC>*/
ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select user_id, username, dbms_lob.getlength(username) from t where user_id = uid;

   USER_ID USERNAME                       DBMS_LOB.GETLENGTH(USERNAME)
---------- ------------------------------ ----------------------------
     46612 OPS$TKYTE                                                 9

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> update t set username = rpad('*',4000,'*') where user_id = uid;

1 row updated.

ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;

   USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
     46612                         4000

ops$tkyte@ORA8I.WORLD> update t set username = user  where user_id = uid;

1 row updated.

ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;

   USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
     46612                            9

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> declare
  2          l_data varchar2(30000);
  3  begin
  4          l_data := rpad( '*', 30000, '*' );
  5          update t set username = l_data where user_id = uid;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;

   USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
     46612                        30000

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> declare
  2          l_data varchar2(30000);
  3  begin
  4          l_data := rpad( '*', 3000, '*' );
  5          update t set username = l_data where user_id = uid;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> select user_id, dbms_lob.getlength(username) from t where user_id = uid;

   USER_ID DBMS_LOB.GETLENGTH(USERNAME)
---------- ----------------------------
     46612                         3000


for example.  In plsql you can manipulate upto 32k of the lob like a string in 816/817.  it was 4000 bytes before that.

dbms_lob.trim is the way to explicitly "deallocate" the lob data (but you seem to have found that already ?)

There is no chance of lob data being scattered.  It is allocated in chunks, chunks are added at the "end" and as you trim back -- those chunks are freed.  You never "erase the middle" of a lob, you trim the end of it off.

 

What about out-of-line LOBs ?

Andre Whittick Nasser, October 31, 2001 - 8:26 pm UTC

Thanks for your enlightment.

But, what about, say, a CLOB segment in a LOB_DATA tablespace ?

DBMS_LOB.TRIM() only works on in-line LOBS. Let us suppose it has INITIAL 1M and NEXT 1M. It can grow up to 4GB. If I look for it in DBA_SEGMENTS, there it is, so it is a segment, all right.

For example, if I continually update this CLOB column with bigger amounts of data, the segment will grow.

Imagine there are "tons" of LOBS all around my database, always growing. Varchar2s don't "overflow". CLOBS stop short only at 4 gigs and are more prone to crowd my DB.

What is the best way to manage this expantion ? How can I deallocate the extra extents ?

Tom Kyte
October 31, 2001 - 9:09 pm UTC

where did you get the idea that dbms_lob.trim works on in-line lobs only?

ops$tkyte@ORA717DEV.US.ORACLE.COM> CREATE TABLE t       (x int, y clob)
  2  LOB ("Y") STORE AS (TABLESPACE "SYSTEM" DISABLE STORAGE IN ROW )
  3  /

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> declare
  2          l_data varchar2(32000) default rpad( '*',32000,'*' );
  3  begin
  4          insert into t values ( 1, l_data );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select x, dbms_lob.getlength(y) from t;

         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         1                 32000

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
  2          for x in ( select * from t )
  3          loop
  4                  dbms_lob.trim( x.y, 2 );
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select x, dbms_lob.getlength(y) from t;

         X DBMS_LOB.GETLENGTH(Y)
---------- ---------------------
         1                     2


The segment will not grow (well, it'll grow big enough to hold the lob and the pctversion for the lob but thats it).  It will not grow wildly.  You can setup very simple tests to see this.

CLOBS are ONLY as big as their length -- you divide your chunksize and take the ceil of that to figure out how much space they are using.  Say its 65k with a chunksize of 16k -- you will have 5 chunks of 16k allocated to hold that clob.  If you make the clob grow -- it'll use that much, if you trim it -- it'll GIVE back the space onto the free list for this segment.

Clobs MAX OUT at 4 gig, they don't stop short.

I don't no where you are getting your information from but its wrong.  Its managed just like a table -- if you insert rows and we grow the table, then you delete them -- the space goes onto the freelist and will be used for the next insert.  Clobs segments aren't much different from table or index segments in this regards. 

Varchar2 to CLOB migration (contd)

Andre Whittick Nasser, November 01, 2001 - 7:52 am UTC

Tom,

Sorry, I think I mixed up the concepts "internal" and "in-line". Below is an extract from the Reference Guide:

TRIM Procedure
This procedure trims the value of the ***internal*** LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.

By internal, I understand LOBs stored in database tablespaces, as opposed to LOBs kept in OS filesystems -- these would be "external".

So, ok, I can "trim" the LOB explicitly.

But, sorry about insisting... How to avoid LOBs staying too big ?

I'll try to give a clearer example of what I am thinking of.

Ok, you showed me that the "length" of the CLOB varies according to the length of the string stored therein.

"abcd" --> length=4
"12345678901234567890" --> length=20
"x" --> length=1

But this metric is more of a "logical" nature.

If I insert, say, 1 meg, the CLOB will grow -- creating extents -- and the only thing I can do to release space is by "trimming" it.

What I was looking for is some form of behavior like what happens to rollback segments, which (should) srihnk when allocated space is not necessary any longer -- which, in "logical" terms is what VARCHAR2 is supposed to do.

We here cannot afford (or would prefer not) to keep track of a LOB that is big because some time in the past it contained a 2-gig string but now holds only ten bytes. In this case, it would maintain the old size (2 gigs) and the only thing I could possibly do is a TRIM. Right ?

In sum, the expectation of us living-dead geeks in this cold dark office facing sunny Copacabana beach is that the CLOB's behavior would approach as much as possible that of a humble VARCHAR2.

Thanks again !

Tom Kyte
November 01, 2001 - 8:44 am UTC

You can release the space back to the freelist for the segment not only by trimming it but by:

o deleting the row
o setting the lob locator to empty_clob or null
o pointing the lob locator to new data.

I'm not getting whats so hard to see here. Its just like rows in a table. you insert them, they take space. You delete them and the block will go onto the freelist for the table segement.

You insert a clob, it takes space from the lob segment. You change their size in any number of ways and their free space will go onto the freelist for that segment.

If you HAD a 2gig lob and it is now a 2 byte lob, you will have 2gig free in that log segment for subsequent lobs that come along (ANY lob in that table will reuse that space).

Look at it this way: If you HAD a 2gig table and it is now a 2 byte table, you will have 2 gig free in that table segment for subsequent inserts and updates that come along...

You don't NEED trim. If you

update t set lob_col = 'xx' where id = 5

(using BIND variables of course in place of xx and 5) and lob_col used to be 2gig, and it is now 2 bytes -- thats it, it shrunk, space released, done deal, over and out.

(so if you treat it like a varchar2 as I demonstrated over and over, you got what you want)

OTOH if you:

select lob_col into l_clob from t where id = 5 for update;

dbms_lob.write( l_clob, 2, 1, 'xx' );

AND you want it to be 2 bytes (think about it, just because you wrote 2 bytes doesn't mean the clob should be 2 bytes) you will now need to trim it. However, the comparision with the varchar2 is long gone, you've already used dbms_lob.write. Think how annoying it would be if dbms_lob.write trimmed the data -- it would be *wrong*.

The cool thing is -- if you disagree, just write your OWN dbms_lob_write subroutine that does exactly what you want. No muss, no fuss.



Varchar2 to CLOB migration (contd)

Andre Whittick Nasser, November 01, 2001 - 8:25 am UTC

Tom, me again....

In the process of migrating varchar2's to CLOBS, I would not like to lose the contents of the old varchar2 fields.

I am using a test database, and this will run on a production environment, although there is little data in it -- not a "critical" issue.

What do you think of this ?

ALTER TABLE T ADD ( AUX CLOB );

UPDATE T SET AUX=TEXT; -- TEXT is the old varchar2 column

ALTER TABLE T DROP COLUMN TEXT;

ALTER TABLE T ADD ( TEXT CLOB );

UPDATE T SET TEXT=AUX;

ALTER TABLE T DROP COLUMN AUX;

I think the only major "collateral" effect of this is the new position of the column.

( Tired of saying "thanks"... Obrigado ! -- that's in Portuguese ! )


Tom Kyte
November 01, 2001 - 8:49 am UTC


yes, that will work however since MOST programs (pre-oracle9i) will have to be changed to some degree to accomidate the clob -- you might consider renaming the column (skipping that last add and update).

Varchar2 to CLOB migration (contd)

Andre Whittick Nasser, November 01, 2001 - 9:55 am UTC

Tom,

I wrote a script for the review above:

-- Start of Script ----------------------------

set serveroutput on size 10000
set feedback off
set termout off
spool change_clob.sql


begin
for rt in ( select table_name,column_name, data_length
from user_tab_columns
where data_type='VARCHAR2' and
data_length=4000 ) loop

dbms_output.put_line('-- Tabela '||rt.table_name||' / Coluna: '||rt.column_name);

dbms_output.put_line('ALTER TABLE '||rt.table_name||' ADD ( AUX CLOB );');

dbms_output.put_line('UPDATE '||rt.table_name||' SET AUX='||rt.column_name||';');

dbms_output.put_line('ALTER TABLE '||rt.table_name||' DROP COLUMN '||rt.column_name||';');

dbms_output.put_line('ALTER TABLE '||rt.table_name||' ADD ( '||rt.column_name||' CLOB );');

dbms_output.put_line('UPDATE '||rt.table_name||' SET '||rt.column_name||'=AUX;');

dbms_output.put_line('ALTER TABLE '||rt.table_name||' DROP COLUMN AUX;');

dbms_output.put_line('--');

end loop;

end;
/

spool off
set feedback on
set termout on

-- End of Script ----------------------------

I know I could have used EXECUTE IMMEDIATE, but I had to have an output script generated.

Extract from output script:

--
-- Tabela TB_TVG_TLN_FA_CLUBE / Coluna: DE_TXT_FA_CLUBE_TXT
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( AUX CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET AUX=DE_TXT_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_TXT_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_TXT_FA_CLUBE_TXT CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET DE_TXT_FA_CLUBE_TXT=AUX;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN AUX;
--
-- Tabela TB_TVG_TLN_FA_CLUBE / Coluna: DE_INFO_ADIC_FA_CLUBE_TXT
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( AUX CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET AUX=DE_INFO_ADIC_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_INFO_ADIC_FA_CLUBE_TXT;
ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_INFO_ADIC_FA_CLUBE_TXT CLOB );
UPDATE TB_TVG_TLN_FA_CLUBE SET DE_INFO_ADIC_FA_CLUBE_TXT=AUX;
ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN AUX;



The rest of the script ran ok, but I got:


ALTER TABLE TB_TVG_TLN_FA_CLUBE DROP COLUMN DE_INFO_ADIC_FA_CLUBE_TXT
*
ERRO na linha 1:
ORA-00955: name is already used by an existing object


ALTER TABLE TB_TVG_TLN_FA_CLUBE ADD ( DE_INFO_ADIC_FA_CLUBE_TXT CLOB )
*
ERRO na linha 1:
ORA-01430: column being added already exists in table


UPDATE TB_TVG_TLN_FA_CLUBE SET DE_INFO_ADIC_FA_CLUBE_TXT=AUX
*
ERRO na linha 1:
ORA-00932: inconsistent datatypes

After that, I have:

anasser@GLBVIG> desc TB_TVG_ENT_EPISODIO
Nome Nulo? Tipo
----------------------------------------------------------- -------- ------------------------------
CD_EPISODIO_ID NOT NULL NUMBER(38)
CD_SITE_ID NOT NULL NUMBER(38)
DE_TITULO_EPIS_TXT VARCHAR2(100)
DT_EXIBICAO_EPIS_DATE DATE
DE_RESUM_EPIS_DESTAC_TXT VARCHAR2(2000)
CT_PUBLICADO_REG VARCHAR2(1)
DT_PUBLICADO_DATE DATE
DE_TXT_COMPLT_EPIS_TXT CLOB

I tried to look it up, but I got no clue...

Do you any idea what might be wrong ?

Tom Kyte
November 01, 2001 - 3:45 pm UTC

don't know whats happening to you -- you are altering a totally different table then you describe and all.  I ran this and it worked just dandy:

ops$tkyte@ORA717DEV.US.ORACLE.COM> @test
ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int, text varchar2(4000) );

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1, 'hello world' );

1 row created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> set echo off
ops$tkyte@ORA717DEV.US.ORACLE.COM> @change_clob
ops$tkyte@ORA717DEV.US.ORACLE.COM> -- Tabela T / Coluna: TEXT
ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T ADD ( AUX CLOB );

Table altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> UPDATE T SET
  2  AUX=TEXT;

1 row updated.

ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T DROP COLUMN
  2  TEXT;

Table altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T ADD ( TEXT
  2  CLOB );

Table altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> UPDATE T SET
  2  TEXT=AUX;

1 row updated.

ops$tkyte@ORA717DEV.US.ORACLE.COM> ALTER TABLE T DROP COLUMN AUX;

Table altered.

ops$tkyte@ORA717DEV.US.ORACLE.COM> --
ops$tkyte@ORA717DEV.US.ORACLE.COM> set echo off

 

Andre - Bug

A MetaLink User, November 01, 2001 - 11:36 am UTC

Andre:

Check out bug #1769244 in MetaLink... it may be relevant.

Space Reuse in Lobsegment and Lobindex

Moorthy Rekapalli, December 13, 2004 - 6:45 pm UTC

Tom,

1) Will a lobsegment inherit PCTUSED property from the table? Reason for asking this is - when we delete rows in a table that has a CLOB, will the lobsegment's blocks be qualified for future inserts after they fall below pctused mark?

2) What about lobindex. If we delete rows from a table, there will be dead space in the Btree indexes. To re-claim the dead space, once in a while we rebuild the indexes on volatile tables. Do we need to do the same for lobindex? If so, how to do that?

3) Can we change the "disable storage in row" to "enable storage in row" and vice versa for a table that has data in it.

I learn a lot from your site and can not thank you enough.

Thanks,
Moorthy.



Tom Kyte
December 13, 2004 - 7:31 pm UTC

1) lobs don't work with pctused. A lob allocates space a CHUNK at a time (a chunk is some multiple of blocks)

a chunk is either

a) used by a lob
b) not used by a lob

there is pctversion which controls how much of the lob space is used for multi-versioning. So the lob chunks that are not used anymore are aged out of the "version" space on a FIFO manner.

So, as soon as some other modification bumps it out of the version pool, it'll be entirely up for reuse

2) nope -- there are degenerate cases whereby an index populated by a sequence, with deletes *MIGHT* (really *MIGHT*) need a coalesce. But an index on say "NAME" would not. lobs are spread out. no rebuild possible actually (you'd have to move the entire lob segment)

3) the enable/disable in row would require a "move" of the lob (which involves a move of the table). everything needs to be rebuilt to do this (things inline need to be moved out of line, things out of line could need to be moved inline...)

Question with Show_Space

A reader, July 31, 2007 - 9:08 am UTC

Tom,  I am trying to use the Show_space procedure on a lob segment, but I don't quite understand what it is showing..

All rows are insert only with a majority of the rows deleted at the end of each day(approx 15k rows).  So in the morning, I would think I should see alot of blocks on the free list but I don't..

Database is 9.2.0.7

Tablespace is set up with
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Here is the table. 

CREATE TABLE ENACWSTRANSACTION
(
  TR_ID          NUMBER(38)                     NOT NULL,
  TRANSACTIONID  NUMBER(17)                     NOT NULL,
  CR_TIMESTAMP   TIMESTAMP(6),
  ACCOUNTXML     CLOB
)
TABLESPACE IC_IND02
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCOMPRESS 
LOB (ACCOUNTXML) STORE AS 
      ( TABLESPACE  IC_IND02 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          4M
                    NEXT             4M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      );


SQL> SET SERVEROUTPUT ON
SQL> 
SQL> DESC ENAC.ENACWSTRANSACTION;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 TR_ID                                     NOT NULL NUMBER(38)
 TRANSACTIONID                             NOT NULL NUMBER(17)
 CR_TIMESTAMP                                       TIMESTAMP(6)
 ACCOUNTXML                                         CLOB

SQL> 
SQL> SELECT COUNT (1)
  2    FROM enac.enacwstransaction;

  COUNT(1)
----------
      1467

SQL> 
SQL> SELECT ROUND (AVG (SYS.DBMS_LOB.getlength (accountxml)))
  2    FROM enac.enacwstransaction;

ROUND(AVG(SYS.DBMS_LOB.GETLENGTH(ACCOUNTXML)))
----------------------------------------------
                                         16537

SQL> 
SQL> 
SQL> BEGIN
  2     show_space ('SYS_LOB0000054533C00004$$', 'ENAC', 'LOB');
  3  END;
  4  /
Unformatted Blocks .....................               1
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................         756,439
Total Blocks............................         758,272
Total Bytes.............................   6,211,764,224
Total MBytes............................           5,924
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              26
Last Used Ext BlockId...................         445,449
Last Used Block.........................             512

PL/SQL procedure successfully completed.


Show_space has "Full Blocks" at 756,439.  There is know way this can be right.  Right?  


Thanks for your time..

Tom Kyte
August 02, 2007 - 9:37 am UTC

why not? space in lob segments is managed very differently.

It isn't managed in the way other data is, that is what makes "lobs" special. They do not generate undo (pctversion is unique to the lob, it controls how space it reused and such).


LOB segment size

Andrei K├╝bar, August 16, 2007 - 5:15 am UTC

Hello,

I have an issue with a LOB segment, which I'm struggling with the third day. I have found the same type of questions on the internet but all of them were left unanswered. I have also searched metalink and oracle forums and still no luck. So maybe You could clarify this for us..

So my test goes like this:

1. I created a tablespace called "smallblock" with 2K blocksize
2. I created a table with a CLOB type field and specified the smallblock tablespace as a storage for the LOB segment:

SCOTT@andrkydb> create table t1 (i int, b clob) lob (b) store as
t1_lob (chunk 2K disable storage in row tablespace smallblock); 


3. I insert data into the table, using a bit less than 2K of data for the clob type column:

SCOTT@andrkydb> begin
  2  for i in 1..1000 loop
  3  insert into t1 values (mod(i,5), rpad('*',2000,'*'));
  4  end loop;
  5  end;
  6  /


4. Now I can see that I have an average of 2000 bytes for each lob item:
SCOTT@andrkydb> select avg(dbms_lob.getlength(b)) from t1;

AVG(DBMS_LOB.GETLENGTH(B))
--------------------------
                      2000


and that all together they take up:
SCOTT@andrkydb> select sum(dbms_lob.getlength(b)) from t1;

SUM(DBMS_LOB.GETLENGTH(B))
--------------------------
                   2000000


But when I take a look at how much is the LOB segment actually taking, I get a result, which is being a total mystery to me:

SCOTT@andrkydb>  select bytes from dba_segments where segment_name = 'T1_LOB';

     BYTES
----------
   5242880


What am I missing? Why is LOB segment is being ~2 times bigger than it is required by the data?

I am on 10.2.0.3 EE, Solaris 5.10 sparc 64bit.
Tom Kyte
August 20, 2007 - 9:56 pm UTC

because there is block overhead and it is more than likely that you need 2 blocks for each 2000 byte lob (which SHOULD be stored inline probably)

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here