Lock Types - Oracle Database Transactions and Locking Revealed - page 35

An example of when you might need to increase the setting would be on a table or, even more frequently, on an
index (since index blocks can get many more rows on them than a table can typically hold) that is frequently modified
and has a lot of rows per block on average. You may need to increase either PCTFREE or INITRANS to set aside ahead
of time sufficient space on the block for the number of expected concurrent transactions. This is especially true if
you anticipate the blocks will be nearly full to begin with, meaning there is no room for the dynamic expansion of the
transaction structure on the block.
One last note on INITRANS . A couple of times I've stated that the default value for this attribute is 2. However,
if you examine the data dictionary after creating a table, you'll notice that INITRANS displays a value of 1:
EODA@ORA12CR1> create table t ( x int );
EODA@ORA12CR1> select ini_trans from user_tables where table_name = 'T';
INI_TRANS
----------
1
So is the default number of transaction slots 1 or 2? Even though the data dictionary is showing a value of 1, we
can demonstrate that it really is 2. Consider this experiment. First generate one transaction for table T by inserting
a single record:
EODA@ORA12CR1> insert into t values ( 1 );
Now verify that one block is consumed by table T :
EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
57715
Next, place into the variables— B and F —the block number and the data file number of the block used by table T :
EODA@ORA12CR1> column b new_val B
EODA@ORA12CR1> column f new_val F
EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) B,
2 dbms_rowid.ROWID_TO_ABSOLUTE_FNO( rowid, user, 'T' ) F
3 from t;
Now dump the block being used by table T :
EODA@ORA12CR1> alter system dump datafile &F block &B;
Next, place into a variable named TRACE the location and name of the trace file containing the dump information
for the block:
EODA@ORA12CR1> column trace new_val TRACE
EODA@ORA12CR1> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
2 from v$process a, v$session b, v$parameter c, v$instance d
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid')
5 and c.name = 'user_dump_dest';