When generating DDL script for a table, I can choose “Don’t specify, Deferred, or Immediate” for the segment creation option
I don’t see an option to “Use the current definition”
Am I not looking in the right place or does Toad not support this?
I know I can use dbms_metadata to derive it myself and that’s what I’m doing as a workaround.
The main problem is there is nothing (that I can see) that tells me the script is incomplete. I have to already know the answer or as a safer bet, never use the feature and always derive it myself.
If Oracle stores this original value of this parameter in the data dictionary, they don’t expose it in DBA_SEGMENTS or anywhere else that I could find it, so the information just isn’t available to Toad.
The closest that I can come is that if there is no row for the segment in dba_segments, then I can conclude that it was created with the DEFERRED option and was never created…but if the segment exists, then I have no way of knowing if it was created with DEFERRED and then later created when a row was inserted, or if it was created with IMMEDIATE.
In fact, from a quick test of the two above scenarios, I see that Oracle doesn’t know either. I just created a table with DEFERRED…dbms_metadata.get_ddl shows DEFERRED. Then I inserted a row, and dbms_metadata.get_ddl shows IMMEDIATE.
If you know of a way to tell for sure how it was created, please let me know.
dba_tables and dba_indexes have a “SEGMENT_CREATED” column you can use instead of making additional call to dba_segments.
I agree, if an object was originally created deferred and then later has a segement created for it, then there is no way to tell what the initial setting was.
Similarly, if an object has segments (regardless of deferred or immediate origin) and theyn you truncate with “drop all storage” then there is no record of what the original status was. In this case dbms_metadata will again show DEFERRED.
To implement, maybe alter the drop down to have 5 options something like these…
Do not specify
Immediate
Immediate only if segment exists
Deferred
Deferred only if no segment exists
or maybe just 4 options
Do not specify
Immediate
Deferred
Deferred/Immediate based on segment_created