Init parameterFiles & storage

DEFERRED_SEGMENT_CREATION

What it controls

Whether new tables and indexes get space only when the first row arrives.

Know this before you change it

Saves space for empty objects, but old exp and some tools miss tables with no segment.

Default: TRUE

Check and change it

1-- Current value on every instance2SELECT inst_id, name, value, isdefault, ismodified3FROM gv$parameter4WHERE name = 'deferred_segment_creation';5 6-- Value stored in the spfile7SELECT sid, value FROM v$spparameter WHERE name = 'deferred_segment_creation' AND isspecified = 'TRUE';8 9-- Dynamic: takes effect now and is kept after a restart10ALTER SYSTEM SET deferred_segment_creation = TRUE SCOPE = BOTH SID = '*';11 12-- Or for your own session only13ALTER SESSION SET deferred_segment_creation = TRUE;14 15-- Remove it from the spfile to go back to the default at the next restart16ALTER SYSTEM RESET deferred_segment_creation SCOPE = SPFILE SID = '*';

Open in the parameter referenceOracle's reference

More in Files & storage

• DB_CREATE_FILE_DESTDefault location for Oracle-managed datafiles and tempfiles.
• DB_CREATE_ONLINE_LOG_DEST_NLocations for Oracle-managed online redo logs and control files. Numbered 1 to 5; each one holds a member of every group.
• CONTROL_FILESThe control file copies the instance opens at startup.
• DB_FILESThe most datafiles the database can have open.
• FILESYSTEMIO_OPTIONSAsynchronous and direct I/O for datafiles on file systems.
• RECYCLEBINWhether dropped tables go to the recycle bin so they can be flashed back.
• MAX_STRING_SIZEEXTENDED raises VARCHAR2, NVARCHAR2 and RAW limits from 4000 to 32767 bytes.