Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to understand the INITRANS and affairs of Oracle. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

INITRANS and transaction of Oracle

Each block has a block header. There is a transaction table in the header of this block. Entries are created in the transaction table to describe which transactions lock which rows / elements on the block. The initial size of this transaction table is specified by the object's INITRANS setting. For tables, this value defaults to 2 (the INITRANS of the index also defaults to 2). The transaction table dynamically expands as needed, up to a maximum of MAXTRANS entries (assuming there is enough free space on the block). Each allocated transaction entry takes up 23 million 24 bytes of storage space in the header of the block. Note that for Oracle 10g maxtrans, the MAXTRANS of all segments is 255.

Because there is a concept of PCT_free in the oracle block, that is, 10% of the block size is reserved by the oracle as a buffer, when the transaction modifying the oracle increases, the transaction slot grows downwards, and when the data of the oracle block is updated, the data grows up, and the space of the PCT_free is compressed. Here we are just looking at the role of INITRANS, so pctfree 0

SCOTT@hyyk > create table T3 (id int, num int) INITRANS 1 pctfree 0

SCOTT@hyyk > 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='T3'

You can see that the pctfree 0 parameter takes effect, where the initial value of INITRANS is 1

SCOTT@hyyk > insert into T3 values (1BI 1)

SCOTT@hyyk > insert into T3 values (2jue 2)

SCOTT@hyyk > insert into T3 values (3BI 3)

SCOTT@hyyk > commit

SCOTT@hyyk > select id,num, dbms_rowid.rowid_relative_fno (rowid) file#, dbms_rowid.rowid_block_number (rowid) block# from T3

ID NUM FILE# BLOCK#

--

1 1 4 581

2 2 4 581

3 3 4 581

You can see that the three rows of data inserted are all 581 pieces of file 4.

Session 1

SCOTT@hyyk > update T3 set num=11 where id = 1; / / No submission here

Looking at the transaction, you can see that a transaction has been started, occupying a transaction slot 080011007A050000

Session 2

SCOTT@hyyk > update T3 set num=11 where id = 2

1 row updated. / / found that it can be inserted here.

The initial size of this transaction table is specified by the object's INITRANS setting. For tables, this value defaults to 2 (the INITRANS of the index also defaults to 2)

Look at the transaction again and start another transaction, transaction xid 06001B0075050000

Conversation three

SCOTT@hyyk > update T3 set num=11 where id = 3

... wait for

Because there are no slots in the transaction table

The above is how to understand the INITRANS and transactions of Oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.