Application Design and ORA – 4031

We have moved to new site. Please find article at

http://askdba.org/weblog/?p=92

Physical Corruption: ORA-1578 -Part 1

We have moved to new site. Please find article at

http://askdba.org/weblog/?p=91

Deciding "PROCESSES" parameter value

Many times we observe following error which corresponds to low setting of processes parameter.

$ oerr ora 20

00020, 00000, “maximum number of processes (%s) exceeded”

// *Cause: All process state objects are in use.

// *Action: Increase the value of the PROCESSES initialization parameter.



This is mostly seen in new database installation as the processes parameter is not taken into account.

According to Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-03

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.



This nrings us to a question that “Can we set up a large value for this parameter so that user’s are not affected and we do not have to restart the system (PROCESSES is a static parameter) ?”

Answer is NO.



You need to calculate ideal value for the processes parameter by taking care of Default Background processes (Also looking at number of Archiver processes, parallel_max_servers) and total number of user connections.

This is required as otherwise you will be required to change the value of SEMMNS parameter (for UNIX only) i.e Max number of semaphores in system and along with this , it will increase the “Overhead” Memory in SGA.

Find below explanation for the above points.

1)SEMMNS parameter

Each Oracle instance needs to have a set amount of semaphores. The total amount of semaphores required is derived from the ‘processes’ parameter specified inOracle instance init.ora file. As more instances and/or databases are added the OS kernel parameter SEMMNS will need to be adjusted accordingly.

According to Metalink Note:153961.1



In Oracle7 , number of semaphores required by an instance is equal to ‘processes’ parameter in the init.ora for the instance.For Oracle8, Oracle8i, Oracle9i and Oracle10g, number of semaphores required by an instance is equal to 2 times the setting of the ‘processes’ parameter in the init.ora for the instance.

However, Oracle only momentarily grabs 2 X ‘processes’ and then releases half at instance startup.



2)Overhead Memory



Parameters like db_files, open_cursors and processes contribute to the variable part of SGA. This can be seen by issuing “Show SGA” command. You will see that “Variable Size” will be more then sum of “Shared Pool + Large Pool + Java Pool”. This is attibuted to the value of these parameters.

Please note that in case you specify a low value for SGA_MAX_SIZE, you will see Oracle bumping the value to higher value so as to accomodate high value of Overhead memory.

Staring from 10g, Overhead memory is accomodated in shared_pool_size.

e.g If you specify SHARED_POOL_SIZE as 200 MB and your internal overhead is 100 Mb, then your actual shared pool value available to instance is only 100Mb.

You can read Note:351018.1 – Minimum for SHARED_POOL_SIZE Parameter in 10.2 Version for more information.

ERRORSTACK- Oracle Debugging Event

We have moved to new site. Please find article at

http://askdba.org/weblog/?p=89

Changing Database Characterset With Export/Import Utilities

Recently there was a thread on Oracle Forums regarding change of Database characterset from WE8ISO8859P1 to AL32UTF8. User wanted to upgrade his database from 9i to 10g and also change the database characterset. He was looking forward to use either Export/Import utilities or Database Upgrade Assistant for the same. There were few responses to this question with responses from few people (NLS being a dry topic for most of DBA’s )

To make this issue simpler, we will keep this problem to only change of Database characterset. Export/Import is considered to be a Straightforward approach but we should understand that this should not be the first step.

As per Globalization Support Guide, there are two steps to change the characterset

1)Data Scanning

2)Data Conversion

Export/Import comes under second step, i.e Data Conversion . Data Scanning is a very important step for any Characterset Change and should not be compromised. Failure to do so can result in data loss or data corruption.

In case you are planning to use Export/Import ,Approach should be to first run csscan.

Csscan utility helps to identify if data is Changeless (Data to be stored in same way in new Characterset), Convertible (Data have different code points in new Characterset and needs to be converted), Truncation (Data will be truncated in new Characterset , so column needs to be modified) and Lossy (Data is not understood by new characterset and will be lost on Conversion).

In case only Changeless and Convertible data is there, you can go ahead and use export/import.

If only changeless data is there, then you need to simply use “Alter database ” statement or csalter (in 10g) to change the characterset.

In case you have lossy data, then you would be required to identify what the data is, correct it and then change the characterset.

You should read the following Metalink Notes and Documentation before attempting to change Database Characterset

Oracle® Database Globalization Support Guide10g Release 2 (10.2)Part Number B14225-02

Note:227338.1 – Character Set Scanner – Frequently Asked Questions

Note 257736.1 Changing the Database Character Set – an extended overview

Note 260192.1 Changing WE8ISO8859P1/WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8

Tweaking _Shared_pool_reserved_min_alloc and ORA – 4031

We have moved to new site. Please find article at

http://askdba.org/weblog/?p=86

Undo Tablespace Sizing

We have moved to new site. Please find article at

http://askdba.org/weblog/?p=85