4 Replies Latest reply on Dec 27, 2012 7:01 PM by 977635

    regarding lobs...

      I am not that familiar with LOBs, and was hoping someone could shed some light for me.

      I am running Oracle EE, and have made an interesting discovery of this new database that i am responsible.

      First, I found out that I have a table that is about 7.4G, but it has two LOB columns that when I query dba_lobs, I found that they contains 365G of lobs and the table itself has 22G of LOBS - not sure what is the difference.
      SQL> 1  select segment_name, round(sum(bytes)/1024/1024/1024,1) as "SIZE" , segment_type
        2  from dba_segments where owner = 'ARADMIN'
        3  group by segment_name, segment_type
        4  having round(sum(bytes)/1024/1024/1024,1) > 1
        5* order by 2
      SEGMENT_NAME                                SIZE SEGMENT_TYPE
      -------------------------------- --------------- ------------------
      . . .
      SYS_LOB0000077517C00027$$                    4.2 LOBSEGMENT
      SYS_LOB0000210343C00029$$                    4.4 LOBSEGMENT
      SYS_LOB0000077480C00002$$                    4.6 LOBSEGMENT
      T465                                           5 TABLE
      T2052                                        8.3 TABLE
      T2115                                       12.4 TABLE
      T2444                                       13.4 TABLE
      T2179                                       14.8 TABLE
      T2192                                       21.8 TABLE
      SYS_LOB0000077549C00015$$                    182 LOBSEGMENT   <=== (related to table T2192)
      SYS_LOB0000077549C00016$$                  184.4 LOBSEGMENT  <=== (related to table T2192)
      30 rows selected.
      Now, let's look at the which table these LOBS belong...
      SQL> select table_name, column_name, segment_name
        2  from dba_lobs
        3  where segment_name in (
        4  select segment_name from dba_segments where owner = 'ARADMIN'
        5   having round(sum(bytes)/1024/1024/1024,1) > 1
        6  group by segment_name
        7  )
        8  /
      TABLE_NAME                       COLUMN_NAME                      SEGMENT_NAME
      -------------------------------- -------------------------------- --------------------------------
      B1947C536880923                  C536880923                       SYS_LOB0000077310C00002$$
      T2051                            C536870998                       SYS_LOB0000077426C00041$$
      T2052                            C536870987                       SYS_LOB0000077440C00063$$
      T2115                            C536870913                       SYS_LOB0000077463C00009$$
      B2125C536880912                  C536880912                       SYS_LOB0000077480C00002$$
      B2125C536880913                  C536880913                       SYS_LOB0000077483C00002$$
      T2179                            C536870936                       SYS_LOB0000077517C00027$$
      T2192                            C456                             SYS_LOB0000077549C00015$$   <====
      T2192                            C459                             SYS_LOB0000077549C00016$$   <====
      T2444                            C536870936                       SYS_LOB0000210343C00029$$
      T1990                            C536870937                       SYS_LOB0000250271C00026$$
      11 rows selected.
      So, from the above, I noticed in the first query that the table T2192 itself contains 21.8G of LOBS, and, that the columns C456 and C459 of same table contain a total of (181.7+183.9) = 365.6G.

      First question is how can the table be only 21.8G, and the lob segments of the table columns be 365.6G of Lobs?
      It seems some lobs must be external, while others are part of the actual table.

      Next, I am wondering if a row is deleted from the table, would the lobs associated with that row that are referenced by columns C456 and C459 also be deleted.
      Discussing this with our Sr. Developer, he says the table is purged of rows older than 6 months, but my question is whether the Lobs are actually purged with the rows.

      Any ideas?

      Edited by: 974632 on Dec 27, 2012 8:05 AM
        • 1. Re: regarding lobs...
          John Spencer
          The space taken by the lob segments is not reported as part of the space for the table segment, welll, not all of it anyway. What is reported as part of the table's size is the size of the lob locator (a pointer to the "real" lob segment, 16 bytes if I recall correctly) and any lobs that are stored in-line in the table (those less than about 4K). To get the "total" size of the table you need to add the space taken by ant lob columns.

          If you delete a row, then the space taken in the lob segments will be set as free space withing the existing segment. It will eventually (there are a number of around redo and undo which are handled differently for lobs), be re-used by data for other rows, but it could take a while.

          There are a number of documents on My Oracle support regarding this, many are linked to from:

          Troubleshooting Guide (TSG) - Large Objects (LOBs) [ID 846562.1]

          • 2. Re: regarding lobs...
            Thank you John. That's exactly the information I was looking for.
            • 3. Re: regarding lobs...
              Hi John,

              Reading note 386341.1, this is pretty messed up about lobs.

              First, the UNDO data for a LOB segment is kept within the LOB segment space, e.g., when lobs are deleted, etc. Yuck!
              So, you are right about the space eventually being returned to the database, but surely we can do better than that!

              Then, when we check for the size of the lobs using dbms_lob.getlength, (since we are using AL32UTF8), it returns it in the number of characters instead of bytes.
              So, then we have to convert - ref. note 790886.1. An enhancement request via Bug 7156454 has been filed to get this functionality and is under consideration by development.

              So, how does one (safely) clean up lobs that have been deleted in the database?
              It seems that doing an alter table... 'move lob' might work, and also an alter table ... modify lob (...) (shrink space [cascade]);

              But with this being production, I'm very concerned about all the related bugs, even though I am on
              WARNING : shrinking / reorganizing BASICFILE lobs can cause performance problems due to "enq: HW contention" waits
              Serious LOB corruption can occur after an
              ALTER TABLE <table> MODIFY LOB ("<lob_column>") (STORAGE (freelists <n>));
              has been issued on a LOB column which resides in a manual space managed tablespace. Subsequent use of the LOB can fail with various internal errors such as:
              ORA-600 [ktsbvmap1]
              ORA-600 [25012]
              For more information, please refer to bug 4450606.
              #2. Be aware of the following bug before using the SHRINK option in releases which are <=
              Bug: 5636728 LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
              Please check:
              Note.5636728.8 Ext/Pub Bug 5636728 - LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
              for details on it.
              #3. Be aware that, sometimes, it could be needed to perform the shrink operation twice, in order to avoid the:
              is fixed in 10.2.
              From looking at note: 1451124.1, it seems the best options are:
              1) alter table move (locks the table, and requires additional space of at least double the size of the table).
              2) do an export / drop the table / and reimport - again downtime required.

              Neither option are possible in our environment.
              • 4. Re: regarding lobs...
                In case anyone is interested, here is a great note on showing the results of using alter table ... MOVE to clean up the lobs.

                How To Use ALTER TABLE ... MOVE To Shrink A Table (Including BASICFILE / SECUREFILE LOBs) [ID 1396120.1]