Install New Unicode Character Set in Oracle

Locale Builder is an utility to view, modify, or define locale-specific data. As well as to create your own formats for language, territory, character set, and linguistic sort.(I will discuss how to create .nlb(binary file) from .nlt(text file)).


.nlt file looks like this lx22713.nlt and there is also boot.nlt file lx0boot.nlt.

1. Choose Tools > Generate NLB or click the Generate NLB icon in the left side bar.
2. Click Browse to find the directory where the NLT files(containing boot file) are located and click ok.
   

  3.Copy these .nlb files and paste them into $ORACLE_HOME/nls/data directory.

That is all,new Unicode character set is installed.

Now to use this Unicode character:

1. Run->regedit->HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_...-> find and modify NLS_LANG value to AMERICAN_AMERICA.GEO8BOGALTWIN(this name is found in .nlt file  in a  <name> tag)

2.Open PL/SQL Developer->choose Tools->Preferences...->Fonts->Select-> and then choose Font:.

Enable ssh root login in Solaris 10

1. Change the file /etc/ssh/sshd_config with PermitRootLogin yes to replace PermitRootLogin no;

#vi /etc/ssh/sshd_config

2. restart the services

#svcadm restart svc:/network/ssh:default

Disable Telnet on Solaris 10

Simply do this:

# svcadm disable telnet

Double-click opens search instead of folder

Run->cmd


C:\>regsvr32 /i shell32.dll

Oracle web-based management(how to access)

Oracle Enterprise Manager:
C:\>emctl start dbconsole

Go to the file:
"%ORCLE_HOME%\install\portlist.ini" and see the port number of "Enterprise Manager Console HTTP Port (orcl) " after that go to URL like "http://hostname:portnumber/em".

iSQL*Plus
C:\>isqlplusctl start

Go to the file:

"%ORCLE_HOME%\install\portlist.ini" and see the port number of "iSQL*Plus HTTP port number" after that go to URL like "http://hostname:portnumber/isqlplus".

sql server 2005 change collation for database

Changing database collation may cause some problems especially
error-5030 The database could not be exclusively locked to perform
the operation.

To resolve this problem you should do the following:

Start->Run->cmd
C:\>osql -E //if it is trusted user then osql -U -P -S
1>use
2>GO

1>ALTER DATABASE djbadmin SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2>GO

1>ALTER DATABASE djbadmin COLLATE Latin1_General_CI_AS
2>GO

1>ALTER DATABASE djbadmin SET MULTI_USER
2>GO

Apex installation

1.Oracle Application Express Installation Requirements

1.1 Oracle Database Requirement

Oracle Application Express version 4.0 requires an Oracle database (Enterprise Edition, Standard Edition or Standard Edition One) that is release 10.2.0.3 or higher. Application Express 4.0 can also be used with Oracle Database 10g Express.

1.1.1 Checking the shared_pool_size of the Target Database

Note:Ignore this requirement if your configuration uses non-null values for the database initialization parameters SGA_TARGET (in Oracle Database 10g and 11g) or MEMORY_TARGET (in Oracle Database 11g).

Oracle Application Express requires the shared_pool_size of the target database to be at least 100 MB.
To check the shared_pool_size of the target database:

1. Start the database:

SQL> STARTUP

2. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):

SQL> SHOW PARAMETER PFILE;

3.  Determine the current values of the shared_pool_size parameter:

SQL> SHOW PARAMETER SHARED_POOL_SIZE

4.   If the system is using a server parameter file, set the value of the SHARED_POOL_SIZE initialization parameter to at least 100 MB:

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;

5.  Shut down the database:

SQL> SHUTDOWN

6.  Restart the database:

SQL> STARTUP

1.2 Browser Requirements
To view or develop Oracle Application Express applications, Web browsers must support Java Script and the HTML 4.0 and CSS 1.0 standards. The following browsers are required to develop applications in Oracle Application Express:

·         Microsoft Internet Explorer 7.0 or later version

·         Mozilla Firefox 3.5 or later version

·         Google Chrome 4.0 or later version

·         Apple Safari 4.0 or later version

Application Express applications can be developed that support earlier Web browser versions, including Microsoft Explorer 6.0.

1.3 Disk Space Requirement

Oracle Application Express disk space requirements are as follows:

·         Free space for Oracle Application Express software files on the file system: 450 MB if using English only download (apex_4_0_en.zip) and 1 GB if using full download (apex_4_0.zip).

