Oracle 11gr2 deferred segment creation and exp/IMP description

1. Deferred segment creation description

Since Oracle 11.2.0.1, Oracle provides a new space allocation method: when creating a non-partition table, this table segment is not created immediately, instead, the segment is created only when the first row of the record is inserted. This is different from the method used to create and allocate space for the segment. such a segment is also called a delay segment.

 

Support for partition tables and LOB fields has been added since version 11.2.0.2. And the function is enhanced:

(1) Both partition objects and non-partition objects are supported. For partition tables, the default extent size is 8 m when the new segments are created, instead of the previous 64 K.

(2) For systems upgraded from Versions earlier than 11.2.0.2, if empty tables exists, you can clear these segments through dbms_space_admin.drop_empty_segments.

(3) In 11.2.0.2, The truncate command has been enhanced. The "Drop all storage" option in the truncate table allows you to delete the segments as if you were deleting extents.

(4) dbms_space_admin.materialize_deferred_segments can be used to instantiate tables, partitions, and dependent objects for whichsegment creation was deferred

 

Advantages of deferred segment:

(1) Reduce the overhead of space: When you create hundreds of tables at a time, because many tables are not used in a short time, you can save a lot of disk overhead.

(2) Accelerate Application Deployment: because no segment is allocated, you only need to operate the data dictionary when creating a table without Designing space allocation. Therefore, the efficiency is naturally much higher.

The above content from: http://tomszrp.itpub.net/post/11835/510259

Note:

Thisnew feature in not applicable to sys and the system users as the segment to thetable is created along with the table creation.

-- This feature is not applicable to sys and system users.

The parameter used to control the deferred segment is deferred_segment_creation. The default value is true.

 

This parameter is described on the official website as follows:

Http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams073.htm#REFRN10307

 

Deferred_segment_creation

 

 

 

 

 

 

Property	Description
Parameter type	Boolean
Default Value	True
Modifiable	Alter session, alter System
Range of Values	True | false
Basic	No

 

Specify the semantics of deferred segment creation. ifset to true, then segments for tables and their dependent objects (lobs, indexes) will not be created until the first row is inserted into the table.

-- If deferred_segment_creation is set to true, the segments and related objects (indexes and lobs) of the table will be created after the insert operation.

Beforecreating a set of tables, if it is known that a significant number of them willnot be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.

-- This parameter is set to reduce disk space usage and creation time.

 

Ii. exp/IMP and deferred segment

There are two documents on MOs to illustrate this problem: [id1178343.1] and [id960216.1].

 

When deferred segment is enabled and empty tables are exported using exp, The EXP-00011: 'table name' does not exist is reported. That is, empty tables are not exported. This problem has been fixed in 11.2.0.2.

 

Expdp/impdp supports deferred segment. In 11gr2 and later versions, try to use expdp/impdp.

 

To assign a segment when creating a table, use the following SQL statement:

