I wrote on deferred segment creation recently.
Today I was looking for specific storage attributes that I used to find in user_segments. They are no longer here. Where are they then?
test case :
create table t(x clob) store (x) as securefile x (retention max storage(maxsize 8192000000));
Where do I find the retention max max_size of my securefile? once the segment is created, it is easy to find it in user_segments
SQL> insert into t values('x'); 1 row created. SQL> select max_size from user_segments where segment_name='X'; MAX_SIZE ---------- 1000000
1000000 in blocks is my specified 8192000000 bytes.
let’s go back
SQL> truncate table t drop all storage; Table truncated. SQL> select max_size from user_segments where segment_name='X'; no rows selected
It is not there.
you must dig in the sys tables to find out. Specifically there is a new table for the deferred segments
SQL> select maxsiz_stg from sys.deferred_stg$ where obj# in (select obj# from sys.obj$ where name='X'); MAXSIZ_STG ---------- 1000000
Of course you should not base your business logic on internal tables that may change in a next release. A more appropriate workaround would be to disable deferred segment creation 🙂