Skip to main content


Showing posts from 2009

PCP -- Parallel Concurrent Processing

I often get queries about PCP(Parallel Concurrent Processing) and its configuration. I will try to include all such details in this blog which will help configure PCP step by step.

PCP makes sense on a RAC environment else there is no point having PCP atleast for me.

POINT1: Check if you require PCP to load balance or to be used as failover.

If both the nodes then, Change the names of the concurrent managers say SM:Node A and Assign PRIMARY and SECONDARY Nodes to that manager.

Then duplicate the record and change the name to SM:Node B for the 2nd manager and change the PRIMARY and SECONDARY Nodes
Note: Ensure the hostname should be only the name of the host from uname -a command, not the virtual hostname.

Do it for all the managers in the instance including tx mgr, internal mgr EXCEPT Internal Monitor.

Following configuration details are from metalink document 362135.1 section 3.12.

Check PCP Prerequisites

It is assumed that you have more than one Concurrent processing tiers in your environmen…

ORA-01950: no privileges on tablespace

SQL> create table mith( n number primary key, l varchar2(60));
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Whenever a new user is created it requires certain previlages like connect, create any table, view etc along with grant on table space. To get rid of this error you can use the following sq,




Text Index -- Printjoins

I am trying to create a test case for one of my clients having issues with text index.

Text index comes with lot of options for language, lexer etc. It is not as simple as creating a normal index. Through my experience i have seen multiple issues with text indexes and one of which is with printjoins.

Details of Text index and its options can be found at,

and look at my other post on text index,

