This discussion is archived
5 Replies Latest reply: Feb 12, 2013 12:32 PM by Osama_Mustafa RSS

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

Arun Natarajan-OC Explorer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points