Increase ini_trans for tables

[ggnanaraj]

I have a lot of application tables with the default ini_trans of 1. This is causing a lot of waits, thou' the max_trans is 255.

How can I increase the ini_trans ? I'm thinking of increasing it to 2. Please give your suggestion.

ORACLE: SYS> select * from v$waitstat;

CLASS COUNT TIME
------------------ ---------- ----------
data block 27412 24878
sort block 0 0
save undo block 0 0
segment header 0 0
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 0 0
undo block 0 0

14 rows selected.

ORACLE: SYS>


ORACLE: SYS> SELECT count, file#, name
2 FROM x$kcbfwait, v$datafile
3 WHERE indx + 1 = file#
4 ORDER BY count
5 ;

COUNT FILE# NAME
---------- ---------- ---------------------------------------------------------------
0 2 E:\ORANT\DATABASE\USR1ORCL.ORA
0 3 E:\ORANT\DATABASE\RBS1ORCL.ORA
0 4 E:\ORANT\DATABASE\TMP1ORCL.ORA
0 6 E:\ORANT\DATABASE\ESFDOUTPUT.ORA
0 7 D:\ORANT\DATABASE\ORACLE\INDX1ORCL.ORA
0 10 D:\ORANT\DATABASE\ORACLE\ESFDIN301.ORA
0 11 D:\ORANT\DATABASE\ORACLE\ESFDIN401.ORA
0 9 D:\ORANT\DATABASE\ORACLE\ESFDIN201.ORA
2 1 E:\ORANT\DATABASE\SYS1ORCL.ORA
17966 8 D:\ORANT\DATABASE\ORACLE\ESFDINPUT02.ORA
33267 5 E:\ORANT\DATABASE\ESFDINPUT.ORA

11 rows selected.

ORACLE: SYS>

Thanks.

[Sameer]

SQL> CREATE TABLE test (id NUMBER);

Table created.

SQL> SELECT ini_trans, max_trans from dba_tables WHERE table_name = 'TEST';

 INI_TRANS  MAX_TRANS
---------- ----------
         1        255

SQL> ALTER TABLE TEST INITRANS 5 MAXTRANS 255 ;

Table altered.

SQL> SELECT ini_trans, max_trans from dba_tables WHERE table_name = 'TEST' ;

 INI_TRANS  MAX_TRANS
---------- ----------
         5        255