·         Free space in Oracle Application Express tablespace: 185 MB

·         Free space in SYSTEM tablespace: 100 MB

·         Free space in Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB

1.4 Oracle XML DB Requirement

Oracle XML DB must be installed in the Oracle database that you want to use. If you are using a preconfigured database created either during an installation or by Database Configuration Assistant (DBCA), Oracle XML DB is already installed and configured.

See Also: Oracle XML DB Developer's Guide for more information about manually adding Oracle XML DB to an existing database

Tip: The installer does a prerequisite check for Oracle XML DB and will exit if it is not installed.

Tip: The installation of Oracle XML DB creates the user ANONYMOUS. In order for Oracle Application Express workspace provisioning to work properly, the ANONYMOUS user must not be dropped from the database.

1.5 PL/SQL Web Toolkit

Oracle Application Express requires the PL/SQL Web Toolkit version 10.1.2.0.6 or later. For instructions on determining the current version of the PL/SQL Web Toolkit, and for instructions on installing version 10.1.2.0.6, please review the README.txt file contained in the directory apex/owa.

2. install Oracle Application Express

 2.1 Recommended Pre-installation Tasks

Before installing Oracle Application Express, Oracle recommends that you complete the following steps:

1.      Shut down with normal or immediate priority the Oracle Database instances where you plan to install Oracle Application Express. On Oracle Real Application Clusters (Oracle RAC) systems, shut down all instances on each node.

2.      Back up the Oracle Database installation. Oracle recommends that you create a backup of the current Oracle Database installation before you install Oracle Application Express. You can use Oracle Database Recovery Manager, which is included the Oracle Database installation, to perform the backup.

3.      Start the Oracle Database instance that contains the target database. After backing up the system, you must start the Oracle instance that contains the target Oracle database. Do not start other processes such as the listener or Oracle HTTP Server. However, if you are performing a remote installation, make sure the database listener for the remote database has started.

2.2 Download and Install Oracle Application Express

To install Oracle Application Express:

1.      For installations where the development will be in English only, download the file apex_4.0_en.zip from the Oracle Application Express download page. Where the development will include languages other than English, download apex_4.0.zip from the Oracle Application Express download page. See:

http://www.oracle.com/technology/products/database/application_express/download.html

Note that the actual file name may differ if a more recent release has shipped since this document was published.

2.      Unzip downloaded zip file:

·         UNIX and Linux: Unzip apex_4.0.zip

·         Windows: Double click the file apex_4.0.zip in Windows Explorer

3.      Change your working directory to apex.

4.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

    And run the command:

     SQL>@apexins SYSAUX SYSAUX TEMP /i/

        When Oracle Application Express installs it creates three new database accounts:

·         APEX_040000 - The account that owns the Oracle Application Express schema and metadata.

·         FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.

·         APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.

2.3 Change the Password for the ADMIN Account

To change the password for the ADMIN account:

1.      Change your working directory to the apex directory where you unzipped the installation software.

2.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

3.      Run apxchpwd.sql. For example:

SQL>@apxchpwd

When prompted enter a password for the ADMIN account.

2.4 Restart Processes

After you install Oracle Application Express, you must restart the processes that you stopped before you began the installation, such as listener and other processes.

2.5 Configure the Embedded PL/SQL Gateway

2.5 .1 Running the apex_epg_config.sql Configuration Script

  The embedded PL/SQL gateway installs with the Oracle Database 11g. However, you must configure it before you can use it with Oracle Application Express. To accomplish this, you run a configuration file and unlock the ANONYMOUS account.

To run the apex_epg_config.sql configuration script:

1.      Change your working directory to the apex directory where you unzipped the Oracle Application Express software.

2.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

3.      Run apex_epg_config.sql passing the file system path to the base directory where the Oracle Application Express software was unzipped as shown in the following example:

On Windows:

@apex_epg_config SYSTEM_DRIVE:\TEMP

On UNIX and Linux:

@apex_epg_config /tmp

4.      Enter the following statement to unlock the ANONYMOUS account:

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

2.5 .2 Verifying the Oracle XML DB HTTP Server Port

 The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database. You can determine if the Oracle XML DB HTTP server is enabled by verifying the associated port number.
To verify the port number where the Oracle XML DB HTTP Server is running:

1.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

2.      Enter the following statement to verify the port number:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If the port number returns 0, the Oracle XML DB HTTP Server is disabled.

3.      To enable it, follow the instructions in 2.5 .3  Enabling Oracle XML DB HTTP Server.

