8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Segment Creation on Demand (Deferred Segment Creation) in Oracle Database 11g Release 2
11.2.0.1
Basic Usage
Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving.
The functionality can be controlled by the DEFERRED_SEGMENT_CREATION
initialization parameter, which is set to TRUE by default. It can be turned off using the following command.
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=[TRUE | FALSE];
The CREATE TABLE
statement now supports the following segment creation clause.
SEGMENT CREATION { IMMEDIATE | DEFERRED }
The default action is DEFERRED
, as shown below.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) LOB(clob_data) STORE AS SECUREFILE tab1_clob_data; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; no rows selected SQL> INSERT INTO tab1 VALUES (1, 'CLOB data for 1'); COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ TAB1 TABLE SYS_IL0000085548C00002$$ LOBINDEX TAB1_PK INDEX TAB1_CLOB_DATA LOBSEGMENT 4 rows selected. SQL>
The DEFERRED
clause can also be used explicitly.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) SEGMENT CREATION DEFERRED LOB(clob_data) STORE AS SECUREFILE tab1_clob_data; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; no rows selected SQL>
The default behavior is altered by using the IMMEDIATE
clause.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) SEGMENT CREATION IMMEDIATE LOB(clob_data) STORE AS SECUREFILE tab1_clob_data; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ TAB1 TABLE SYS_IL0000085544C00002$$ LOBINDEX TAB1_PK INDEX TAB1_CLOB_DATA LOBSEGMENT 4 rows selected. SQL>
Delayed Quota Errors
A rather annoying issue that results from deferred segment creation is that of delayed quota errors. Since the table creation doesn't result in segment creation, tables can be defined against any tablespace regardless of quotas. A quota error is only issued when an insert is attempted against the object.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER ) TABLESPACE SYSTEM; Table created. SQL> INSERT INTO tab1 VALUES (1); INSERT INTO tab1 VALUES (1) * ERROR at line 1: ORA-01950: no privileges on tablespace 'SYSTEM' SQL>
Export of Empty Objects Issues
Another annoying result of deferred segment creation is the un-patched "exp" utility doesn't support it properly. Tables with no segments don't get exported as expected. One solution is to turn off the functionality before creating any objects using the following command.
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
Alternatively, you can force the allocation of extents on any table with no rows using the following command.
ALTER TABLE tablename ALLOCATE EXTENT;
11.2.0.2
TRUNCATE TABLE .. DROP ALL STORAGE
The 11.2.0.2 patch introduces an addition to the TRUNCATE
command. The default action is unchanged, but the DROP ALL STORAGE
clause causes all segments associated with the table to be dropped.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE tab1_clob_data; INSERT INTO tab1 VALUES (1, 'Some CLOB data.'); COMMIT; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ TAB1 TABLE SYS_IL0000085289C00002$$ LOBINDEX TAB1_CLOB_DATA LOBSEGMENT 3 rows selected. SQL> TRUNCATE TABLE tab1; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ TAB1 TABLE SYS_IL0000085289C00002$$ LOBINDEX TAB1_CLOB_DATA LOBSEGMENT 3 rows selected. SQL> TRUNCATE TABLE tab1 DROP ALL STORAGE; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; no rows selected SQL>
DBMS_SPACE_ADMIN
The DBMS_SPACE_ADMIN
package includes two new procedures to help manage segments associated with empty tables. The MATERIALIZE_DEFERRED_SEGMENTS
procedure forces segment creation for objects whose segment creation has been deferred.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE tab1_clob_data; COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; no rows selected SQL> CONN / AS SYSDBA BEGIN DBMS_SPACE_ADMIN.materialize_deferred_segments ( schema_name => 'TEST', table_name => 'TAB1', partition_name => NULL); END; / CONN test/test COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------ TAB1 TABLE SYS_IL0000085625C00002$$ LOBINDEX TAB1_CLOB_DATA LOBSEGMENT 3 rows selected. SQL>
The DROP_EMPTY_SEGMENTS
procedure drops the segments for any tables with now no rows.
CONN / AS SYSDBA BEGIN DBMS_SPACE_ADMIN.drop_empty_segments ( schema_name => 'TEST', table_name => 'TAB1', partition_name => NULL); END; / CONN test/test COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type FROM user_segments; no rows selected SQL>
The parameters are used as follows.
SCHEMA_NAME
- Checks all tables and their dependent objects in the specified schema. If the default NULL value us used, tables in all schema are checked.TABLE_NAME
- Used with theSCHEMA_NAME
parameter to target a specific table and its dependents.PARTITION_NAME
- Used with theSCHEMA_NAME
andTABLE_NAME
parameters to target a specific partition and its dependents.
Partitioned Table Support
Deferred segment creation now supports partitioned tables, as shown by the following example.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, part_key DATE, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) LOB(clob_data) STORE AS SECUREFILE tab1_clob_data PARTITION BY RANGE (part_key) ( PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users, PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users ); COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type, partition_name FROM user_segments; no rows selected SQL>
The default behavior can be overridden using the segment creation clause.
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, part_key DATE, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) SEGMENT CREATION IMMEDIATE LOB(clob_data) STORE AS SECUREFILE tab1_clob_data PARTITION BY RANGE (part_key) ( PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users, PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users ); COLUMN segment_name FORMAT A30 SELECT segment_name, segment_type, partition_name FROM user_segments; SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME ------------------------------ ------------------ ------------------------------ TAB1 TABLE PARTITION PART_1 TAB1 TABLE PARTITION PART_2 TAB1_PK INDEX SYS_IL0000085525C00003$$ INDEX PARTITION SYS_IL_P91 SYS_IL0000085525C00003$$ INDEX PARTITION SYS_IL_P92 TAB1_CLOB_DATA LOB PARTITION SYS_LOB_P89 TAB1_CLOB_DATA LOB PARTITION SYS_LOB_P90 7 rows selected. SQL>
Default Size of First Extent for Partitioned Tables
The default size of the first extent for partitioned tables has been increased from 64K to 8Mb. Although this isn't really a feature of segment creation on demand, it does mean the relative space saving it gives has increased in this version, because the default space wasted has increased.
DROP TABLE tab1; CREATE TABLE tab1 ( id NUMBER, part_key DATE, clob_data CLOB, CONSTRAINT tab1_pk PRIMARY KEY (id) ) SEGMENT CREATION IMMEDIATE LOB(clob_data) STORE AS SECUREFILE tab1_clob_data PARTITION BY RANGE (part_key) ( PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users, PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users ); COLUMN segment_name FORMAT A30 SELECT segment_name, partition_name, bytes/1024/1024 AS MB FROM user_segments; SEGMENT_NAME PARTITION_NAME MB ------------------------------ ------------------------------ ---------- TAB1 PART_1 8 TAB1 PART_2 8 TAB1_PK .0625 SYS_IL0000085631C00003$$ SYS_IL_P111 .0625 SYS_IL0000085631C00003$$ SYS_IL_P112 .0625 TAB1_CLOB_DATA SYS_LOB_P109 8 TAB1_CLOB_DATA SYS_LOB_P110 8 7 rows selected. SQL>
Export of Empty Objects Fixed
The issues related to export of empty objects mentioned earlier are fixed in 11.2.0.2.
For more information see:
- CREATE TABLE
- DBMS_SPACE_ADMIN
- TRUNCATE TABLE
- Default Size of First Extent of Any New Segment for a Partitioned Table Has Changed
Hope this helps. Regards Tim...