5 Replies Latest reply: Feb 12, 2013 2:32 PM by Osama_Mustafa RSS

    DBMS_SPACE Issue:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package

    Arun Natarajan-OC
      Hi Friends,

      I am getting error while trying the find the unused space from LOB Segment using the Metalink Note : How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM [ID 386341.1]

      SQL> @lob.sql
      declare
      *
      ERROR at line 1:
      ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
      ORA-06512: at "SYS.DBMS_SPACE", line 152
      ORA-06512: at line 11

      *[oracle@s1]$ more lob.sql*
      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('vehicle_prod','SYS_LOB0000100824C00003$$','LOB',
      TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
      LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
      LAST_USED_BLOCK);

      dbms_output.put_line('SEGMENT_NAME =SYS_LOB0000100824C00003$$');
      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;
      /

      I referred Bug 14181074 : DBMS_SPACE.SPACE_USAGE GENERATES ORA-3213 but doesn't help.

      Regards,
      DB

      Edited by: 839396 on Feb 11, 2013 4:06 PM
        • 1. Re: DBMS_SPACE Issue:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
          Justin_Mungal
          Works fine for me, on 11.2.0.3.
          SQL> select segment_name, segment_type, bytes from dba_segments where owner='JUSTIN';
          
          SEGMENT_NAME              SEGMENT_TYPE            BYTES
          ------------------------- ------------------ ----------
          SYS_LOB0000075919C00002$$ LOBSEGMENT             131072
          SYS_C0011101              INDEX                   65536
          SYS_IL0000075919C00002$$  LOBINDEX                65536
          LOBTABLE                  TABLE                   65536
          
          
          SQL> 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('JUSTIN','SYS_LOB0000075919C00002$$','LOB',
          TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
          LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
          LAST_USED_BLOCK);
          
          dbms_output.put_line('SEGMENT_NAME =SYS_LOB0000075919C00002$$');
          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;
          /SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27
          SEGMENT_NAME =SYS_LOB0000075919C00002$$
          -----------------------------------
          TOTAL_BLOCKS = 16
          TOTAL_BYTES = 131072
          UNUSED_BLOCKS = 0
          UNUSED BYTES = 0
          LAST_USED_EXTENT_FILE_ID = 4
          LAST_USED_EXTENT_BLOCK_ID = 200
          LAST_USED_BLOCK = 8
          
          PL/SQL procedure successfully completed.
          Double check your segment name with dba_segments. It sounds like you're just referencing an invalid segment name.
          • 2. Re: DBMS_SPACE Issue:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
            Osama_Mustafa
            Check
            EM DBConsole Reports ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package [ID 809633.1]
            OERR: ORA-3213 Invalid Lob Segment Name for DBMS_SPACE package [ID 75175.1]
            • 3. Re: DBMS_SPACE Issue:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
              Arun Natarajan-OC
              Hi Justin,

              Thanks and the issue is the owner name is in lower case and now i corrected to upper case and the issue fixed.

              Regards,
              DB
              • 4. Re: DBMS_SPACE Issue:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
                Arun Natarajan-OC
                Hi osama,

                Thanks and the issue is the owner name is in lower case and now i corrected to upper case and the issue fixed.

                Regards,
                DB