2.5 .3  Enabling Oracle XML DB HTTP Server

The embedded PL/SQL gateway runs in the Oracle XML DB HTTP server in the Oracle database.
To enable Oracle XML DB HTTP server:

1.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

2.      Enter a statement similar to the following:

EXEC DBMS_XDB.SETHTTPPORT(port);

For example:

EXEC DBMS_XDB.SETHTTPPORT(8080);

2.6 Enable Network Services in Oracle Database 11g

By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 or 2. Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 or 2, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_040000 database user.

2. 6.1 Granting Connect Privileges

The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.

DECLARE

  ACL_PATH  VARCHAR2(4000);

  ACL_ID    RAW(16);

BEGIN

  -- Look for the ACL currently assigned to '*' and give APEX_040000

  -- the "connect" privilege if APEX_040000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid

  -- (for example, does not contain stale references to dropped users).

  -- If it does, the following exception will be raised:

  --

  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'

  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...

  --

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID

    FROM XDB.XDB$ACL A, PATH_VIEW P

   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND

         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);

   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 

     'connect') IS NULL THEN 

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 

     'APEX_040000', TRUE, 'connect'); 

  END IF;

EXCEPTION

  -- When no ACL has been assigned to '*'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

    'ACL that lets power users to connect to everywhere',

    'APEX_040000', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

/

COMMIT;

The following example demonstrates how to provide less privileged access to local network resources. This example would enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.

DECLARE

  ACL_PATH  VARCHAR2(4000);

  ACL_ID    RAW(16);

BEGIN

  -- Look for the ACL currently assigned to 'localhost' and give APEX_040000

  -- the "connect" privilege if APEX_040000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid

  -- (for example, does not contain stale references to dropped users).

  -- If it does, the following exception will be raised:

  --

  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'

  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...

  --

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID

    FROM XDB.XDB$ACL A, PATH_VIEW P

   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND

         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);

   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 

     'connect') IS NULL THEN 

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 

     'APEX_040000', TRUE, 'connect'); 

  END IF;

EXCEPTION

  -- When no ACL has been assigned to 'localhost'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',

    'ACL that lets power users to connect to everywhere',

    'APEX_040000', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');

END;

/

COMMIT;

2.7 About Managing JOB_QUEUE_PROCESSES


JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs.

If  JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully execute a script.

2.7.1 Viewing JOB_QUEUE_PROCESSES from SQL*Plus

SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'

(We had 1000)

2.7.2 Changing the Number of JOB_QUEUE_PROCESSES

You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus:
To update the number of JOB_QUEUE_PROCESSES:

1.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

2.      In SQL*Plus run the following SQL statement:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 

For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.

2.8 Configuring the SHARED_SERVERS Parameter

The embedded PL/SQL gateway uses the shared server architecture of the Oracle Database. To achieve acceptable performance when using the embedded PL/SQL gateway, ensure the SHARED_SERVERS database initialization parameter is set to a reasonable value (that is, not 0 or 1). For a small group of concurrent users, Oracle recommends a value of 5 for SHARED_SERVERS.
Consider the following example:

1.      Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog

SQL> CONNECT SYS as SYSDBA

Enter password: SYS_password

2.      Run the following statement:

ALTER SYSTEM SET SHARED_SERVERS = 5 SCOPE=BOTH;

Note: If database is not started by spfile shutdown and start it by spfile.

Go to the link to check if it works

http://hostname:port/apex/apex_admin

Oracle Data Miner(Installation)

Download oracle data miner from:
http://www.oracle.com/technetwork/database/options/odm/downloads/index.html


from sys user:
--create user:

SQL>CREATE USER dmuser IDENTIFIED BY dmuser

       DEFAULT TABLESPACE USERS

       TEMPORARY TABLESPACE TEMP

       QUOTA UNLIMITED ON USERS;

--grant necessary privileges

SQL>GRANT CREATE JOB TO dmuser;

SQL>GRANT CREATE PROCEDURE TO dmuser;

SQL>GRANT CREATE SEQUENCE TO dmuser;

SQL>GRANT CREATE SESSION TO dmuser;

SQL>GRANT CREATE SYNONYM TO dmuser;

SQL>GRANT CREATE TABLE TO dmuser;

SQL>GRANT CREATE TYPE TO dmuser;

SQL>GRANT CREATE VIEW TO dmuser;

SQL>GRANT EXECUTE ON ctxsys.ctx_ddl TO dmuser;

