Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you about the creation of the delay section of ORACLE 11g, which is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

There are empty tables in many databases, and more empty tables will take up a lot of disk space. ORACLE introduced a new feature of creating delay segment in 11gR2 version, which means that when creating a new empty table, ORACLE will not allocate a segment (SEGMENTS) to the empty table by default, that is, it will not allocate space for the empty table, thus avoiding the space occupied by the empty table as follows:

SQL > SELECT * FROM V$VERSION

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for 32-bit Windows: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

SQL > CREATE TABLE T_TEST_1 (ID NUMBER,NAME VARCHAR2 (10))

The table has been created.

SQL > SELECT SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='T_TEST_1'

No rows selected

By default, ORACLE does not allocate space for an empty table (T_TEST_1). People who have viewed the official ORACLE11gR2 documentation about CREATE TABLE syntax may see SEGEMENT CREATION information, as follows:

When SEGEMENT CREATION is IMMEDIATE, ORACLE will create a SEGMENTS for the table when creating the table. When SEGEMENT CREATION is DEFERRED, ORACLE will not create a segment for the empty table. The effects of these two cases are demonstrated below.

SQL > CREATE TABLE T_TEST_2 (ID NUMBER,NAME VARCHAR2 (10))

2 SEGMENT CREATION IMMEDIATE

The table has been created.

SQL > CREATE TABLE T_TEST_3 (ID NUMBER,NAME VARCHAR2 (10))

2 SEGMENT CREATION DEFERRED

The table has been created.

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

SEGMENT_NAME

-

T_TEST_2

You can see that when SEGEMENT CREATION is IMMEDIATE, ORACLE establishes a segment for T_TEST_2, and when SEGEMENT CREATION is DEFERRED, ORACLE does not establish a segment for the table T_TEST_3. When inserting information into an empty table with no assigned segment, ORACLE automatically creates a segment for the empty table.

SQL > INSERT INTO T_TEST_1 VALUES (1)

1 line has been created.

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

SEGMENT_NAME

-

T_TEST_1

T_TEST_2

You can also use ALLOCATE EXTENT to create segment information for empty tables.

SQL > ALTER TABLE T_TEST_3 ALLOCATE EXTENT

The table has changed.

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

SEGMENT_NAME

-

T_TEST_1

T_TEST_2

T_TEST_3

Although the creation of delay segments avoids the problem of empty tables taking up space, it also brings a little trouble for DBA, that is, when EXP exports data, although the information of empty tables also exists in the database dictionary, ORACLE will not export empty tables with unassigned segments, so you will encounter minor problems when using EXP for data migration.

SQL > CREATE USER dbdream IDENTIFIED BY dbdream DEFAULT TABLESPACE USERS

The user has been created.

SQL > GRANT CONNECT,RESOURCE TO DBDREAM

Authorization successful.

SQL > CREATE TABLE T_TEST_1 (ID NUMBER,NAME VARCHAR2 (10))

The table has been created.

SQL > CREATE TABLE T_TEST_2 (ID NUMBER,NAME VARCHAR2 (10))

2 SEGMENT CREATION IMMEDIATE

The table has been created.

D:\ > exp dbdream/dbdream file=d:\ dbdream.dmp

Export: Release 11.2.0.1.0-Production on Monday February 13 11:35:22 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Exported ZHS16GBK character set and AL16UTF16 NCHAR character set

The specified user is about to be exported.

...

. . Exporting table T_TEST_2 exported 0 rows

...

The export was terminated successfully without warning.

EXP can only export tables with assigned segments, but EXP is powerless to export empty tables with unassigned segments. To export empty tables with unassigned segments, you need to use a data pump (EXPDP), and use EXPDP to export empty tables with unassigned segments.

SQL > CREATE DIRECTORY D_TEST AS'd:\ titled test'

The directory has been created.

SQL > GRANT READ,WRITE ON DIRECTORY D_TEST TO DBDREAM

Authorization successful.

D:\ > expdp dbdream/dbdream directory=D_TEST dumpfile=dbdream.dmp

Export: Release 11.2.0.1.0-Production on Monday February 13 11:50:00 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Estimation is being made using the BLOCKS method.

...

. . The line "DES". "T_TEST_1" 0 KB 0 is exported

. . The line "DES". "T_TEST_2" 0 KB 0 is exported

...

Assignment "DES". "SYS_EXPORT_SCHEMA_01" completed successfully at 11:50:47

If you have to use EXP for migration, and all empty tables need to be migrated, you need to use the method mentioned above that is good for ALLOCATE EXTENT to create segments. Before doing the EXP operation, use the ALLOCATE EXTENT method to assign segment information to the empty table.

SQL > DECLARE

2 V_COUNT NUMBER

3 BEGIN

4 FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP

5 EXECUTE IMMEDIATE 'SELECT COUNT (*) FROM' | | I.TABLE_NAME INTO V_COUNT

6 IF V_COUNT = 0 THEN

7 EXECUTE IMMEDIATE 'ALTER TABLE' | | I.TABLE_NAME | | 'ALLOCATE EXTENT'

8 END IF

9 END LOOP

10 END

11 /

The PL/SQL process completed successfully.

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

SEGMENT_NAME

-

T_TEST_1

T_TEST_2

Then export the data using EXP so that the empty table can be exported.

D:\ > exp dbdream/dbdream file=d:\ dbdream.dmp

Export: Release 11.2.0.1.0-Production on Monday February 13 11:58:03 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Exported ZHS16GBK character set and AL16UTF16 NCHAR character set

The specified user is about to be exported.

...

. . Exporting table T_TEST exported 0 rows

. . Exporting table T_TEST_2 exported 0 rows

...

The export was terminated successfully without warning.

This is how the new feature delay section of ORACLE 11g is created. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.