Serial isolation in Oracle support for deferred and interval partitions

Two days ago, it was mentioned that the serializable isolation does not support the ability to delay segment creation and interval partitioning, and it is thought that because both of these methods are recursively generating DDL operations in DML, they can cause problems in serial isolation. However, when validating this point of view, unexpectedly found that the phenomenon is inconsistent with the document description.

The description on the document is:

Serializable transactions do is work with deferred segment creation or interval partitioning. Trying to insert data in an empty table with no segment created, or into a partition of a interval partitioned table th At does isn't yet have a segment, causes an error.

And the results of the actual operation:

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_deter

2 (ID number)

3 SEGMENT creation DEFERRED;

Table has been created.

sql> CREATE TABLE T_interval

2 (ID number)

3 PARTITION by RANGE (ID)

4 INTERVAL (1)

5 (PARTITION P1 VALUES less THAN (1));

Table has been created.

Sql> SET TRANSACTION Isolation level SERIALIZABLE;

Transaction processing set.

Sql> INSERT into T_deter VALUES (1);

1 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> SET TRANSACTION Isolation level SERIALIZABLE;

Transaction processing set.

Sql> INSERT into T_interval

2 SELECT rownum

3 from TAB;

12 lines have been created.

Sql> COMMIT;

Submit completed.

I don't know if this is a bad description of the document or if there is a problem with the Oracle implementation, but for the user, of course, the less restrictive the better.

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Import Forum Reference link collection share to friends recommendation to Circle Management report

TAG:

Reference Delete yangtingkun/2011-06-08 08:45:21

To Redhouser:

It's what you say. You can explain the creation of a delay segment and not the interval partition.

The detailed testing process is as follows:

Sql> Show USER

USER is "TEST"

Sql> SELECT default_tablespace from User_users;

Default_tablespace

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

USERS

Sql> CREATE TABLE T_defer (ID number)

2 SEGMENT creation DEFERRED;

Table has been created.

Sql> SELECT Segment_name

2 from User_segments

3 WHERE segment_name = ' t_defer ';

No rows selected

Sql> SET TRANSACTION Isolation level SERIALIZABLE;

Transaction processing set.

Sql> INSERT into T_defer

2 SELECT rownum

3 from TAB;

7 lines have been created.

Sql> SELECT Segment_name

2 from User_segments

3 WHERE segment_name = ' t_defer ';

No rows selected

Sql> SELECT * from T_defer;

Id

----------

1

2

3

4

5

6

7

7 rows have been selected.

Sql> COMMIT;

Submit completed.

Sql> SELECT Segment_name

2 from User_segments

3 WHERE segment_name = ' t_defer ';

Segment_name

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

T_defer

Author: 51cto Blog Oracle Little Bastard