DeferredSegmentCreation of Oracle11g

This topic describes the ldquo feature launched in Oracle11g, the feature of creating rdquo for latency segments, and the feature that needs to be noted when we use this feature.

This article describes the ldquo feature launched in Oracle 11g, the rdquo feature for creating delay segments, and the Deferred Segment Creation feature.

This article mainly introduces the Deferred Segment Creation feature introduced in Oracle 11g, as well as the issues that need attention when we use this feature.

1) Deferred Segment Creation

In Oracle, table space, segments, partitions, and blocks are four layers of the logical storage structure. A data table is generally composed of one or more Segment objects (partition tables) segments. That is to say, when a data table is created, Oracle will create a Data Segment object corresponding to it.

After a Segment is created, the Oracle space management mechanism allocates at least one extent as needed for initialization. The size of each extent needs to be configured according to different tablespaces. However, before the 11g period, when a data table is created, a space Segment is allocated. However, the Deferred Segment Creation feature is introduced in Oracle 11g.

First, create a data table justForTest to check whether the database assigns a segment to the table.

SQLjustForTest (test1 varchar2 (2); Table created

However, the corresponding segment object is not created, as shown below:

; SEGMENT_NAME bytesblocks extents ------------------------------------------

You can use dbms_metadata to extract the DDL statement of the data table, as shown in the following figure:

Create table "test". "JUSTFORTEST" ("TEST1" VARCHAR2 (2) segment creation deferredpctfree 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 nocompress logging tablespace "testNS ";

You can use DDL statements to obtain all statement parameters for creating a data table, including default parameters. Here, we found that there is no parameter "segment creation deferred" in the Past versions. This parameter indicates that the delay SEGMENT is generated during data table CREATION.

2) Segment Creation

So when will Oracle create objects? As long as a piece of data is inserted into the data table, whether or not it is commit, it will be accompanied by Oracle's creation of the data table segment.

SQLjustForTest); 1 row insertedSQL; SEGMENT_NAME bytesblocks extents ------------ ---------- JUSTFORTEST

Oracle launched the Deferred Segment Creation, which is just for the purpose of saving the object space. If an empty table has never been used, it is "uneconomical" to allocate space for creating a segment object. Therefore, we propose to postpone the creation of segments.

SQL> show parameter deferred_segment_creation; NAMETYPEVALUE --------------------------------- ---------------------------------- deferred_segment_creationbooleanTRUE

Of course, we can set the deferred_segment_creation parameter to false. In this way, the created tables will be allocated immediately, but the tables before the adjustment will not change. The adjustment method is as follows:

Alter system set deferred_segment_creation = false;

3) Oracle 11g solution for exporting empty tables and few tables

If deferred_segment_creation is true, the database empty table will not be allocated extent immediately, that is, it will not occupy the data space. When we use exp to export the database, these empty tables will not be exported. If you want to export empty tables together, refer to Oracle 11g's solution to export empty tables and small tables.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5