Re: Deferred Segment Creation

 Jared,

To find whether a segment was created or not, you can use DBA_TABLES.SEGMENT_CREATED
(dba_tab_partitions/dba_tab_subpartitions/indexes/etc.). I don't think that the data dictionary has enough information to differentiate between "was declared as immediate" vs "was declared as deferred, but the segment was created later on".

SQL> create table t(n int);

Table created.

SQL>
SQL> select segment_created
  2 from tabs
  3 where table_name='T';

*SEG---NO* SQL>
SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

  CREATE TABLE "TC"."T"
   ( "N" NUMBER(*,0)
* ) SEGMENT CREATION DEFERRED*
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
  TABLESPACE "USERS" SQL>
SQL> insert into t values (0);

1 row created.

SQL>
SQL> select segment_created
  2 from tabs
  3 where table_name='T';

*SEG---YES* SQL>
SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

  CREATE TABLE "TC"."T"
   ( "N" NUMBER(*,0)
   ) *SEGMENT CREATION IMMEDIATE*
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS" Best regards,
Mikhail Velikikh

On Thu, 25 Feb 2021 at 19:54, Jared Still <jkstill_at_gmail.com> wrote:

> Perfect, thank you Jonathan.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Principal Consultant at Pythian
> Oracle ACE Alumni
> Pythian Blog http://www.pythian.com/blog/author/still/
> Github: https://github.com/jkstill
>
>
>
>
> On Thu, Feb 25, 2021 at 11:45 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Jared,
>>
>> If you check the view dba_tables you'll see that it joins tab$ to
>> deferred_stg$ on obj#, and then there are lots of columns which use
>> when (bitand(t.property, 17179869184) = 17179869184)
>> in a case statement to decide whether to use seg$ or deferred_stg$ to
>> report segment-related information (like INITIAL, NEXT, etc.)
>>
>> So if you can get object_id from dba_objects for object_type = 'TABLE'
>> (et. al.) then you could join that to deferred_stg$ on obj# to see if a row
>> exists there - and if it does then the table was declared as deferred.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l