MAXTRANS - Deprecated in 10g

Learned something new from Oracle product documentation. Its about the Physical Storage parameter MAXTRANS which is deprecated in Oracle 10g, its defaulted to 255. Here is the Test case to demonstrate that.

test@9iR2> create table t(x number) maxtrans 2;

Table created.

test@9iR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------

  CREATE TABLE "TEST"."T"
   (    "X" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 2 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"

test@9iR2> SELECT tablespace_name, segment_space_management
  2  FROM dba_tablespaces
  3  where tablespace_name ='SYSTEM';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
SYSTEM                         MANUAL

test@9iR2> exec show_space(USER,'T');
l_free_blks*******************  0
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  7
l_unused_bytes****************  57344
l_last_used_extent_file_id****  1
l_last_used_extent_block_id***  55793
l_last_used_block*************  1

PL/SQL procedure successfully completed.
 

   You see now the Unused Block is 7, Since 1 Block is allocated to Block header.

test@9iR2> insert into t
  2  select level
  3  from dual
  4  connect by level <= 3;

3 rows created.

test@9iR2> commit;

Commit complete.

test@9iR2> exec show_space(USER,'T');
l_free_blks*******************  1
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  6
l_unused_bytes****************  49152
l_last_used_extent_file_id****  1
l_last_used_extent_block_id***  55793
l_last_used_block*************  2

PL/SQL procedure successfully completed.

   Now 1 Block is filled with Table data and 1 Block allocated to Block header.

--Session 1
test@9iR2> update t set x = x+1 where x = 1;

1 row updated.

--Session 2
test@9iR2> update t set x = x + 1 where x = 2;

1 row updated.

--Session 3
test@9iR2> update t set x = x+1 where x = 3;  <<<=== This is kept blocked due to MAXTRANS set to 2.

scott@10GR2> create table t(x number) maxtrans 2;

Table created.

scott@10GR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-----------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"

The MAXTRANS defaulted to 255 in Oracle 10g, though its explicitly specified 2 with Create table statement.

scott@10GR2> SELECT tablespace_name, segment_space_management
  2  FROM dba_tablespaces
  3  where tablespace_name ='TOOLS';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
TOOLS                          AUTO

scott@10GR2> insert into t
  2  select level
  3  from dual
  4  connect by level <= 3;

3 rows created.

scott@10GR2> commit;

Commit complete.

--Session 1
scott@10GR2> update t set x = x+1 where x = 1;

1 row updated.

--Session 2
scott@10GR2> update t set x = x + 1 where x = 2;

1 row updated.

--Session 3
scott@10GR2> update t set x = x+1 where x = 3;

1 row updated.