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. For more information about ITL, see:

Inclueitl (Interested Transaction list) Description

Http://blog.csdn.net/tianlesoftware/article/details/6573988

 

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.

 

Oracle table creation Parameters

Http://blog.csdn.net/tianlesoftware/article/details/4954417

 

In earlier versions of Oracle, the default value of the table's initrans parameter is 1, but in the latest version, Oracle is changed to 2, even if the data dictionary is still 1.

 

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 uses a block: 94658.

 

-- Confirm that the block has a specific datafile Number:

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

 

 

-- Dump the block and confirm the number of ITL:

SQL> alter system dump datafile 1 block94658;

System altered.

 

-- View the trace file:

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 that the returned result is: initrans is 1. Therefore, even if the ITL initialization value in our data dictionary is 1, the number of ITL is 2 in actual creation.

 

 

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

PL/SQL procedure successfully completed.

-- Restore settings

 

 

For more information about Oracle object source code, see:

How to view object definition statements such as table store trigger functions in Oracle

Http://blog.csdn.net/tianlesoftware/article/details/5679293

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940