Initrans

I was looking at Anand Blog and thought to mention one thing.

I am using 11.2.0.2 Version and here default initrans is 1. Also, the thing i want to mention is when you increase this parameters older blocks are not affected only newly created blocks after this change would be affected.


Taral@Taral1> drop table test1 purge;

Table dropped.

Taral@Taral1> create table test1(col1 number);

Table created.

Taral@Taral1> select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='TARAL';

OWNER                          TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ----------
TARAL                          TEST1                                   1        255

Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select segment_name,tablespace_name,header_file, header_block from dba_segments where segment_name='TEST1';

SEGMENT_NAME                                                                      TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ------------------------------ ----------- ------------
TEST1                                                                             USERS                                    4          522

Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';

SEGMENT_NAME                                                                        BLOCK_ID    FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1                                                                                    520          4 USERS
TEST1                                                                                    528          4 USERS
TEST1                                                                                    536          4 USERS

Taral@Taral1>
Taral@Taral1> alter system dump datafile 4 block 528;

System altered.

Block header dump:  0x01000210
 Object id on Block? Y
 seg/obj: 0x127c1  csc: 0x00.d0319  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.002.000002ee  0x00c02ec2.008d.02  --U-  660  fsc 0x0000.000d037d
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264

Taral@Taral1> alter table test1 initrans 5;

Table altered.

Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';

SEGMENT_NAME                                                                        BLOCK_ID    FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1                                                                                    520          4 USERS
TEST1                                                                                    528          4 USERS
TEST1                                                                                    536          4 USERS
TEST1                                                                                    544          4 USERS
TEST1                                                                                    552          4 USERS

Taral@Taral1> alter system dump datafile 4 block 544;

System altered.

Block header dump:  0x01000220
 Object id on Block? Y
 seg/obj: 0x127c2  csc: 0x00.db29d  itc: 5  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000218 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00b.00000306  0x00c00861.009a.05  --U-  654  fsc 0x0000.000db2a5
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000220
data_block_dump,data header at 0xf2982ac

Taral@Taral1> alter system dump datafile 4 block 528;

System altered.

Block header dump:  0x01000210
 Object id on Block? Y
 seg/obj: 0x127c2  csc: 0x00.d03e4  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.015.000002fd  0x00c00112.00a4.0c  --U-  660  fsc 0x0000.000d03ec
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264

Blocks already allocated are not affected but newly created blocks will take new settings.

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: