Re: Deferred Segment Creation

Hi Franky,

Thanks for that idea. That is doable via SQL MINUS.

It is rather perplexing though that the information seems to be hidden - DBMS_METADATA knows how to get it.

I have not checked, that code may well be in a C lib.

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:24 AM Franky Weber Faust <weber08weber_at_gmail.com> wrote:

> Hi Jared,
>
> Not sure exactly, but what I know is that you won’t be able to find the
> data object in the dba_segments view if it was created using deferred
> segment creation, but you’ll see it in dba_objects. Also the data_object_id
> I think it could be null if segment was not created. I really don’t know
> about last assumption, but can be easily tested.
>
> Let’s see others reply.
>
> Regards,
>
> Franky
>
> On Thu, 25 Feb 2021 at 16:14 Jared Still <jkstill_at_gmail.com> wrote:
>
>> Recently I have reason to know if tables/indexes/partitions were created
>> with 'DEFERRED SEGMENT CREATION'
>>
>> As this is the default behavior unless deferred_segment_creation has been
>> set to FALSE, probably it is most of these objects.
>>
>> But then again, deferred_segment_creation may be altered per session.
>>
>> When I went looking for a column in some DBA/SYS view that might show
>> this, I could not find one.
>>
>> Perhaps it is very obvious, and I just can't see the forest for the trees.
>>
>> I know that this information is stored, for two reasons
>>
>> - the documentation says it is an option when tables/etc are created
>> - running dbms_metdata.ddl generates the DDL with that bit included
>>
>> It did occur to me that maybe the docs are somewhat inaccurate, and the
>> the current value of deferred_segment_creation may be what is used.
>>
>> Or I could test for it, creating tables with the different options,
>> running dbms_medata, etc.
>>
>> Or, I could just ask here.
>>
>> Which sometimes leads to fascinating conversations :)
>>
>> So, finally my formal question:
>>
>> Can someone tell me how to query the data dictionary to determine if an
>> object was created with deferred segment creation?
>>
>>
>>
>> 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
>>
>>
>> --
> [image: LORE DATA]
> Franky Weber Faust
>
> Consultor de Banco de Dados | LORE DATA
> Oracle ACE ♠️
> Fone: *47 99125 2714*
> Email: franky_at_loredata.com.br
> loredata.com.br
>

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