What’s deferred segment creation? It is a feature that saves lots of time by releases and lots of space if you have a -legacy- application with 10’000 tables and most of them are empty.
When was it introduced ? Partly in 22.214.171.124 and partly in 126.96.36.199, depending on the object type.
What’s the opposite ? immediate segment creation
What’s the default ? deferred segment creation
How does it work ?
create table t1(x number) segment creation immediate;
create table t1(x number) segment creation deferred;
Where it the doc? start with Understand Deferred Segment Creation
Ok, now my 2 cents on this feature. It is a major change of the most basic elements of Oracle, the segment. This behavior will keep its bunch of surprises on your dba scripts.
1) you will not see the segment in dba_segments
2) if you drop the tablespace the tablespace containing the object without the INCLUDING CONTENTS, the drop tablespace will succeed and the table will remain
SQL> create tablespace ts datafile '/u02/oradata/@/ts.dbf' size 1m; Tablespace created. SQL> create table t(x number) tablespace ts; Table created. SQL> drop tablespace ts; Tablespace dropped. SQL> select * from t; select * from t * ERROR at line 1: ORA-00959: tablespace 'TS' does not exist SQL> drop table t; drop table t * ERROR at line 1: ORA-00959: tablespace 'TS' does not exist
Neither SELECT nor DROP is possible at that stage
To quickly identify those almost-nonexistent tablespaces you may use this query
SELECT TABLESPACE_NAME FROM ALL_CLUSTERS UNION SELECT TABLESPACE_NAME FROM ALL_INDEXES UNION SELECT TABLESPACE_NAME FROM ALL_IND_PARTITIONS UNION SELECT TABLESPACE_NAME FROM ALL_IND_SUBPARTITIONS UNION SELECT TABLESPACE_NAME FROM ALL_LOBS UNION SELECT TABLESPACE_NAME FROM ALL_LOB_PARTITIONS UNION SELECT TABLESPACE_NAME FROM ALL_LOB_SUBPARTITIONS UNION SELECT TABLESPACE_NAME FROM ALL_TABLES UNION SELECT TABLESPACE_NAME FROM ALL_TAB_PARTITIONS UNION SELECT TABLESPACE_NAME FROM ALL_TAB_SUBPARTITIONS MINUS select tablespace_name from dba_tablespaces ; TABLESPACE_NAME ------------------------------ TS
then you can recreate it and -if wished- drop it with contents
SQL> create tablespace ts datafile '/u02/oradata/@/ts.dbf' size 1m reuse; Tablespace created. SQL> drop tablespace ts including contents and datafiles; Tablespace dropped. SQL> select * from t; select * from t * ERROR at line 1: ORA-00942: table or view does not exist
The table is gone for real.
If you based some scripts on dba_segments to list the content of the tablespaces, you probably should check the assigned tablespace in the tables,indexes,lobs and (sub)partitions DBA_VIEWS too.
I met this feature while using transportable tablespace. Transportable table will transport the object with no segment that belongs to the tablespace.
There is a bunch of published bugs on Metalink regarding deferred segment creation. An easy workaround is to not use the feature by setting the initialization parameter DEFERRED_SEGMENT_CREATION to false. This of course affects only new objects.
I am always very cautious about those major changes affecting the dba scripts on the dictionary. While selecting from the base dictionary tables (TAB$, COL$, …) is never recommended, selecting from the USER_ and DBA_ views is supposed to be backward compatible, but the dba scripts that used to work in previous release may break here… This is obviously the price to pay to get new features, right?