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@xxxxxxxxx> wrote:
Recently I have reason to know if tables/indexes/partitions were created[image: LORE DATA]
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
--
