Here is video of these procedures - Change INITRANS on table in Oracle database
First of all what is initrans? it is initial number of concurrent entries allocated within each data block allocated to the database object, this value can range from 1 to 255 and default is 1 with following exceptions:
- The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.
- The default value for an index is 2.
Each transaction that updates a block requires a transaction entry in the block. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
1) check if table have child tables with pk-fk relationship, if table have child table(s) then consider to change initrans also for child table(s)
select * from dba_constraints a, dba_constraints b where a.constraint_name = b.r_constraint_name and a.table_name = 'TABLE_NAME';2) check if Table have indexes, then you must rebuild indexes, and also change initrans for indexes.
commands will be like:
ALTER TABLE TABLE_NAME MOVE INITRANS 10; ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20; ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20; ......
3) check invalid objects and compile if any.
Let's demonstrate small demo :
[oracle@testdb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 16:42:12 2018 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> set linesize 500 SQL> select * from dba_constraints a, dba_constraints b where a.constraint_name = b.r_constraint_name and a.table_name = 'TABLE_NAME'; no rows selected SQL> select table_name, INI_TRANS from dba_tables where table_name = 'TABLE_NAME'; TABLE_NAME INI_TRANS ------------------------------ ---------- TABLE_NAME 1 SQL> SELECT table_name, index_name, status, ini_trans FROM DBA_INDEXES WHERE TABLE_NAME = 'TABLE_NAME'; TABLE_NAME INDEX_NAME STATUS INI_TRANS ------------------------------ ------------------------------ -------- ---------- TABLE_NAME TABLE_NAME_PK VALID 2 TABLE_NAME TABLE_NAME_I1 VALID 2 SQL> ALTER TABLE TABLE_NAME MOVE INITRANS 10; Table altered. SQL> select table_name, INI_TRANS from dba_tables where table_name = 'TABLE_NAME'; TABLE_NAME INI_TRANS ------------------------------ ---------- TABLE_NAME 10 SQL> SELECT table_name, index_name, status, ini_trans FROM DBA_INDEXES WHERE TABLE_NAME = 'TABLE_NAME'; TABLE_NAME INDEX_NAME STATUS INI_TRANS ------------------------------ ------------------------------ -------- ---------- TABLE_NAME TABLE_NAME_PK UNUSABLE 2 TABLE_NAME TABLE_NAME_I1 UNUSABLE 2 SQL> ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20; Index altered. SQL> ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20; Index altered. SQL> SELECT table_name, index_name, status, ini_trans FROM DBA_INDEXES WHERE TABLE_NAME = 'TABLE_NAME'; TABLE_NAME INDEX_NAME STATUS INI_TRANS ------------------------------ ------------------------------ -------- ---------- TABLE_NAME TABLE_NAME_PK VALID 20 TABLE_NAME TABLE_NAME_I1 VALID 20 SQL>
No comments:
Post a Comment