Createtable B _tab (ID number, text varchar2 (10) segment creation immediate;

 

You can also disable this function by modifying deferred_segment_creation to false. The modification only takes effect for the table created later. Existing tables are not affected.

 

For an existing empty table, use the following two commands to manually allocate the segment.

SQL> ALTER TABLE table_name move;
Table altered.
Or
SQL> ALTER TABLE table_nameallocate extent;
Table altered.

Or insert a data record directly into the empty table.

 

Iii. Test

Refer to Thomas Zhang's blog:

Http://tomszrp.itpub.net/post/11835/520574

 

SQL> select * from V $ version whererownum = 1;

Banner

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

Oracle Database 11g enterprise editionrelease 11.2.0.1.0-Production

 

SQL> show parameterdeferred_segment_creation

Name type value

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

Deferred_segment_creation Boolean true

 

SQL> Create Table T1 as select * fromdba_users;

Table created.

 

SQL> Create Table T2 as select * From dba_userswhere 1 = 2;

Table created.

 

SQL> Create Table T3 as select * fromdba_users where 1 = 2;

Table created.

 

SQL> select table_name from tabs where table_namein ('T1 ', 't2', 't3 ');

Table_name

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

T1

T2

T3

-- Tabs is a synonym for user_tables.

 

SQL> select segment_name fromuser_segments where segment_name in ('t1', 't2', 't3 ');

Segment_name

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

T1

-- Only table T1 is allocated with a segment.

 

SQL> select table_name

2 from tabs t

3 where not exists (select segment_name from user_segments swhere S. segment_name = T. table_name );

 

Table_name

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

T3

T2

-- View the table with no segment allocated

 

-- Use exp to export three tables

C: \ Users \ administrator. David Dai> expicd/ICD tables = (T1, T2, T3) file = 'd: \ Temp. dmp ';

Export: Release 11.2.0.1.0-production onwed Jul 13 17:13:22 2011

 

Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All rights reserved.

 

Connected to: Oracle Database 11 genterprise Edition Release 11.2.0.1.0-Production

With the partitioning, OLAP, data miningand real application testing options

Export done in zhs16gbk Character Set andal16utf16 nchar Character Set

 

About to export specified tables viaconventional path...

.. Exporting table T1 35 rows exported

EXP-00011: ICD. T2 doesnot exist.

EXP-00011: ICD. T3 doesnot exist.

Export terminated successfully withwarnings.

-- The system prompts that table T2 and table T3 do not exist.

 

 

-- Manually allocate a segment. There are many methods here, insert a piece of data, or use the alter command to operate.

-- Method 1 uses allocateextent. Here we only operate on T2.

SQL> select 'alter table' | table_name | 'allocate extent (size 64 K );'

2 from tabs t

3 where not exists (selectsegment_name from user_segments s where S. segment_name = T. table_name );

 

'Altertable' | table_name | 'allocateextent (size64k );'

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

Alter table T3 allocate extent (size 64 K );

Alter table T2 allocate extent (size 64 K );

 

SQL> ALTER TABLE T2 allocate extent (size64k );

Table altered.

 

-- Method 2: Use altertable move

SQL> ALTER TABLE T3 move;

Table altered.

 

-- Confirm the segment allocation

SQL> select segment_name fromuser_segments where segment_name in ('t1', 't2', 't3 ');

 

Segment_name

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

T1

T2

T3

 

-- Exp again

C: \ Users \ administrator. David Dai> expicd/ICD tables = (T1, T2, T3) file = 'd: \ temp1.dmp ';

 

Export: Release 11.2.0.1.0-production onwed Jul 13 17:18:29 2011

 

Copyright (c) 1982,200 9, Oracle and/or itsaffiliates. All rights reserved.

 

Connected to: Oracle Database 11 genterprise Edition Release 11.2.0.1.0-Production

With the partitioning, OLAP, data miningand real application testing options

Export done in zhs16gbk Character Set andal16utf16 nchar Character Set

 

About to export specified tables via conventionalpath...

.. Exporting table T1 35 rows exported

.. Exporting table T2 0 rows exported

.. Exporting table T3 0 rows exported

Export terminated successfully withoutwarnings.

 

In 11.2.0.2, deferred segment can be allocated and dropped using the added materialize_deferred_segments and drop_empty_segments. Because there is no 11.2.0.2 environment at hand, you can refer to Thomas Zhang's blog for this part of the test.

 

The related syntax is as follows:

-- Create a table

SQL> Create Table T (

2 ID number,

3 C clob,

4 constraint t_pk primary key (ID) using index local

5)

6 segment creation deferred

7 partition by hash (ID) Partitions 4;

 

-- View segment

SQL> select segment_name, segment_type, bytes, extents

2 from user_segments

3 where segment_name in ('T', 't_ pK ')

4 or segment_name in (selectsegment_name

5 from user_lobs

6 wheretable_name = 'T ')

7 order by 1, 2;

 

-- Use materialize_deferred_segments to instantiate the segment of the partition table.

SQL> begin

2 SYS. dbms_space_admin.materialize_deferred_segments (

3 Schema_name => 'Study ',

4 table_name => 'T'

5 );

6 end;

7/

 

-- Use drop_empty_segments to delete an empty segment.

SQL> begin

2 dbms_space_admin.drop_empty_segments (

3 Schema_name => 'Study ',

4 table_name => 'T'

5 );

6 end;

7/

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.