Oracle logo

nothing in user_segments

August 23rd, 2011 by igogo

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

Article source: http://laurentschneider.com/wordpress/2011/08/nothing-in-user_segments.html

Leave a Reply