What is INITRANS and MAXTRANS in Oracle?

What is INITRANS and MAXTRANS in Oracle?

Specifying the Transaction Entry Parameters: INITRANS and MAXTRANS. INITRANS specifies the number of DML transaction entries for which space is initially reserved in the data block header. Therefore, you can limit the amount of free space that can be allocated for transaction entries in a data block using MAXTRANS .

What is INITRANS in Oracle table?

The INITRANS setting controls Initial Transaction Slots (ITLs). A transaction slot is required for any session that needs to modify a block in an object. For tables INITRANS defaults to 1 for indexes, 2. The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255).

What is INITRANS?

INITRANS is a block level storage parameter which can be specified while creating a object (table). INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block. There can be a maximum of 255 concurrent sessions that can access a block at any given time.

What is MAXTRANS in Oracle?

Answer: In previous releases of the Oracle Database, the MAXTRANS values used to represent a physical attribute for objects such as table, index, or cluster. MAXTRANS represented the maximum number of concurrent update transactions for any given data block belonging to the segment.

What is PCT free in Oracle?

PCTFREE is a block storage parameter used to specify how much space should be left in a database block for future updates. For example, for PCTFREE=10, Oracle will keep on adding new rows to a block until it is 90% full. This eliminates holes created by row deletions and maximizes contiguous free space in blocks.

What is a tablespace in Oracle?

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database’s data. Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

What does Nologging mean in Oracle?

LOGGING/NOLOGGING helps manage enabling direct path writes in order to reduce the generation of REDO and UNDO. It is one of several ways to control the delicate balance between recoverability and performance. Oracle Architecture Background Information. REDO is how Oracle provides durability, the “D” in ACID.

How do you change Initrans?

How to change initrans on table for avoiding enq: TX – allocate ITL 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)
2. check if Table have indexes, then you must rebuild indexes, and also change initrans for indexes.

What is block in database?

A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

How many types of segments are there in Oracle?

four types
There are four types of segments used in Oracle databases: data segments.

What happens when tablespace is full in Oracle?

Sooner or later, every Oracle DBA will encounter the issue of an exhausted temporary tablespace. If one session uses up all the temporary tablespace, all other users that require it for some operation that are assigned to that temporary tablespace will be affected and will eventually get the ORA-1652 error.

How many types of tablespace are there in Oracle?

Type of data consists of 3 kinds of tablespace including: Permanent Tablespace. Temporary Tablespace. Undo Tablespace.

What is the maxtrans setting for Oracle initrans?

For tables INITRANS defaults to 1 for indexes, 2. The MAXTRANS setting controls the maximum number of ITLs that a block can allocate (usually defaults to 255). If a block is sparsely populated then Oracle will dynamically increase the number of ITLs up to MAXTRANS.

When do you need transaction entry in initrans?

A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT…FOR UPDATE statement accessing one or more rows in the block. Every transaction which modifies a block must acquire an entry in the Interested Transaction List (ITL) in block. Space for this list is defined by INITRANS.

What is the default block size for initrans?

The database block size plays an important role in allocating the number of inital ITLs for the blocks. The default value is 1 for tables and 2 for clusters and indexes. The default value is an operating system-specific function of block size, not exceeding 255.

How can I increase the value of initrans?

Since each transaction updating a block requires a transaction entry, you can also increase the INITRANS value for high volume databases. Use a tool (like Ion) to see the specific table of index that has the contention, that’s a clue.