If the user will need to import or export data mining models, grant this additional privilege.

SQL>GRANT CREATE ANY DIRECTORY TO dmuser;

SQL>@dmshgrants.sql  

Value for 1:sh

Value for 2:dmuser

--from dmuser:

SQL>@dmsh.sql 

What is "INITRANS"?

I've searched theme about initrans for table...and I have found a very good topic.I want to share my knowledge about this subject with you...here it is:

by surachart

What is "INITRANS"?
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions: (on10g MAX_TRANS 's ignored )

* 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.

In general, you should not change the INITRANS value from its default.

Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system. 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.

If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block.

Therefore, in the CREATE INDEX or ALTER INDEX statements,

you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index

BLOCK SIZE and INITRANS :
=========================

Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITLs, the ITLs allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITLs for the block. The rule is "the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size".

ie : sizeof(INITIAL ITLs) < ( 50 % of the DATABASE BLOCK SIZE )

Examining ITL allocation (a brief experiment)
==========================

STEP 1 : Create a table with INITRANS 10.
SQL> CREATE TABLE TEST (I NUMBER) INITRANS 10;

STEP 2: Insert 1 record for testing purpose. A blockdump can be taken later.
SQL> INSERT INTO TEST VALUES (10);
SQL> COMMIT;

Step 3: Find out the block number and the file id for dumping the block:
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

Step 4: Dump the block:
SQL> ALTER SYSTEM DUMP DATAFILE x BLOCK y;

Step 5: Open the dump trace file located in USER_DUMP_DEST directory and check the following:

Block header dump: 0x0040ad12
Object id on Block? Y
seg/obj: 0x5881 csc: 0x5dc.33121987 itc: 10 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0003.05b.0000009b uba: 0x008005f3.005e.42 --U- 1 fsc 0x0000.33121989
0x02 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x06 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

Example:
SQL> CREATE TABLE TEST (I NUMBER) INITRANS 10;

Table created.

SQL> INSERT INTO TEST VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------------------------------------------
4 36477

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36477;

System altered.

Check tracefile:

Block header dump: 0x01008e7d
Object id on Block? Y
seg/obj: 0x18fec csc: 0x00.278431ed itc: 10 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1008e79 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.019.0017e02b 0x01800ce1.17d9.08 --U- 1 fsc 0x0000.278431ef
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
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
The above dump confirms that the ITL allocation worked as specified. However this may not be the case for all the valid values for INITRANS.

Assuming that your database has 2k block size (db_block_size=2048), if you set INITRANS to 45, only 41 ITLs will be found in the dump because of the consideration of the block size. So for a 2k block size, any value greater than 41 for INITRANS would result only in allocating 41 ITL entries in the block header.

Likewise , if you have a 4k block size, the maximum number of inital ITL entries are only 83. Any value over and above 83 for INITRANS are ignored.

BLOCK SIZE NO OF ITLs allocated in block header
=============================================

2048 41
4096 83
8192 169

As I mentioned before, not more than 50 % of the block is utilized for ITLs. For a 2k block sized database, the maximum ITLs during intial allocation is 41. Each ITL takes 24 bytes of space in the header. So

For 2k Block: 41 * 24 = 984 bytes. This is 48 % of the total block size.

For 4k block: 83 * 24 = 1992 bytes.

For 8k block: 169 * 24 = 4056 bytes. (Same 48%)

Follow the above 5 steps to check the ITL allocation for different block sizes.
==================================================================================

We can modify INITRANS attribute by use ALTER

EXAMPLE
SQL> ALTER TABLE TABLE_NAME INITRANS 10;

Example: Test on 8k ... initrans =255
SQL> CREATE TABLE TEST2 (I NUMBER) INITRANS 255;

Table created.

SQL> INSERT INTO TEST2 VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test2;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 36485

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36485;

System altered.

Check trace file:

Block header dump: 0x01008e85
Object id on Block? Y
seg/obj: 0x18fed csc: 0x00.2784367f itc: 169 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1008e81 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.0011e01c 0x01800c72.154d.05 --U- 1 fsc 0x0000.27843681
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
.
.
.
0xa5 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa6 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa7 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

So, use shell command to help

$ grep fsc oradb1_ora_15394.trc | wc -l
169

So, 8k block: 169 * 24 = 4056 bytes. (Same 48%)... not 255

You should know before modify =>

Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:

* For a nonpartitioned table, the values you specify override any values specified for the table at create time.

* For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.

* For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause.