1.默認(rèn)創(chuàng)建表
create table test_initrans_1
(
id int,
name varchar2(10)
);
--導(dǎo)出實(shí)際表結(jié)構(gòu)
create table TEST_INITRANS_1
(
id INTEGER,
name VARCHAR2(10)
)
tablespace UD
pctfree 10
initrans 1
maxtrans 255;
2.指定 maxtrans < 255 則 oracle 會自動改成 255 ( 如指定 >255 則報(bào)錯）
create table TEST_INITRANS_2
(
id INTEGER,
name VARCHAR2(10)
)
tablespace UD
pctfree 10
initrans 1
maxtrans 2;
--導(dǎo)出實(shí)際表結(jié)構(gòu)
create table TEST_INITRANS_2
(
id INTEGER,
name VARCHAR2(10)
)
tablespace UD
pctfree 10
initrans 1
maxtrans 255;
3.頻繁改動的表，建議默認(rèn) initrans 改大點(diǎn)如10-50，pctfree 也可考慮加大
create table TEST_INITRANS_3
(
id INTEGER,
name VARCHAR2(10)
)
tablespace UD
pctfree 20
initrans 10;
--導(dǎo)出實(shí)際表結(jié)構(gòu)
create table TEST_INITRANS_3
(
id INTEGER,
name VARCHAR2(10)
)
tablespace UD
pctfree 20
initrans 10
maxtrans 255;
- 默認(rèn)創(chuàng)建分區(qū)表
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
);
--導(dǎo)出實(shí)際表結(jié)構(gòu)
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.頻繁改動的分區(qū)表吱窝，建議默認(rèn) initrans 改大點(diǎn)如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;
--導(dǎo)出實(shí)際表結(jié)構(gòu)
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.批量插入測試數(shù)據(jù)
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個事務(wù)槽)--下兩個同
2 492 975 2410254
3 976 1000 2410255
