Oracle中INITRANS和MAXTRANS引數
INITRANS：
INITRANS 指的是一個 BLOCK 上初始預分配給並行交易控制的空間 (ITLs)
( 當 BLOCK 上某筆 ROW 被交易更新鎖定時，會在 BLOCK header ITL allocate 一個鎖，當下一個交易要更新同一筆 row 時，就會發現他已經被先前的交易持有鎖了，會先去檢查該交易是否 active? 如果是，後來的該筆交易就會被 blocking ，等待 ) 如果一個表格需要同時有大量交易存取，你應該設定 INITRANS 大一點，可以減少 ITL 還要動態擴充的 Overhead 。
For tables INITRANS defaults to 1 for indexes 2
MAXTRANS：
MAXTRANS 指的是如果 INITRANS 空間不夠用了，就會自動擴充套件 ITL ，直到最大值也就是 MAXTRANS 值為止，預設是 255 。但是，如果 BLOCK 空間已經不足，也有可能無法持續擴充到 255 個 ITS 空間喔。
每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由物件的INITRANS 設定指定。對於表，這個值預設為1（索引的INITRANS 預設為2）。事務表會根據需要動態擴充套件，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要佔用塊首部中的23～24 位元組的儲存空間。注意，對於Oracle 10g，MAXTRANS 則會忽略，所有段的MAXTRANS 都是255。
也就是說，如果某個事物鎖定了這個塊的資料，則會在這個地方記錄事務的標識，當然那個事務要先看一下這個地方是不是已經有人佔用了，如果有，則去看看那個事務是否為活動狀態。如果不活動，比如已經提交或者回滾，則可以覆蓋這個地方。如果活動，則需要等待(閂的作用)
所以，如果有大量的併發訪問使用的這個塊，則引數不能太小，否則資源競爭將導致系統併發效能下降。
測試了一下ORACLE 併發事務的時候的塊分配和ITL 管理,
略去大部分的測試過程,大概的結果小結如下:
1. INITRANS =1 時 併發多個INSERT 事務(本次測試最多5個)的時候並不會由於ITL的爭用而等待組塞,ORACLE 採取的策略是每個INSERT事物已經操作完成，屬於不活動事物，只等待commit或者rollback,這樣各個會話之間就不會產生衝突，除非段沒有多餘的塊(次種情況與本次的主題無關).
2.INITRANS =1 時 併發多個UPDATE事務(本次測試最多7個)的時候也不會由於ITL的爭用而導致等待產生,此時ORACLE除了使用預設的ITL之外，另外動態擴充套件所需要的ITL，緊緊在非常極端的情況下才會出現等待，(當然應用層面的死鎖或等待與本主題無關)。
1) 該BLOCK沒有FREE空間了，注意FREE引數的設定不能太小。
2) 該塊使用的ITL總數，超過該塊允許的ITL的最大值min(round(block_size*0.5/24) - 2 ,255) 。
要達到這樣的極端情況實際的生產情況是很難的，應該比業務SQL的死鎖出現的機率更小。
小結：建立表的時候除非已經清楚，大部分的情況下沒有必要調整INITRANS引數，通常1-4以下足夠用了，INITRANS 設定非常大的時候ORACLE 有出現壞塊的BUG，另外FREE 引數倒是要注意不能隨意改小,除非你已經很清楚更改的後果．
SQL> create table xx (x number) storage(initial 64k next 64k) initrans 2;
Table created.
SQL>
SQL> create table a as select * from xx;
Table created.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
no rows selected
SQL> INSERT INTO XX SELECT 11 FROM DUAL;
1 row created.
SQL> select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name ='XX';
SEGMENT_NAME EXTENT_ID BLOCKS BYTES
--------------- ---------- ---------- ----------
XX 0 8 65536
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='XX';
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
XX VALID 10 40 2 255 65536 65536 1 2147483645
SQL>
SQL> INSERT INTO a SELECT 11 FROM DUAL;
1 row created.
SQL> commit;
Commit complete.
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='A';
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
A VALID 10 40 1 255 65536 1048576 1 2147483645
SQL> select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name ='A';
SEGMENT_NAME EXTENT_ID BLOCKS BYTES
--------------- ---------- ---------- ----------
A 0 8 65536
SQL> INSERT INTO XX SELECT 12 FROM DUAL;
1 row created.
SQL> INSERT INTO a SELECT 12 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
SQL> INSERT INTO XX SELECT 13 from dual;
1 row created.
SQL> INSERT INTO a SELECT 13 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
SQL> INSERT INTO XX SELECT 14 from dual;
1 row created.
SQL> INSERT INTO a SELECT 14 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
SQL>
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
SQL> commit;
Commit complete.
SQL> INSERT INTO XX SELECT 15 from dual;
1 row created.
SQL> INSERT INTO a SELECT 15 from dual;
1 row created.
SQL> INSERT INTO XX SELECT 16 from dual;
1 row created.
SQL> INSERT INTO a SELECT 16 from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
6 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
6 rows selected.
SQL>
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
21 1 94665
22 1 94665
23 1 94665
24 1 94665
25 1 94665
X FILE# BLOCK#
---------- ---------- ----------
26 1 94665
31 1 94665
32 1 94665
33 1 94665
34 1 94665
35 1 94665
36 1 94665
18 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
21 1 102801
22 1 102801
23 1 102801
24 1 102801
25 1 102801
X FILE# BLOCK#
---------- ---------- ----------
26 1 102801
31 1 102801
32 1 102801
33 1 102801
34 1 102801
35 1 102801
36 1 102801
18 rows selected.
另開一個視窗插入：
INSERT INTO XX SELECT 21 from dual;
INSERT INTO XX SELECT 22 from dual;
INSERT INTO XX SELECT 23 from dual;
INSERT INTO XX SELECT 24 from dual;
INSERT INTO XX SELECT 25 from dual;
INSERT INTO XX SELECT 26 from dual;
INSERT INTO A SELECT 21 from dual;
INSERT INTO A SELECT 22 from dual;
INSERT INTO A SELECT 23 from dual;
INSERT INTO A SELECT 24 from dual;
INSERT INTO A SELECT 25 from dual;
INSERT INTO A SELECT 26 from dual;
commit;
再開一個視窗插入：
INSERT INTO XX SELECT 31 from dual;
INSERT INTO XX SELECT 32 from dual;
INSERT INTO XX SELECT 33 from dual;
INSERT INTO XX SELECT 34 from dual;
INSERT INTO XX SELECT 35 from dual;
INSERT INTO XX SELECT 36 from dual;
INSERT INTO A SELECT 31 from dual;
INSERT INTO A SELECT 32 from dual;
INSERT INTO A SELECT 33 from dual;
INSERT INTO A SELECT 34 from dual;
INSERT INTO A SELECT 35 from dual;
INSERT INTO A SELECT 36 from dual;
commit;
查詢：
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
21 1 94665
22 1 94665
23 1 94665
24 1 94665
25 1 94665
X FILE# BLOCK#
---------- ---------- ----------
26 1 94665
31 1 94665
32 1 94665
33 1 94665
34 1 94665
35 1 94665
36 1 94665
18 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
21 1 102801
22 1 102801
23 1 102801
24 1 102801
25 1 102801
X FILE# BLOCK#
---------- ---------- ----------
26 1 102801
31 1 102801
32 1 102801
33 1 102801
34 1 102801
35 1 102801
36 1 102801
18 rows selected.
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_NAME IN('XX','A');
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
A VALID 10 40 1 255 65536 1048576 1 2147483645
XX VALID 10 40 2 255 65536 65536 1 2147483645
SQL>
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,PCT_FREE,STATUS from dba_indexes where table_owner='SYS' and table_name IN('XX','A');
OWNER INDEX_NAME TABLE_OWNE TABLE_NAME UNIQUENES INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_FREE STATUS
---------- ---------- ---------- ---------- --------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------
SYS IDX_XX SYS XX NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID
SYS IDX_A SYS A NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID
SQL>
INITRANS 指的是一個 BLOCK 上初始預分配給並行交易控制的空間 (ITLs)
( 當 BLOCK 上某筆 ROW 被交易更新鎖定時，會在 BLOCK header ITL allocate 一個鎖，當下一個交易要更新同一筆 row 時，就會發現他已經被先前的交易持有鎖了，會先去檢查該交易是否 active? 如果是，後來的該筆交易就會被 blocking ，等待 ) 如果一個表格需要同時有大量交易存取，你應該設定 INITRANS 大一點，可以減少 ITL 還要動態擴充的 Overhead 。
For tables INITRANS defaults to 1 for indexes 2
MAXTRANS：
MAXTRANS 指的是如果 INITRANS 空間不夠用了，就會自動擴充套件 ITL ，直到最大值也就是 MAXTRANS 值為止，預設是 255 。但是，如果 BLOCK 空間已經不足，也有可能無法持續擴充到 255 個 ITS 空間喔。
每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由物件的INITRANS 設定指定。對於表，這個值預設為1（索引的INITRANS 預設為2）。事務表會根據需要動態擴充套件，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要佔用塊首部中的23～24 位元組的儲存空間。注意，對於Oracle 10g，MAXTRANS 則會忽略，所有段的MAXTRANS 都是255。
也就是說，如果某個事物鎖定了這個塊的資料，則會在這個地方記錄事務的標識，當然那個事務要先看一下這個地方是不是已經有人佔用了，如果有，則去看看那個事務是否為活動狀態。如果不活動，比如已經提交或者回滾，則可以覆蓋這個地方。如果活動，則需要等待(閂的作用)
所以，如果有大量的併發訪問使用的這個塊，則引數不能太小，否則資源競爭將導致系統併發效能下降。
測試了一下ORACLE 併發事務的時候的塊分配和ITL 管理,
略去大部分的測試過程,大概的結果小結如下:
1. INITRANS =1 時 併發多個INSERT 事務(本次測試最多5個)的時候並不會由於ITL的爭用而等待組塞,ORACLE 採取的策略是每個INSERT事物已經操作完成，屬於不活動事物，只等待commit或者rollback,這樣各個會話之間就不會產生衝突，除非段沒有多餘的塊(次種情況與本次的主題無關).
2.INITRANS =1 時 併發多個UPDATE事務(本次測試最多7個)的時候也不會由於ITL的爭用而導致等待產生,此時ORACLE除了使用預設的ITL之外，另外動態擴充套件所需要的ITL，緊緊在非常極端的情況下才會出現等待，(當然應用層面的死鎖或等待與本主題無關)。
1) 該BLOCK沒有FREE空間了，注意FREE引數的設定不能太小。
2) 該塊使用的ITL總數，超過該塊允許的ITL的最大值min(round(block_size*0.5/24) - 2 ,255) 。
要達到這樣的極端情況實際的生產情況是很難的，應該比業務SQL的死鎖出現的機率更小。
小結：建立表的時候除非已經清楚，大部分的情況下沒有必要調整INITRANS引數，通常1-4以下足夠用了，INITRANS 設定非常大的時候ORACLE 有出現壞塊的BUG，另外FREE 引數倒是要注意不能隨意改小,除非你已經很清楚更改的後果．
SQL> create table xx (x number) storage(initial 64k next 64k) initrans 2;
Table created.
SQL>
SQL> create table a as select * from xx;
Table created.
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
no rows selected
SQL> INSERT INTO XX SELECT 11 FROM DUAL;
1 row created.
SQL> select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name ='XX';
SEGMENT_NAME EXTENT_ID BLOCKS BYTES
--------------- ---------- ---------- ----------
XX 0 8 65536
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='XX';
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
XX VALID 10 40 2 255 65536 65536 1 2147483645
SQL>
SQL> INSERT INTO a SELECT 11 FROM DUAL;
1 row created.
SQL> commit;
Commit complete.
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_name='A';
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
A VALID 10 40 1 255 65536 1048576 1 2147483645
SQL> select SEGMENT_NAME,EXTENT_ID,BLOCKS,BYTES from user_extents where segment_name ='A';
SEGMENT_NAME EXTENT_ID BLOCKS BYTES
--------------- ---------- ---------- ----------
A 0 8 65536
SQL> INSERT INTO XX SELECT 12 FROM DUAL;
1 row created.
SQL> INSERT INTO a SELECT 12 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
SQL> INSERT INTO XX SELECT 13 from dual;
1 row created.
SQL> INSERT INTO a SELECT 13 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
SQL> INSERT INTO XX SELECT 14 from dual;
1 row created.
SQL> INSERT INTO a SELECT 14 FROM DUAL;
1 row created.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
SQL>
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
SQL> commit;
Commit complete.
SQL> INSERT INTO XX SELECT 15 from dual;
1 row created.
SQL> INSERT INTO a SELECT 15 from dual;
1 row created.
SQL> INSERT INTO XX SELECT 16 from dual;
1 row created.
SQL> INSERT INTO a SELECT 16 from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
6 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
6 rows selected.
SQL>
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
21 1 94665
22 1 94665
23 1 94665
24 1 94665
25 1 94665
X FILE# BLOCK#
---------- ---------- ----------
26 1 94665
31 1 94665
32 1 94665
33 1 94665
34 1 94665
35 1 94665
36 1 94665
18 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
21 1 102801
22 1 102801
23 1 102801
24 1 102801
25 1 102801
X FILE# BLOCK#
---------- ---------- ----------
26 1 102801
31 1 102801
32 1 102801
33 1 102801
34 1 102801
35 1 102801
36 1 102801
18 rows selected.
另開一個視窗插入：
INSERT INTO XX SELECT 21 from dual;
INSERT INTO XX SELECT 22 from dual;
INSERT INTO XX SELECT 23 from dual;
INSERT INTO XX SELECT 24 from dual;
INSERT INTO XX SELECT 25 from dual;
INSERT INTO XX SELECT 26 from dual;
INSERT INTO A SELECT 21 from dual;
INSERT INTO A SELECT 22 from dual;
INSERT INTO A SELECT 23 from dual;
INSERT INTO A SELECT 24 from dual;
INSERT INTO A SELECT 25 from dual;
INSERT INTO A SELECT 26 from dual;
commit;
再開一個視窗插入：
INSERT INTO XX SELECT 31 from dual;
INSERT INTO XX SELECT 32 from dual;
INSERT INTO XX SELECT 33 from dual;
INSERT INTO XX SELECT 34 from dual;
INSERT INTO XX SELECT 35 from dual;
INSERT INTO XX SELECT 36 from dual;
INSERT INTO A SELECT 31 from dual;
INSERT INTO A SELECT 32 from dual;
INSERT INTO A SELECT 33 from dual;
INSERT INTO A SELECT 34 from dual;
INSERT INTO A SELECT 35 from dual;
INSERT INTO A SELECT 36 from dual;
commit;
查詢：
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from xx;
X FILE# BLOCK#
---------- ---------- ----------
11 1 94665
12 1 94665
13 1 94665
14 1 94665
15 1 94665
16 1 94665
21 1 94665
22 1 94665
23 1 94665
24 1 94665
25 1 94665
X FILE# BLOCK#
---------- ---------- ----------
26 1 94665
31 1 94665
32 1 94665
33 1 94665
34 1 94665
35 1 94665
36 1 94665
18 rows selected.
SQL> select x ,dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from a;
X FILE# BLOCK#
---------- ---------- ----------
11 1 102801
12 1 102801
13 1 102801
14 1 102801
15 1 102801
16 1 102801
21 1 102801
22 1 102801
23 1 102801
24 1 102801
25 1 102801
X FILE# BLOCK#
---------- ---------- ----------
26 1 102801
31 1 102801
32 1 102801
33 1 102801
34 1 102801
35 1 102801
36 1 102801
18 rows selected.
SQL> select TABLE_NAME,STATUS,PCT_FREE,PCT_USED,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables where table_NAME IN('XX','A');
TABLE_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------
A VALID 10 40 1 255 65536 1048576 1 2147483645
XX VALID 10 40 2 255 65536 65536 1 2147483645
SQL>
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,UNIQUENESS,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,PCT_FREE,STATUS from dba_indexes where table_owner='SYS' and table_name IN('XX','A');
OWNER INDEX_NAME TABLE_OWNE TABLE_NAME UNIQUENES INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_FREE STATUS
---------- ---------- ---------- ---------- --------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------
SYS IDX_XX SYS XX NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID
SYS IDX_A SYS A NONUNIQUE 2 255 65536 1048576 1 2147483645 10 VALID
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2148862/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12C 中CDB和PDB的引數檔案管理Oracle
- Oracle 核心引數Oracle
- Oracle中的sysctl.conf核心引數Oracle
- ORACLE中Cursor_sharing引數詳解Oracle
- Oracle中單引號和雙引號的區別Oracle
- oracle中的processes,session,transaction引數詳解OracleSession
- Oracle：PDB 引數管理Oracle
- 聊聊Oracle表空間Offline的三種引數（中）Oracle
- Laravel同時接收路由引數和查詢字串中的引數Laravel路由字串
- ORACLE並行相關的引數Oracle並行
- Oracle GoldenGate常用引數詳解OracleGo
- oracle rac 核心引數詳解Oracle
- Oracle Table建立引數說明Oracle
- Oracle JDBC ResultSet引數測試OracleJDBC
- 使用 XmlCommand 對Oracle傳引數XMLOracle
- Oracle RAC引數檔案管理Oracle
- Oracle 效能最佳化之核心的shmall 和shmmax 引數OracleHMM
- solaris10中安裝oracle核心引數的調整Oracle
- C# 中的 in 引數和效能分析C#
- oracle O7_DICTIONARY_ACCESSIBILITY 引數Oracle
- [20190917]oracle引數deferred屬性.txtOracle
- Oracle面試寶典-引數篇Oracle面試
- Oracle RAC修改引數檔案位置Oracle
- Oracle ENABLE=broken引數與TCP KeepAliveOracleTCP
- 【PROFILE】PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX引數在Oracle不同版本中的差別Oracle
- Oracle:SYS_REFCURSOR作為函式中的輸出引數Oracle函式
- Bash變數和引數變數
- python變數和引數Python變數
- Java對比有引數和無引數Java
- 【Oracle】簡單引數也有講究，JOB_QUEUE_PROCESS引數調研Oracle
- Oracle初始化引數的來源Oracle
- Oracle安裝相關Linux引數(轉)OracleLinux
- oracle資料庫--Oracle雙引號和單引號的區別小結Oracle資料庫
- 引數的定義和引數的傳遞
- 從網址中截去主機名和引數
- C#通過反射獲取類中的方法和引數個數，反射呼叫方法帶引數C#反射
- TypeScript 函式可選引數和預設引數TypeScript函式
- postman 請求引數和 Spring Boot Controller 接受引數PostmanSpring BootController
- 小程式內引數和掃碼引數統一