Test the default ITL quantity of Oracle 10g objects

Each Block in Oracle has a part called ITL, which is used to store transaction information.

The number of ITL of an object is determined by the initrans and maxtrans parameters of the object. The Oracle official documentation records that the default value of initrans is 1, and that of maxtrans is 255. in fact, the ITL in any block can grow dynamically as needed, as long as there is enough space in the block. The maximum number of ITL instances is 255. In fact, the size is determined by the size of the data block. In versions 10 Gb later than Oracle, for 8 KB block size, the maximum ITL count can only be 169.

OracleIn earlier versionsInitransThe default value is1But in the latest versionOracleChange2Even if the data dictionary is displayed1.

Next we will test the default value in the Oracle10.2.0.4 environment.

[Oracle @ localhost ~] $ Sqlplus/as sysdba;

SQL * Plus: Release 10.2.0.4.0-Productionon has been released without prior errors › 6 hours without errors 7 09:37:32 2012

Copyright (c) 1982,200 7, Oracle. All Rights Reserved.

 

Connected:

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0-Production

With the Partitioning, Data Mining and RealApplication Testing options

 

SQL> create table anqing (id number, namevarchar2 (20 ));

Table created.

SQL> insert into anqingvalues (1, 'tiancesoft ');

1 row created.

SQL> commit;

Commit complete.

 

SQL> select distinctdbms_rowid.rowid_block_number (rowid) from anqing;

DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)

------------------------------------

94658

 

From this statement, we can determine that the table usesBlock:94658.

 

--ConfirmBlockSpecificDatafileNo:

SQL> selectdbms_rowid.rowid_relative_fno (rowid) file_id, dbms_rowid.rowid_block_number (rowid) block_id from anqing;

 

FILE_ID BLOCK_ID

--------------------

1 94658

 

 

-- DumpTheBlock, ConfirmITLQuantity:

SQL> alter system dump datafile 1 block94658;

System altered.

 

--ViewTraceFile:

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/Oradata/XEZF/admin/udump/xezf_ora_31544.trc

 

Here are two pieces of ITL information. Now let's take a look at the default table creation parameter values:

 

SQL> set long 2000000

SQL> set pagesize 0

SQL> executedbms_metadata.set_transform_param (dbms_metadata.session_transform, 'store', false );

--This statement does not display certain attributes at the session level, making the returned results more concise.

 

PL/SQL procedure successfully completed.

 

SQL> selectdbms_metadata.get_ddl ('table', 'anqing', 'sys ') from dual;

 

Create table "SYS". "ANQING"

("ID" NUMBER,

"NAME" VARCHAR2 (20)

)

PCTFREE 10 PCTUSED 40

INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

TABLESPACE "SYSTEM"

--Note the returned results:InitransYes1.So even if we say that even in our data dictionaryITLThe initialization value is1ButITLNumber2.

SQL> executedbms_metadata.set_transform_param (dbms_metadata.session_transform, 'default ');

PL/SQL procedure successfully completed.

--Restore settings