User complains that when they search with . character it does not consider . as character. Look at the example below(tested on


insert into mith values( 1, 'nisha.mithun');
insert into mith values( 2, 'nisha mithun');
insert into mith values( 3, 'nisha mithun nisha mithun');
insert into mith values( 4, 'hello-world');
insert into mith values( 5, 'hello@world…

Oracle Text Index -- Real time implementation


Create a context index on a table which has around 100 million records adds upto a size of 120GB. Index creation takes more than 20 days to complete. Along with 100 million records there are around 50 million more records which will be added to this table which makes the table grow to a size of 180GB and with this data index creation is expected to take even more time.

Following steps were being used to create the index.
set timing on time onexec Ctx_Ddl.Create_Preference('SCB', 'BASIC_WORDLIST'); exec ctx_ddl.set_attribute('SCB', 'wildcard_maxterms',1 5000) ;exec ctx_ddl.set_attribute('SCB', 'substring_index', 'TRUE') ;execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/oat_lg/rrotmedb/archive/ctx/'); execute CTXSYS.CTX_OUTPUT.START_LOG('g_SCB_IDX_gmis.LOG'); exec ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID); drop index NORKOM56.SCB_TRANS_IDX force;CREATE INDEX NORKOM56.s…

JVM Performance tuning with 11i

Following are the questions answered to one of the mailing lists. Here it is shared.....

Usually Performance issues with JVM's are related either to,

1. JDK Bugs
2. JVM thread deadlocks and memory leaks 3. Application request taking too long to process ex. long running query, poor sql, db issues or java coding 4. Poor performing Garbage collection
5. Incorrect JVM startup settings

The amount of memory that should be allocated to each JVM depends on the number of JVM's, the number of users on the system, the amount of physical memory on the middle tiers, the habits of the users and the version of the JDK used by the system. THe version of JDK being used makes a large difference to how the memory and the number of JVM's should be sized. JDK 1.3 performs better with more JVM's and less heap while JDK 1.4/1.5 performs better when there are less JVM's and more heap. Thumb rule is to have 1JVM per 2 cpu's with increased heap parameter for better performance. As a minimum …

Forms Socket/Servlet

Following are the queries answered by me in one of the forums.

1. Will it be possible that the forms tier load also will be balanced by Hardware load balancer ? Before we know the answer for this question, i guess its better to know the forms architecture. Forms in Oracle Applications can be configured in 2 modes.

a. Socket Mode and
b. Servlet Mode.

a. Socket Mode: By default forms 6i comes with Socket Mode install. If customer hasn't done any changes then he would still be using Socket Mode. In socket mode then forms cannot be loadbalanced using hardware load balancer. In this case you need to use Forms Metric Server Load Balancing.

For configuration details refer to document: 217368.1 Section 2.4. Forms Server / Metrics Layer Load Balancing.
Forms socket mode connections are always faster compared to servlet mode(if hardware resources are infinite then this should be of little concern). This is the only reason why customers use forms socket mode else Servlet mode is…

Concurrent user logins in 11i

Following query will give you number of concurrent users logged into the system.

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

Run the following queries:-
This will give the number of users on the system in the past 1 hour.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

This will give the number of users on the system in the past 1 day.

select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

This will show the activity in the last 15 minutes.

select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",
user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

Check the No…


Recently i posted my views for the questions on a forum, having it here for discussion.....

Following are my understanding and only my personal view.

1. Can anyone please clarify what's the diff in SAP and EBS.

SAP and EBS are ERP solutions as you know, business wise its just a comfort factor which makes customer to choose either SAP or Oracle. To be straight SAP doesnt allow you to do customizations as it can be done in Oracle, this is what makes SAP tightly integrated less error prone. Both use their own way of programming which is the answer for your second question below. SAP has its own standards and Oracle too has its legacy. For techincal details you can refer to the following link,

2. In SAP ABAP is used for programming purposes. what is equivalent in EBS to that.?

ABAP is a programing language created by SAP and it is similar to any other programming language. Its quite similar to COBOL. This was first used as a reportin…


How to Setup Auditing
Do the following to set up auditing on your database:

1. Modify the "init.ora" file, usually located in the "$ORACLE_HOME/dbs"
directory to enable the AUDIT_TRAIL parameter and stop/start the instance to
make the parameter effective.

AUDIT_TRAIL can be set to one of the following four values:

--> DB/TRUE enables systemwide auditing where audited records are written to
the database audit trail, the SYS.AUD$ table
--> OS enables systemwide auditing where audited records are written to the
operating system's audit trail

--> DB_EXTENDED enables systemwide auditing as DB/TRUE does; in addition, it
populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table

DB_EXTENDED can be used starting with 10g only. More informations can be found in
Note 249438.1 - 10G New Value DB_EXTENDED for the AUDIT_TRAIL Parameter

--> NONE/FALSE disables auditing. This is the default value.


2. If yo…

DB Health Check

Table of Contents

1. Introduction
2. Parameter file
3. Controlfiles
4. Redolog files
5. Archiving
6. Datafiles
6.1 Autoextend
6.2 Location
7. Tablespaces
7.1 SYSTEM Tablespace
7.2 SYSAUX Tablespace
7.3 Locally vs Dictionary Managed Tablespaces
7.4 Temporary Tablespace
7.5 Tablespace Fragmentation
8. Objects
8.1 Number of Extents
8.2 Next extent
8.3 Indexes
9. AUTO vs MANUAL undo
9.1 AUTO Undo
9.2 Manual undo
10. Memory Management
10.1 Pre-Oracle 9i
10.2 Oracle 9i
10.3 Oracle 10g
10.4 Oracle 11g
11. Logging & Tracing
11.1 Alert File
11.2 Max_dump_file_size
11.3 User and core dump size parameters
11.4 Audit files
11.5 Sqlnet

1. Introduction
This article explains how to perform a health check on the database. General
guidelines are given on what areas to investigate to get a better overview on
how the database is working and evolving. These guidelines will reveal common
issues regarding configuration as well as problems that may occur in the future.

The areas investigated here are mostly b…

India Localization






"6491231 REF : 6059025 - MULTINODE : PATCH TOOL …

What are Flexfields


There are 2 types of flex fields

Key Flexfield
Descriptive Flexfield

Key Flexfield:

Descriptive Flexfield:



Concurrent Requests Purge Program -- What happens?

The following tables will be purged:
This table contains a complete history of all concurrent requests.
This table records arguments passed by the concurrent manager to each program
it starts running.
This table records when requests do not update database tables.
This table records information about Oracle Applications and operating system
This table collects runtime performance statistics for concurrent requests.
This table contains the concurrent program performance statistics generated by
compute these statistics.

Stores the post requ…

eMail Center -- What is it?

Oracle eMail Center is a comprehensive solution for managing high volumes of
interaction by automatically replying to certain email inquiries as well as routing
with cross application functionality.

Oracle eMail Center increases customer satisfaction and reduces customer attrition
reducing agent turnover.


the Message Component, while viewing and r…

Did you know --- Oracle Database 9i

remainder of the database.