Oracle Database Discussions

I have a partitioned table that contain one SECUREFILE column (Oracle 11.2.0.2 Linux 64bit).

I would like to check its space usage in order to check if the compression and deduplication will help us to reduce the CLOB column's size.

I've found the following note in metalink: How to Check Space Occupied by LOB Compression
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=861344.1

However, this link is refered to a regular table, not partitioned.
I've seen in the documentation of DBMS_SPACE.SPACE_USAGE that there is a partition name attribute, but I'm not seem to use the procedure correctly.


I'm trying to run the following code:
DECLARE
l_segment_size_blocks NUMBER;
l_segment_size_bytes NUMBER;
l_used_blocks NUMBER;
l_used_bytes NUMBER;
l_expired_blocks NUMBER;
l_expired_bytes NUMBER;
l_unexpired_blocks NUMBER;
l_unexpired_bytes NUMBER;
v_segname varchar2(30);
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => 'LOG',
partition_name =>'PART_03_2009',
segment_name => ' [https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=861344.1|https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=861344.1] SECURITY_LOG',
segment_type => 'TABLE PARTITION',
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(' Segment Blocks = '||l_segment_size_blocks||' Bytes = '||l_segment_size_bytes);
DBMS_OUTPUT.PUT_LINE(' Used Blocks = '||l_used_blocks||' Bytes = '||l_used_bytes);
DBMS_OUTPUT.PUT_LINE(' Expired Blocks = '||l_expired_blocks||' Bytes = '||l_expired_bytes);
DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks = '||l_unexpired_blocks||' Bytes = '||l_unexpired_bytes);
DBMS_OUTPUT.PUT_LINE('=============================================');
END;
/


And I get this error : ORA-03213: Invalid Lob Segment name for DBMS_SPACE package

Some information

select partition_name, interval, num_rows
from dba_tab_partitions where table_owner = 'LOG'
order by partition_position;
--------------------------------------------------------------------
1 PART_03_2009 NO 1235667
2 PART_04_2009 NO 4621135
3 PART_05_2009 NO 1322246
4 PART_06_2009 NO 6442123


SELECT segment_name, partition_name, segment_type
FROM DBA_SEGMENTS
WHERE OWNER = 'LOG'
and segment_type in ('TABLE PARTITION','LOB PARTITION')
order by 2;
------------------------------------------------------------------------------
SECURITY_LOG PART_03_2009 TABLE PARTITION
SECURITY_LOG PART_04_2009 TABLE PARTITION
SECURITY_LOG PART_05_2009 TABLE PARTITION
SECURITY_LOG PART_06_2009 TABLE PARTITION
SYS_LOB0000048317C00014$$ SYS_LOB_P495 LOB PARTITION
SYS_LOB0000048317C00014$$ SYS_LOB_P498 LOB PARTITION
SYS_LOB0000048317C00014$$ SYS_LOB_P519 LOB PARTITION
SYS_LOB0000048317C00014$$ SYS_LOB_P523 LOB PARTITION


What am I doing wrong ? What should I change?

Thanks in advance,
Roni.