Deferred Segment Creation in Oracle Database 11g
from http://www.oracle-base.com/articles/11g/segment-creation-on-demand-11gr2.php
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_NAMEparameter to target a specific table and its dependents.PARTITION_NAME- Used with theSCHEMA_NAMEandTABLE_NAMEparameters 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:
浙公网安备 33010602011771号