Oracle Database Discussions

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