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;
- 默认创建分区表
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
