ORACLE INITRANS和MAXTRANS 测试

1.默认创建表

create table test_initrans_1
(
 id int,
 name varchar2(10)
);

--导出实际表结构
create table TEST_INITRANS_1
(
  id   INTEGER,
  name VARCHAR2(10)
)
tablespace UD
  pctfree 10
  initrans 1
  maxtrans 255;

2.指定 maxtrans < 255 则 oracle 会自动改成 255 ( 如指定 >255 则报错）

create table TEST_INITRANS_2
(
  id   INTEGER,
  name VARCHAR2(10)
)
tablespace UD
  pctfree 10
  initrans 1
  maxtrans 2;

--导出实际表结构
create table TEST_INITRANS_2
(
  id   INTEGER,
  name VARCHAR2(10)
)
tablespace UD
  pctfree 10
  initrans 1
  maxtrans 255;

3.频繁改动的表，建议默认 initrans 改大点如10-50，pctfree 也可考虑加大

create table TEST_INITRANS_3
(
  id   INTEGER,
  name VARCHAR2(10)
)
tablespace UD
  pctfree 20
  initrans 10;

--导出实际表结构
create table TEST_INITRANS_3
(
  id   INTEGER,
  name VARCHAR2(10)
)
tablespace UD
  pctfree 20
  initrans 10
  maxtrans 255;

4. 默认创建分区表

CREATE TABLE test_initrans_4
(
  tab_seq  number not null,
  tab_no   varchar2(20),
  tab_date date           
)
partition by range (tab_date)
(
  partition P1901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')) tablespace PDB1_1901,
  partition P1902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')) tablespace PDB1_1902
);

--导出实际表结构
create table TEST_INITRANS_4
(
  tab_seq  NUMBER not null,
  tab_no   VARCHAR2(20),
  tab_date DATE
)
partition by range (TAB_DATE)
(
  partition P1901 values less than (TO_DATE('2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1901
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P1902 values less than (TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1902
    pctfree 10
    initrans 1
    maxtrans 255
);

5.频繁改动的分区表，建议默认 initrans 改大点如10-50，pctfree 也可考虑加大

create table TEST_INITRANS_5
(
  tab_seq  NUMBER not null,
  tab_no   VARCHAR2(20),
  tab_date DATE
)
partition by range (TAB_DATE)
(
  partition P1901 values less than (TO_DATE('2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1901
    pctfree 20
    initrans 10,
  partition P1902 values less than (TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1902
    pctfree 20
    initrans 10
);

alter table TEST_INITRANS_5 add PARTITION P1903 VALUES LESS THAN (TO_DATE('2019-04-01', 'YYYY-MM-DD')) TABLESPACE PDB1_1903
 pctfree 20
 initrans 20;

--导出实际表结构
create table TEST_INITRANS_5
(
  tab_seq  NUMBER not null,
  tab_no   VARCHAR2(20),
  tab_date DATE
)
partition by range (TAB_DATE)
(
  partition P1901 values less than (TO_DATE('2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1901
    pctfree 20
    initrans 10
    maxtrans 255,
  partition P1902 values less than (TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1902
    pctfree 20
    initrans 10
    maxtrans 255,
  partition P1903 values less than (TO_DATE('2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace PDB1_1903
    pctfree 20
    initrans 20
    maxtrans 255
);

6.批量插入测试数据

insert into test_initrans_1(id,name)
select rownum id, 
'N_' || lpad(rownum,3,'0') as name
from dual connect by level<=1000;

select min(id) as min_id,max(id) as max_id
,dbms_rowid.rowid_block_number(rowid) as "block_id(在第几个块)"
from test_initrans_1 group by dbms_rowid.rowid_block_number(rowid);
-------------------------------------------------------------------
    MIN_ID  MAX_ID  block_id(在第几个块)
1   1       491     2410253 -> (此块最大支持255个事务槽)--下两个同
2   492     975     2410254  
3   976     1000    2410255