watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


Q           The title is as follows :

stay Oracle in , What is delay segment creation (Deferred Segment Creation)?


     
A           The answer is as follows :          


  stay Oracle 11.2 in , When creating an empty table or partition , To speed up creation ,Oracle The initial segment and space are not allocated immediately , The actual table segment (Table Segement) Is delayed until the first row of data is inserted to create . The delay segment creation feature is implemented through DEFERRED_SEGMENT_CREATION Parameter control , The default is TRUE, Indicates that the function is turned on . Delay segment creation can save space , Speed up the initialization process , It's an optimization for performance and resources . You can change the parameters DEFERRED_SEGMENT_CREATION To turn off this feature :

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FLASE;

This parameter is dynamic , No need to restart the database . It can be done by SQL Statement to find all empty tables ( Tables with no zones assigned ):

SELECT SEGMENT_CREATED,TABLE_NAME FROM USER_TABLES WHERE SEGMENT_CREATED = 'NO';

The constraints of delay segment creation are :

① Table types that are not supported for delay segment creation include : Index organization table (Index-Organized Tables)、 Cluster table (Clustered Tables)、 Global temporary table (Global Temporary Tables)、 Session level temporary table (Session-Specific Temporary Tables)、 Internal table (Internal Tables)、Typed surface (Typed Tables)、AQ surface (AQ Tables) And external tables (External Tables).

② user SYS、SYSTEM、PUBLIC、OUTLN and XDB The following table does not support delay segment creation .

③ Delay segment creation does not support bitmap join index (Bitmap Join Indexes) And domain index (Domain Indexes).

④ Delay segment creation does not support dictionary management of table spaces (Dictionary-Managed Tablespace,DMT).

⑤ Delay segment creation is not supported SYSTEM Table space .

⑥ Delay segments are created from Oracle 11.2.0.2 Partition and sub partition are supported at the beginning of the version .

When creating tables , When SEGEMENT CREATION by IMMEDIATE when ,Oracle Segments are created for the table (SEGMENT), When SEGEMENT CREATION by DEFERRED when ,Oracle Segments are not created for empty tables , As shown below :

SQL> CREATE TABLE T_TEST_2(ID NUMBER,NAME VARCHAR2(10)) SEGMENT CREATION IMMEDIATE;

Table created .

SQL> CREATE TABLE T_TEST_3(ID NUMBER,NAME VARCHAR2(10)) SEGMENT CREATION DEFERRED;

Table created .

SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';

SEGMENT_NAME

-------------

T_TEST_2

SQL> INSERT INTO T_TEST_1 VALUES(1,'LHRDB');

Created 1 That's ok .

SQL> SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'T_TEST%';

SEGMENT_NAME

------------

T_TEST_1

T_TEST_2

You can see , stay SEGEMENT CREATION by IMMEDIATE Under the circumstances ,Oracle by T_TEST_2 Established a segment , stay SEGEMENT CREATION by DEFERRED Under the circumstances ,Oracle There is no table for T_TEST_3 Create segment , When inserting information into an empty table without an assigned segment ,Oracle Segments are automatically created for empty tables . It should be noted that , No zones have been assigned (EXTENT) Table of , stay DBA_SEGMENTS You can't find it in the view . If it is used without DROP ALL STROAGE Of TRUNCATE sentence , Then the allocated segment is only reduced to the original allocated size , It's not completely recycled , As shown below :

SQL> TRUNCATE TABLE TEST1;          ------ Use... Without clauses TRUNCATE

Table truncated.

SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS;       ----- Just delete to the smallest area

SEGMENT_NAME                        BYTES

------------------------------ ----------

TEST1                               65536

SQL> TRUNCATE TABLE TEST1 DROP ALL STORAGE;         ----- Use 11g A new clause in DROP ALL STROAGE

Table truncated.

SQL> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS;      ----- All segments are recycled

no rows selected

What we need to pay attention to , This new feature brings a problem : In the use of exp/imp When exporting or importing , Will not contain these empty tables ( Data pump expdp There is no such problem ,expdp You can export empty tables ), therefore , When migrating data, some empty tables may be missed . The solutions to this problem are :

① The most primitive and stupid way ( Not recommended ):INSERT a line , Again ROLLBACK Or delete it to produce SEGMENT 了 . The method is to insert data into an empty table , And then delete , Will produce SEGMENT, At this point, the empty table can be exported .

② Set up DEFERRED_SEGMENT_CREATION Parameters . Set up DEFERRED_SEGMENT_CREATION Parameter is FALSE To disable “ Delay segment creation ”, Whether it's an empty table or a non empty table , All will be assigned SEGMENT. It should be noted that , After this value is set, it will only affect the newly added tables , For the empty table created before ( What already exists ) It doesn't work , Still can't export .

③ Use ALLOCATE EXTENT. Use ALLOCATE EXTENT Each table in the database can be assigned EXTENT. Batch script generation :

SELECT 'ALTER TABLE '||D.OWNER||'.'||D.TABLE_NAME||' ALLOCATE EXTENT;' EXEC_SQL FROM DBA_TABLES D WHERE D.SEGMENT_CREATED='NO' AND D.OWNER IN ('LHR','ABC'); -- Pay attention to modify the user name

Perform the above SQL After the script is generated, segments can be allocated for each empty table , And then execute exp Command is enough .

   

    

To sum up , When the database server version is 11.2.0.1 Or use exp 11.2.0.1 When the tool exports , We need to pay attention to the problem of empty table . Due to the characteristics of delay segment creation , It will cause the empty table not to be exported normally . from Oracle 11.2.0.2 Start , The problem no longer exists .

In the database server version Oracle 11.2.0.1 in , When exp( No matter what the version is ) When exporting data , You need to determine whether there are empty tables in the database first . When the database contains empty tables , You need to set the parameters first DEFERRED_SEGMENT_CREATION by FALSE, And then use ALLOCATE EXTENT Assign segments to each empty table , Finally, export the required data . in addition , For partitioned tables , Even if no segments are created ( Whether it's the whole partition or individual sub partition, no segments are created , have access to DBA_TAB_PARTITIONS.SEGMENT_CREATED To find out if a segment has been created ), There is no problem of losing partition tables or sub partitions .

The following points need to be noted :

① When exp The version is 11.2.0.1 when , No matter what the version of the database server is , Can't export empty table . therefore , It is not recommended to use version 11.2.0.1 Of exp Tools export data .

② When database or exp The version is 11.2.0.1 when , be based on SCHEMA Level or FULL No error will be reported when exporting data . however , If you export based on table level, an error will be reported . If export based on table schema , When the database server version is 11.2.0.1 when , It will report an error similar to :“EXP-00011: LHR.TESTDSC does not exist”. When exp The version is 11.2.0.1 when , It will report an error similar to :“EXP-00003: Segment not found (0,0) The storage definition of ”.