Thanks Connor.
Here is my results from 12c(12.2.0.1) and 11g (11.2.0.4).
Both System generated and user defined LOB names doesn't work in 11g instance.
with 12.2 this works perfect for both.
demo@ORA12C>
demo@ORA12C> create table t (x int,y clob);
Table created.
demo@ORA12C> insert into t
2 select object_id,rpad(object_name,4000,'*')
3 from all_objects;
60682 rows created.
demo@ORA12C> commit;
Commit complete.
demo@ORA12C> select segment_name,index_name
2 from user_lobs
3 where table_name ='T' ;
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000073986C00002$$ SYS_IL0000073986C00002$$
demo@ORA12C> declare
2 l_segment_size_blocks number;
3 l_segment_size_bytes number;
4 l_used_blocks number;
5 l_used_bytes number;
6 l_expired_blocks number;
7 l_expired_bytes number;
8 l_unexpired_blocks number;
9 l_unexpired_bytes number;
10 begin
11 dbms_space.space_usage(
12 user,
13 'SYS_LOB0000073986C00002$$',
14 'LOB',
15 l_segment_size_blocks,
16 l_segment_size_bytes ,
17 l_used_blocks ,
18 l_used_bytes ,
19 l_expired_blocks ,
20 l_expired_bytes ,
21 l_unexpired_blocks ,
22 l_unexpired_bytes
23 );
24 dbms_output.put_line('-----------------------------------');
25 dbms_output.put_line('l_segment_size_bytes = '||l_segment_size_bytes);
26 dbms_output.put_line('l_used_bytes = '||l_used_bytes);
27 dbms_output.put_line('l_expired_bytes = '||l_expired_bytes);
28 dbms_output.put_line('l_unexpired_bytes = '||l_unexpired_bytes);
29 end;
30 /
-----------------------------------
l_segment_size_bytes = 553844736
l_used_bytes = 497106944
l_expired_bytes = 54575104
l_unexpired_bytes = 0
PL/SQL procedure successfully completed.
demo@ORA12C>
but with 11g, it fails like this.
demo@ORA11G> create table t (x int,y clob);
Table created.
demo@ORA11G>
demo@ORA11G> insert into t
2 select object_id,rpad(object_name,4000,'*')
3 from all_objects;
75725 rows created.
demo@ORA11G> commit;
Commit complete.
demo@ORA11G> select segment_name,index_name
2 from user_lobs
3 where table_name ='T';
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000089955C00002$$ SYS_IL0000089955C00002$$
demo@ORA11G> declare
2 l_segment_size_blocks number;
3 l_segment_size_bytes number;
4 l_used_blocks number;
5 l_used_bytes number;
6 l_expired_blocks number;
7 l_expired_bytes number;
8 l_unexpired_blocks number;
9 l_unexpired_bytes number;
10 begin
11 dbms_space.space_usage(
12 user,
13 'SYS_LOB0000089955C00002$$',
14 'LOB',
15 l_segment_size_blocks,
16 l_segment_size_bytes ,
17 l_used_blocks ,
18 l_used_bytes ,
19 l_expired_blocks ,
20 l_expired_bytes ,
21 l_unexpired_blocks ,
22 l_unexpired_bytes
23 );
24 dbms_output.put_line('-----------------------------------');
25 dbms_output.put_line('l_segment_size_bytes = '||l_segment_size_bytes);
26 dbms_output.put_line('l_used_bytes = '||l_used_bytes);
27 dbms_output.put_line('l_expired_bytes = '||l_expired_bytes);
28 dbms_output.put_line('l_unexpired_bytes = '||l_unexpired_bytes);
29 end;
30 /
declare
*
ERROR at line 1:
ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
ORA-06512: at "SYS.DBMS_SPACE", line 234
ORA-06512: at line 11
demo@ORA11G> drop table t purge;
Table dropped.
demo@ORA11G>
demo@ORA11G> create table t (x int,y clob)
2 lob(y)
3 store as t_y_lob;
Table created.
demo@ORA11G>
demo@ORA11G> insert into t
2 select object_id,rpad(object_name,4000,'*')
3 from all_objects
4 where rownum <=1000;
1000 rows created.
demo@ORA11G> commit;
Commit complete.
demo@ORA11G> select segment_name,index_name
2 from user_lobs
3 where table_name ='T';
SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
T_Y_LOB SYS_IL0000089958C00002$$
demo@ORA11G> declare
2 l_segment_size_blocks number;
3 l_segment_size_bytes number;
4 l_used_blocks number;
5 l_used_bytes number;
6 l_expired_blocks number;
7 l_expired_bytes number;
8 l_unexpired_blocks number;
9 l_unexpired_bytes number;
10 begin
11 dbms_space.space_usage(
12 user,
13 'T_Y_LOB',
14 'LOB',
15 l_segment_size_blocks,
16 l_segment_size_bytes ,
17 l_used_blocks ,
18 l_used_bytes ,
19 l_expired_blocks ,
20 l_expired_bytes ,
21 l_unexpired_blocks ,
22 l_unexpired_bytes
23 );
24 dbms_output.put_line('-----------------------------------');
25 dbms_output.put_line('l_segment_size_bytes = '||l_segment_size_bytes);
26 dbms_output.put_line('l_used_bytes = '||l_used_bytes);
27 dbms_output.put_line('l_expired_bytes = '||l_expired_bytes);
28 dbms_output.put_line('l_unexpired_bytes = '||l_unexpired_bytes);
29 end;
30 /
declare
*
ERROR at line 1:
ORA-03213: Invalid Lob Segment Name for DBMS_SPACE package
ORA-06512: at "SYS.DBMS_SPACE", line 234
ORA-06512: at line 11
demo@ORA11G>