首页 » ORACLE » oracle initrans and maxtrans

oracle initrans and maxtrans

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> create table testinit(id number)
2  initrans 1;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables  where table_name='TESTINIT';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTINIT                                1        255

SQL> drop table testinit;

表已删除。

SQL> create table testinit(id number)  initrans 3;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables
2  where table_name='TESTINIT';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTINIT                                3        255

SQL> create table testmax(id number)   maxtrans 100;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables    where table_name='TESTMAX';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTMAX                                 1        100

SQL> drop table testmax ;

表已删除。

SQL> create table testmax(id number)   maxtrans 256;
maxtrans 256
*
第 2 行出现错误:
ORA-02209: 无效的 MAXTRANS 选项值

####################################换11G#########

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table testinit(id int)
2  initrans 1;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables  where table_name='TESTINIT';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTINIT                                1        255

SQL> drop table testinit purge;

表已删除。

SQL> create table testinit(id number)  initrans 3;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables  where table_name='TESTINIT';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTINIT                                3        255

SQL> create table testmax(id number)   maxtrans 100;

表已创建。

SQL> select table_name,ini_trans,max_trans from user_tables  where table_name='TESTINIT';

TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
TESTINIT                                3        255

SQL> drop table testmax purge;

表已删除。

SQL> create table testmax(id number)   maxtrans 256;
create table testmax(id number)   maxtrans 256
*
第 1 行出现错误:
ORA-02209: 无效的 MAXTRANS 选项值

note:官方

In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated.

Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
The database ignores MAXTRANS when specified by users only for new objects created when the COMPATIBLE initialization parameter is set to 10.0 or greater.

也就是说从Oracle10g开始,对于单个数据块,Oracle缺省最大支持255个并发,MAXTRANS参数被废弃,无论你是否指定,11G中initrans 默认为3,9i是默认为1,这也是oracle 一直在需要一个平衡点。

打赏

目前这篇文章有15条评论(Rss)评论关闭。

  1. Namari | #1
    2011-06-23 at 06:59

    Wow! That’s a really neat aswenr!

    • Berlynn | #2
      2011-09-02 at 23:52

      Yo, good looikn out! Gonna make it work now.

      • Kalyn | #3
        2011-11-11 at 13:09

        Wow I must confess you make some very trenchant ptoins.

    • Janine | #4
      2011-09-03 at 04:45

      Hecvkua good job. I sure appreciate it.

      • Kaden | #5
        2011-11-11 at 22:13

        I told my kids we’d play after I found what I ndeeed. Damnit.

  2. Susannah | #6
    2011-06-22 at 14:11

    Thanks for sharing. Always good to find a real exrpet.

    • Keiwan | #7
      2011-09-03 at 19:34

      Great common sense here. Wish I’d thuhogt of that.

      • Wind | #8
        2011-11-11 at 14:53

        If I were a Teegnae Mutant Ninja Turtle, now I’d say “Kowabunga, dude!”

  3. fan | #9
    2011-06-17 at 08:34

    1个8K的块除去oracle管理用的空间,一般不会超过255行数据,如果一个事务只锁定一行,也不会超过255个事务。

    • Karson | #10
      2011-06-23 at 03:14

      I bow down hmulby in the presence of such greatness.

      • Vicky | #11
        2011-09-03 at 05:04

        This is the perfect way to break down this informatoin.

        • Jacki | #12
          2011-11-12 at 05:45

          You’ve got it in one. Couldn’t have put it beettr.

    • Geraldine | #13
      2011-06-23 at 10:01

      Touchdown! That’s a really cool way of ptuting it!

      • Kevrell | #14
        2011-09-03 at 05:21

        Dude, right on there brtoher.

        • Honney | #15
          2011-11-12 at 06:29

          You are so aewsmoe for helping me solve this mystery.