creation

by

deferred_segment_creation parameter

In oracle 11.2, this parameter deferred_segment_creation was introduced. Default value=TRUE
in many schema’s we have empty tables. the net affect is we have wasted storage, especially if the column type is lob.
When this parameter is set, the object will be created – and available for query, but until it has a row inserted no storage will be allocated.

  • not available in Oracle Standard Edition

Why is this important to us?
often we need to move data between databases. The common method is with export. If using a lower database client from 11gR2 eg 10.2, then any tables without storage allocated will not be exported.

An example is

SQL> SELECT COUNT(*) FROM AOADITEMLIST;
no ROWS

here we can see table AOADITEMLIST has now rows.

lets query to see if the object is known to us:

SQL> SELECT owner, object_name FROM all_objects WHERE object_name='AOADITEMLIST';
OWNER                          OBJECT_NAME
------------------------------ ------------------------------
ADBASE                         AOADITEMLIST

now lets see if any storage has been allocated

SQL> SELECT owner, segment_name FROM dba_segments WHERE segment_name='AOADITEMLIST';

no ROWS selected

lets check what the system parameter is set to:

SQL> sho parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            BOOLEAN     TRUE

Hmm, how to fix?
well why would you want to? essentially the only reason to “fix” is if you were exporting the data into a lower database version eg from 11g to 10g via exp, otherwise there is no need to. if you use expdp, then the rest of this section can be ignored as expdp will export all metadata by default regardless of storage allocation

so how?
well the easy way is to insert a row and then storage will be allocated automatically allocated. But that may not always be the solution, especially if there are many tables required.

So we can force storage to be allocated, without inserting a row,by:

SQL> ALTER TABLE ADBASE.AOADITEMLIST allocate extent;
TABLE altered.

now if we repeat the storage check above, we will see the storage has been allocated

SQL> SELECT owner, segment_name FROM dba_segments WHERE segment_name='AOADITEMLIST';
OWNER    SEGMENT_NAME
--------------------------------------------------------------------------------
ADBASE   AOADITEMLIST

now, if we have lots of tables, we can combine all the above sql to produce a script file to allocate storage for all tables in the current schema :

SQL > SET heading off
SQL > spool deallocate.SQL
sql> SELECT 'alter table '||  object_name || ' allocate extent;'  FROM user_objects WHERE object_name NOT IN (SELECT segment_name FROM user_segments) AND object_type='TABLE';
sql> spool allocate.SQL
sql> @deallocate.SQL

now, if you run a a check, you will see storage has been allocated to all the tables in the connected schema, ie no rows should be returned from this query:

sql> SELECT COUNT(*) FROM user_objects WHERE object_name NOT IN (SELECT segment_name FROM user_segments) AND object_type='TABLE')

How to avoid in future when loading new objects

  • This dynamic parameter is both database and session modifiable.

So if you have the ability to control the session, modify there. if not, then prior to loading you will need to amend the database

SQL> sho parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            BOOLEAN     TRUE

SQL> ALTER SYSTEM SET deferred_segment_creation=FALSE scope=MEMORY;

– this value will be retained until the next time the database is restarted and then it will revert back to the previous value.