8 Replies Latest reply: Sep 12, 2013 3:45 PM by TSharma-Oracle RSS

    LOB space issue

    user10674190

      Hi,

       

       

      We need to reclaim unused LOB space on database.

      Our database supports OTM application and there are three tables with CLOB column: I_LOG, I_TRANSACTION and I_TRANSMISSION - are so huge...

      Last week we implemented the out of the box procedure domainman.transmission_purge in order to keep only 60 days for I_tables.

      But, unused LOB space on database was not reclaimed, so I followed this doc: (http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#CHDHBHAB)

      for try to reclaim unused LOB space on database, and nevertheless unused LOB space on database was not reclaimed.

      Please, anybody can help me?

       

       

       

      --identifying columns:

       

      SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

        2  from dba_lobs

        3  where TABLE_NAME ='I_LOG'

        4  /

       

      OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

      ------------------------------ ------------------------------ ------------------------------ ------------------------------

      GLOGOWNER                      I_LOG                          I_MESSAGE_TEXT                 I_LOG_I_MESSAGE_TEXT          

       

      SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

        2  from dba_lobs

        3  where TABLE_NAME ='I_TRANSACTION'

        4  /

       

      OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

      ------------------------------ ------------------------------ ------------------------------ ------------------------------

      GLOGOWNER                      I_TRANSACTION                  XML_BLOB                       I_TRANSACTION_XML_BLOB        

       

      SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

        2  from dba_lobs

        3  where TABLE_NAME ='I_TRANSMISSION'

        4  /

       

      OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

      ------------------------------ ------------------------------ ------------------------------ ------------------------------

      GLOGOWNER                      I_TRANSMISSION                 XML_BLOB                       I_TRANSMISSION_XML_BLOB       

       

       

       

       

       

      --looking free space before run SHRINK SPACE:

       

      set pages 999

      col tablespace_name format a40

      col "size MB" format 999,999,999

      col "free MB" format 99,999,999

      col "% Used" format 999

      select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

      ,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

      ,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

                     100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

      from    (select tablespace_name, sum(bytes)/1024/1024 used_mb

          from     dba_data_files group by tablespace_name union all

          select     tablespace_name || '  **TEMP**'

          ,    sum(bytes)/1024/1024 used_mb

          from     dba_temp_files group by tablespace_name) tsu

      ,    (select tablespace_name, sum(bytes)/1024/1024 free_mb

          from     dba_free_space group by tablespace_name) tsf

      where    tsu.tablespace_name = tsf.tablespace_name

      order    by 4 desc

      /

       

       

      TABLESPACE_NAME                               size MB     free MB % used

      ---------------------------------------- ------------ ----------- ------

      TEMP  **TEMP**                                  6,000           0    100

      LOB3                                           73,000       3,640     95

      LOB2                                           46,500       3,935     91

      DATA                                           21,000       3,058     85

      LOB1                                           23,000       4,085     82

      SYSAUX                                         17,000       2,984     82

      LOB4                                           21,000       3,805     81

      UNDOTBS2                                        6,000       1,559     74

      REPORT                                            173          50     71

      SYSTEM                                          1,000         285     71

      ARCHIVE                                           100          31     69

      INDX                                           15,000       5,094     66

      BPL_DAY7                                           15           5     66

      BPL_DAY5                                           15           5     66

      BPL_DAY6                                           15           5     66

      LOB7                                              150          60     60

      MSG_PART_TBS1                                      50          23     54

      BPL_DAY3                                           15           8     46

      BPL_DAY4                                           15           8     46

      MSG_LOB_TBS1                                      500         285     43

      BPL_DAY1                                           10           6     40

      PART_3                                          3,000       1,796     40

      PART_1                                          1,978       1,175     40

      LOB5                                              250         155     38

      REPORTINDX                                         30          19     36

      BPL_DAY2                                           25          17     32

      PART_2                                          2,500       1,872     25

      LOB6                                              100          75     25

      PART_4                                         12,532      11,075     11

      DATA_DBA                                           75          69      9

      USERS                                              50          49      2

       

      31 rows selected.

       

       

      --Running SHRINK SPACE

       

      SQL> alter table I_TRANSACTION MODIFY lob (XML_BLOB) (SHRINK SPACE);

       

      Table altered.

       

      Elapsed: 00:04:38.29

      SQL>

      SQL> alter table I_LOG MODIFY lob (I_MESSAGE_TEXT) (SHRINK SPACE);

       

      Table altered.

       

      Elapsed: 00:00:28.07

      SQL>

      SQL> alter table I_TRANSMISSION MODIFY lob (XML_BLOB)   (SHRINK SPACE);

       

      Table altered.

       

      Elapsed: 00:00:42.04

       

       

       

       

      --looking free space after run SHRINK SPACE:

       

      set pages 999

      col tablespace_name format a40

      col "size MB" format 999,999,999

      col "free MB" format 99,999,999

      col "% Used" format 999

      select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

      ,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

      ,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

                     100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

      from    (select tablespace_name, sum(bytes)/1024/1024 used_mb

          from     dba_data_files group by tablespace_name union all

          select     tablespace_name || '  **TEMP**'

          ,    sum(bytes)/1024/1024 used_mb

          from     dba_temp_files group by tablespace_name) tsu

      ,    (select tablespace_name, sum(bytes)/1024/1024 free_mb

          from     dba_free_space group by tablespace_name) tsf

      where    tsu.tablespace_name = tsf.tablespace_name

      order    by 4 desc

      /

       

       

      TABLESPACE_NAME                               size MB     free MB % used

      ---------------------------------------- ------------ ----------- ------

      TEMP  **TEMP**                                  6,000           0    100

      LOB3                                           73,000       3,640     95

      LOB2                                           46,500       3,935     91

      DATA                                           21,000       3,058     85

      LOB1                                           23,000       4,085     82

      SYSAUX                                         17,000       2,984     82

      LOB4                                           21,000       3,790     81

      UNDOTBS2                                        6,000       1,493     75

      REPORT                                            173          50     71

      SYSTEM                                          1,000         285     71

      ARCHIVE                                           100          31     69

      INDX                                           15,000       5,093     66

      BPL_DAY7                                           15           5     66

      BPL_DAY5                                           15           5     66

      BPL_DAY6                                           15           5     66

      LOB7                                              150          60     60

      MSG_PART_TBS1                                      50          23     54

      BPL_DAY3                                           15           8     46

      BPL_DAY4                                           15           8     46

      MSG_LOB_TBS1                                      500         285     43

      BPL_DAY1                                           10           6     40

      PART_3                                          3,000       1,796     40

      PART_1                                          1,978       1,175     40

      LOB5                                              250         155     38

      REPORTINDX                                         30          19     36

      BPL_DAY2                                           25          17     32

      PART_2                                          2,500       1,872     25

      LOB6                                              100          75     25

      PART_4                                         12,532      11,075     11

      DATA_DBA                                           75          69      9

      USERS                                              50          49      2

       

      31 rows selected.

        • 1. Re: LOB space issue
          TSharma-Oracle

          user10674190 wrote:

           

          Hi,

           

           

          We need to reclaim unused LOB space on database.

          Our database supports OTM application and there are three tables with CLOB column: I_LOG, I_TRANSACTION and I_TRANSMISSION - are so huge...

          Last week we implemented the out of the box procedure domainman.transmission_purge in order to keep only 60 days for I_tables.

          But, unused LOB space on database was not reclaimed, so I followed this doc: (http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#CHDHBHAB)

          for try to reclaim unused LOB space on database, and nevertheless unused LOB space on database was not reclaimed.

          Please, anybody can help me?

           

           

           

          --identifying columns:

           

          SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

            2  from dba_lobs

            3  where TABLE_NAME ='I_LOG'

            4  /

           

          OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

          ------------------------------ ------------------------------ ------------------------------ ------------------------------

          GLOGOWNER                      I_LOG                          I_MESSAGE_TEXT                 I_LOG_I_MESSAGE_TEXT          

           

          SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

            2  from dba_lobs

            3  where TABLE_NAME ='I_TRANSACTION'

            4  /

           

          OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

          ------------------------------ ------------------------------ ------------------------------ ------------------------------

          GLOGOWNER                      I_TRANSACTION                  XML_BLOB                       I_TRANSACTION_XML_BLOB        

           

          SQL> select OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME

            2  from dba_lobs

            3  where TABLE_NAME ='I_TRANSMISSION'

            4  /

           

          OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                  

          ------------------------------ ------------------------------ ------------------------------ ------------------------------

          GLOGOWNER                      I_TRANSMISSION                 XML_BLOB                       I_TRANSMISSION_XML_BLOB       

           

           

           

           

           

          --looking free space before run SHRINK SPACE:

           

          set pages 999

          col tablespace_name format a40

          col "size MB" format 999,999,999

          col "free MB" format 99,999,999

          col "% Used" format 999

          select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

          ,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

          ,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

                         100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

          from    (select tablespace_name, sum(bytes)/1024/1024 used_mb

              from     dba_data_files group by tablespace_name union all

              select     tablespace_name || '  **TEMP**'

              ,    sum(bytes)/1024/1024 used_mb

              from     dba_temp_files group by tablespace_name) tsu

          ,    (select tablespace_name, sum(bytes)/1024/1024 free_mb

              from     dba_free_space group by tablespace_name) tsf

          where    tsu.tablespace_name = tsf.tablespace_name

          order    by 4 desc

          /

           

           

          TABLESPACE_NAME                               size MB     free MB % used

          ---------------------------------------- ------------ ----------- ------

          TEMP  **TEMP**                                  6,000           0    100

          LOB3                                           73,000       3,640     95

          LOB2                                           46,500       3,935     91

          DATA                                           21,000       3,058     85

          LOB1                                           23,000       4,085     82

          SYSAUX                                         17,000       2,984     82

          LOB4                                           21,000       3,805     81

          UNDOTBS2                                        6,000       1,559     74

          REPORT                                            173          50     71

          SYSTEM                                          1,000         285     71

          ARCHIVE                                           100          31     69

          INDX                                           15,000       5,094     66

          BPL_DAY7                                           15           5     66

          BPL_DAY5                                           15           5     66

          BPL_DAY6                                           15           5     66

          LOB7                                              150          60     60

          MSG_PART_TBS1                                      50          23     54

          BPL_DAY3                                           15           8     46

          BPL_DAY4                                           15           8     46

          MSG_LOB_TBS1                                      500         285     43

          BPL_DAY1                                           10           6     40

          PART_3                                          3,000       1,796     40

          PART_1                                          1,978       1,175     40

          LOB5                                              250         155     38

          REPORTINDX                                         30          19     36

          BPL_DAY2                                           25          17     32

          PART_2                                          2,500       1,872     25

          LOB6                                              100          75     25

          PART_4                                         12,532      11,075     11

          DATA_DBA                                           75          69      9

          USERS                                              50          49      2

           

          31 rows selected.

           

           

          --Running SHRINK SPACE

           

          SQL> alter table I_TRANSACTION MODIFY lob (XML_BLOB) (SHRINK SPACE);

           

          Table altered.

           

          Elapsed: 00:04:38.29

          SQL>

          SQL> alter table I_LOG MODIFY lob (I_MESSAGE_TEXT) (SHRINK SPACE);

           

          Table altered.

           

          Elapsed: 00:00:28.07

          SQL>

          SQL> alter table I_TRANSMISSION MODIFY lob (XML_BLOB)   (SHRINK SPACE);

           

          Table altered.

           

          Elapsed: 00:00:42.04

           

           

           

           

          --looking free space after run SHRINK SPACE:

           

          set pages 999

          col tablespace_name format a40

          col "size MB" format 999,999,999

          col "free MB" format 99,999,999

          col "% Used" format 999

          select     tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

          ,    decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

          ,    decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

                         100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

          from    (select tablespace_name, sum(bytes)/1024/1024 used_mb

              from     dba_data_files group by tablespace_name union all

              select     tablespace_name || '  **TEMP**'

              ,    sum(bytes)/1024/1024 used_mb

              from     dba_temp_files group by tablespace_name) tsu

          ,    (select tablespace_name, sum(bytes)/1024/1024 free_mb

              from     dba_free_space group by tablespace_name) tsf

          where    tsu.tablespace_name = tsf.tablespace_name

          order    by 4 desc

          /

           

           

          TABLESPACE_NAME                               size MB     free MB % used

          ---------------------------------------- ------------ ----------- ------

          TEMP  **TEMP**                                  6,000           0    100

          LOB3                                           73,000       3,640     95

          LOB2                                           46,500       3,935     91

          DATA                                           21,000       3,058     85

          LOB1                                           23,000       4,085     82

          SYSAUX                                         17,000       2,984     82

          LOB4                                           21,000       3,790     81

          UNDOTBS2                                        6,000       1,493     75

          REPORT                                            173          50     71

          SYSTEM                                          1,000         285     71

          ARCHIVE                                           100          31     69

          INDX                                           15,000       5,093     66

          BPL_DAY7                                           15           5     66

          BPL_DAY5                                           15           5     66

          BPL_DAY6                                           15           5     66

          LOB7                                              150          60     60

          MSG_PART_TBS1                                      50          23     54

          BPL_DAY3                                           15           8     46

          BPL_DAY4                                           15           8     46

          MSG_LOB_TBS1                                      500         285     43

          BPL_DAY1                                           10           6     40

          PART_3                                          3,000       1,796     40

          PART_1                                          1,978       1,175     40

          LOB5                                              250         155     38

          REPORTINDX                                         30          19     36

          BPL_DAY2                                           25          17     32

          PART_2                                          2,500       1,872     25

          LOB6                                              100          75     25

          PART_4                                         12,532      11,075     11

          DATA_DBA                                           75          69      9

          USERS                                              50          49      2

           

          31 rows selected.

          You are here shrinking the segment not the tablespace. It will shrink the table segment not the tablespace. You will need to ehrink the tablespace in order to get space from tablespace. It seems your lob segments are already intact. You did not show above that your LOB segments has some free space. If your segment wont have any free space to release, you will not see any increase free space at the tablespace level,

           

          Please post the result where you see the free space in the LOB segments itself.

          • 2. Re: LOB space issue
            Eduardo Legatti

            HI,

             

            By the way, I think you could format your SQL results for a better view..

             

            Cheers

             

            Legatti

            • 3. Re: LOB space issue
              user10674190

              Hi, thanks for your answer and try to help me.

              How can I get free space in the LOB segments?

              • 4. Re: LOB space issue
                TSharma-Oracle

                First of all , you did not even know if you have any free space in LOB segment to reclaim. so your claim about reclaim becomes invalid. Check this note:

                 

                 

                Also. check this

                 

                https://forums.oracle.com/thread/924154

                 

                • 5. Re: LOB space issue
                  user10674190
                  Hi, I followed this Doc ID 386341.1:And I found this segments in my LOB PARTITION:

                   

                  SQL> select owner,segment_NAME, PARTITION_NAME, bytes/1024/1024

                    2  from dba_segments

                    3  where segment_name='I_TRANSMISSION_XML_BLOB'

                    4  order by 4

                    5  /

                   

                  OWNER           SEGMENT_NAME                                       PARTITION_NAME       BYTES/1024/1024

                  --------------- -------------------------------------------------- -------------------- ---------------

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P747                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P749                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P751                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P753                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P791                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P757                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P759                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P755                       5

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P773                     255

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P809                     260

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P825                     580

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P827                    9230

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P771                   10480

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P789                   21625

                  GLOGOWNER       I_TRANSMISSION_XML_BLOB                            SYS_LOB_P807                   34855

                   

                  15 rows selected.

                   

                  I use dbms_space.space_usage for two LOB PARTITIONS:

                   

                  declare

                  v_unformatted_blocks number;

                  v_unformatted_bytes number;

                  v_fs1_blocks number;

                  v_fs1_bytes number;

                  v_fs2_blocks number;

                  v_fs2_bytes number;

                  v_fs3_blocks number;

                  v_fs3_bytes number;

                  v_fs4_blocks number;

                  v_fs4_bytes number;

                  v_full_blocks number;

                  v_full_bytes number;

                  begin

                  dbms_space.space_usage ('GLOGOWNER', 'I_TRANSMISSION_XML_BLOB', 'LOB PARTITION', v_unformatted_blocks,

                  v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

                  v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,'SYS_LOB_P807');

                  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

                  dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

                  dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

                  dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

                  dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

                  dbms_output.put_line('Full Blocks = '||v_full_blocks);

                  end;

                  /

                   

                   

                  Unformatted Blocks = 18

                  FS1 Blocks = 0

                  FS2 Blocks = 0

                  FS3 Blocks = 0

                  FS4 Blocks = 0

                  Full Blocks = 2223257

                   

                  PL/SQL procedure successfully completed.

                   

                   

                   

                  declare

                  v_unformatted_blocks number;

                  v_unformatted_bytes number;

                  v_fs1_blocks number;

                  v_fs1_bytes number;

                  v_fs2_blocks number;

                  v_fs2_bytes number;

                  v_fs3_blocks number;

                  v_fs3_bytes number;

                  v_fs4_blocks number;

                  v_fs4_bytes number;

                  v_full_blocks number;

                  v_full_bytes number;

                  begin

                  dbms_space.space_usage ('GLOGOWNER', 'I_TRANSMISSION_XML_BLOB', 'LOB PARTITION', v_unformatted_blocks,

                  v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

                  v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes,'SYS_LOB_P789');

                  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

                  dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

                  dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

                  dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

                  dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

                  dbms_output.put_line('Full Blocks = '||v_full_blocks);

                  end;

                  /

                   

                  Unformatted Blocks = 100

                  FS1 Blocks = 0

                  FS2 Blocks = 0

                  FS3 Blocks = 0

                  FS4 Blocks = 55

                  Full Blocks = 1379272

                   

                  How can I see free space ?

                  • 6. Re: LOB space issue
                    TSharma-Oracle

                    Did you read the document carefully? use dbms_space.unused_space() to find the unused space. This has been explained clearly in the doc  I mentioned above. Also read the doc very carefully. it also says

                    even when there is free space in the LOB, this does not mean this space can be released to the tablespace, it could be under the HWM. Read the doc line by line.

                    • 7. Re: LOB space issue
                      user10674190

                      Hi TSharma, thanks for try to help me. Actually, I was using dbms_space.space_usage instead of dbms_space.unused_space So, now I think is clear for me, for example, if I look SYS_LOB_P807 LOB PARTITION: SQL> select owner,segment_NAME, PARTITION_NAME, bytes/1024/1024   2  from dba_segments   3  where segment_name='I_TRANSMISSION_XML_BLOB'   4  and PARTITION_NAME = 'SYS_LOB_P807'   5  / OWNER      SEGMENT_NAME              PARTITION_NAME  BYTES/1024/1024 ----------- -------------------------- ---------------- --------------- GLOGOWNER  I_TRANSMISSION_XML_BLOB    SYS_LOB_P807              34855 it has 34GB, So I thought that it had a a lot space for reclaim, because I am keeping only 60 days in this table, but, after use dbms_space.unused_space I can see only 3MB for release.... So, I think is not worth perform SHRINK SPACE in this case. What do you think? set serveroutput on declare TOTAL_BLOCKS number; TOTAL_BYTES number; UNUSED_BLOCKS number; UNUSED_BYTES number; LAST_USED_EXTENT_FILE_ID number; LAST_USED_EXTENT_BLOCK_ID number; LAST_USED_BLOCK number; begin dbms_space.unused_space('GLOGOWNER','I_TRANSMISSION_XML_BLOB','LOB PARTITION', TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES, LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID, LAST_USED_BLOCK,'SYS_LOB_P807'); dbms_output.put_line('SEGMENT_NAME = '); dbms_output.put_line('-----------------------------------'); dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS); dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES); dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS); dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES); dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID); dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID); dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK); end; / SEGMENT_NAME = ----------------------------------- TOTAL_BLOCKS = 2230720 TOTAL_BYTES = 36548116480 UNUSED_BLOCKS = 222 UNUSED BYTES = 3637248 LAST_USED_EXTENT_FILE_ID = 128 LAST_USED_EXTENT_BLOCK_ID = 621824 LAST_USED_BLOCK = 98 PL/SQL procedure successfully completed.

                      • 8. Re: LOB space issue
                        TSharma-Oracle

                        Right, You can mark this post as answered if you think your question has been answered.