Q: How to enter a single quotation mark in Oracle?
Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.
Method 1
The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides.
SELECT 'test single quote''' from dual;
The output of the above statement would be:
test single quote'
Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character.
This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour.
Method 2
I like this method personally because it is easy to read and less complex to understand. I append a single quote character either from a variable or use the CHR() function to insert the single quote character.
The same example inside PL/SQL I will use like following:
DECLARE
l_single_quote CHAR(1) := '''';
l_output VARCHAR2(20);
BEGIN
SELECT 'test single quote'||l_single_quote
INTO l_output FROM dual;
DBMS_OUTPUT.PUT_LINE(l_single_quote);
END;
The output above is same as the Method 1.
Now my favourite while in SQL is CHR(39) function. This is what I would have used personally:
SELECT 'test single quote'||CHR(39) FROM dual;
The output is same in all the cases.
Now don't ask me any other methods, when I come to know of any other methods I will share here.
Showing posts with label FAQ. Show all posts
Showing posts with label FAQ. Show all posts
Oracle Index and Like clause
Topic: Beginners Level
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.
Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.
For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.
table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)
In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.
The query for such a situation would be:
SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';
Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.
The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.
Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.
So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.
How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.
So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.
Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.
The power of Oracle certification
Author: Michael J. Hillenbrand
Step aside … I am certified!
Step aside … I am certified!
Just two days after I proudly framed and hung my first Oracle OCP certification on my cubicle wall, one of my colleagues hung a Dilbert cartoon right next to it. The Dilbert went something like this.
In the first frame Dilbert is having trouble with his computer. A superhero in tights with a Capital C on his chest tells Dilbert "Step away from the server, I am certified!" In the next frame, our superhero sits down in front of the computer, Dilbert now watching over his shoulder, and says "I SUMMON THE VAST POWER OF CERTIFICATION!" In the last frame, the superhero realizes he doesn't know what to do and admits, "This is embarrassing -- that's all I remember from the classes."
The certification debate
Whether or not to get certified has been a widely debated topic for as long as I can remember. On one side of the fence are those that see great value in certification, that claim that there is no better way to measure a candidate's technical ability. On the other side of the fence are those that see absolutely no value in certifications, that argue that a piece of paper earned by memorizing and taking a multiple-choice test does not prove or replace the value of true experience and real-world knowledge.
As for me, you could say that I am sitting directly on the fence, as I believe both arguments are somewhat valid. In this article, I will discuss the certification process and examine some of the arguments for and against certifications.
The certification process
First and foremost, you need to fully understand the process of getting certification before you can truly measure its value.
Let's first take a look at the certification process for Oracle DBAs. The Oracle certification process has certainly come a long way since I earned my first Oracle certification. Years ago (I hate to admit just how many years) all anyone had to do to get 7.3 certified was take and pass a single multiple-choice test. I took mine for free at an IOUG conference in Philadelphia. For my efforts I received a certificate, a pin and a cool logo that claimed me as an "Oracle Certified Professional," or OCP for short. I later upgraded that certification to 8i OCP by taking yet another single upgrade test. This is the last DBA certification I will likely possess for a couple of reasons. For one, my role as DBA has changed as I entered into management, and for another, Oracle has made the process a lot more difficult.
For all intents and purposes, the days of the single-test OCP certification are over. The process to become OCP has become a lot more involved and a lot more difficult to attain. Starting with Oracle 8i, to fully qualify for an OCP certification you had to pass a series of five tests. Soon after this, Oracle decided to require you to also take one of its instructor-led DBA courses for certification. While there is arguably some benefit to ensuring that students take a class, let's keep this in business perspective. The requirement was nothing but a marketing ploy to get people into the classrooms, and it did not bode well for those that wanted to earn certification and were not financially sponsored to do so through their companies.
Starting with Oracle 9i, the options for certification have changed yet again, and Oracle has placed even more emphasis on its own classes (good for Oracle):
- OCA -- Oracle Certified Associate. Requires passing two tests and no training courses.
- OCP -- Oracle Certified Professional. Requires OCA, one instructor-led course, one exam and one hands-on course.
- OCM -- Oracle Certified Master. Requires OCP, two advanced courses, a two-day hands-on exam and one additional hands-on course. While this is probably the best measure of ability, it is a very cost-prohibitive option.
While the OCA seems to be a viable option for the average DBA, those who wish to pursue certification on their own, without company sponsorship, will find it financially next to impossible to earn an OCP (two courses) or OCM (five total courses).
Justifying Oracle certification
Perception surrounding the true value of certifications really depends on who you ask. Those with certifications will usually argue that they are important; those without them will most likely turn up their noses at them. What most IT folks do agree upon is this: certifications do hold some amount of value, but they can never replace the relevance of good experience. No matter what position you take, you should realize that certifications must be important to somebody, since the certification industry continues to thrive and grow.
The justification that people will use to get certified is normally related to their own expectations for return on investment. In turn, expectations will ultimately determine the value of certification for each individual. If you are getting certified for the wrong reasons, you are destined to be disappointed with the end results. With that said, let's examine some of the reasons people get certified.
"Certification will help me get a job."
Having certification may help you get an interview, but it will not necessarily help you get a job. Recruiters look for two things in DBAs: core DBMS knowledge and hands-on experience. To measure knowledge, some companies and/or agencies will use certifications to pre-qualify candidate resumes, accepting those that have it and rejecting those that don't. Others will drill candidates on technical questions in a face-to-face interview, whether or not they have a certification.
I typically recommend certification for anybody trying to enter the DBA field, as it will often help get you past HR (human resources) and the recruiters and get a foot in the door for an interview. If you are an aspiring DBA with little experience and searching for that first DBA job, OCA certification OCA is definitely a good first step. I have been recruiting DBAs for 15 years now, and while I do not look for certification as a prerequisite for any position, I have to admit that, all other things being equal, it will give you a slight edge over somebody without it.
While nothing can replace the hands-on experience, problem solving and soft skills needed to be a DBA, being certified is a good way to prove that you understand the basic concepts of Oracle, are willing to go the extra mile to learn, and that you take your career seriously.
"Certification will get me promoted."
If you are getting certified in hopes of instant financial gratification, forget it. Getting certified only proves that you know the textbook concepts or perhaps even that you are merely a good test-taker. Some of my very best DBAs were not certified, and some of them couldn't pass a test if their life depended on it! While I always supported certifications, I would never consider a certification a reason to hand out a promotion. Being in the DBA business for more than 20 years now, I know more than anybody that a certification can never replace the value of good experience, nor can it measure one's ability to apply knowledge in the real world.
The reality is that there are some companies that put more emphasis on certifications. Some may use incentives to promote certification, and some may even require it. If you are providing services to a customer (like consulting or remote DBA services), then that customer might be more comfortable knowing that you are certified in the technologies you are supporting.
"Certification makes me better than you!"
This is hands down the worst idea one could entertain and is probably the biggest reason for the argument against certification. I once knew a DBA who proudly flaunted her Oracle Masters certificate around to her peers. At the time, the Oracle Master was nothing but proof that you attended so many classes and did not even involve passing any tests. Since this particular individual had little experience and no clue how to manage databases in the real world, her flaunting did not earn her favor with her peers and eventually led to her demise in the organization.
Do not be pompous about being certified and never think that you are better than somebody else just because you hold a paper certificate. Certificates are nice to have, but they do not equate to a measure of your ability or level of importance in an organization.
"Certification gives me personal satisfaction!"
This is perhaps the best reason to get certified. If you get a sense of accomplishment and confidence from getting certified and are looking to refresh your knowledge or to stay on top of the latest technologies, then by all means do it. The important thing is to do it for the right reasons. I have to admit that I kept that first OCP certificate hanging in my cubicle for quite some time, not to rub it in any of my colleagues' faces, but rather to remind myself that I earned it and was proud of my accomplishment. I knew that I had the experience and capability to apply my knowledge, so I didn't really mind the aforementioned Dilbert cartoon that was displayed directly under it.
Oracle architecture v DB2 architecture
Author: David (DBA)
This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2.
Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2.
I do have some comments I would like to add:
1) IBM says:
This compares best to Oracle tnsnames.ora file. I don't understand why IBM didn't draw this comparison.
2) IBM says:
While I get IBMs point (that you have to create tablespaces manually after the create database command) This is not technically true. Frist, when you issue a create database command in Oracle you explicitly specify the creation of a temp tablesapce. Second, after the database creation all other tablespaces you have created can be used without being "assigned to users" simply by using them in the storage clause of the object creation statement.
3) IBM Says:
Oracle does not have the SMS concept for its storage model but its data files are similar to DB2 DMS table spaces.
This is not entirely true. There is something similar (but not completely) in Oracle known as OMF(9i)/ASSM(10g). I will not go into detail about what these features do, but I will say that while OMF / ASSM files are managed by Oracle and no the Operating System, they do simplify the administration of tablespaces to an almost automatic level.
4) IBM Says:
Again, not entirely true. While less flexible, Oracle 9i/10g allows for multiple db buffers with different block size. The limitation is one buffer per one block size while in DB2 it is one buffer per tablespace.
This article makes direct comparison between the Oracle architecture (instance, databases, physical files, network, configuration files, etc) and the DB2 architecture thus allowing Oracle DBAs to understand the similarities and differences between Oracle and DB2.
Because of its comparative nature (showing you how the same things work in DB2 compared to Oracle) I think its a great first read if you are an Oracle DBA new to DB2.
I do have some comments I would like to add:
1) IBM says:
DB2 does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine.This compares best to Oracle tnsnames.ora file. I don't understand why IBM didn't draw this comparison.
2) IBM says:
Every Oracle database contains a table space named SYSTEM, which Oracle creates automatically when the database is created. Other table spaces for user, temporary and index data need to be created after the database has been created, and a user needs to be assigned to these table spaces before they can be used.While I get IBMs point (that you have to create tablespaces manually after the create database command) This is not technically true. Frist, when you issue a create database command in Oracle you explicitly specify the creation of a temp tablesapce. Second, after the database creation all other tablespaces you have created can be used without being "assigned to users" simply by using them in the storage clause of the object creation statement.
3) IBM Says:
DB2 table spaces can be classified as SMS (system-managed spaces) or DMS (database-managed spaces). SMS table spaces are managed by the operating system and can only be directories. They grow automatically as needed; thus SMS provides good performance with minimum administration.Oracle does not have the SMS concept for its storage model but its data files are similar to DB2 DMS table spaces.
This is not entirely true. There is something similar (but not completely) in Oracle known as OMF(9i)/ASSM(10g). I will not go into detail about what these features do, but I will say that while OMF / ASSM files are managed by Oracle and no the Operating System, they do simplify the administration of tablespaces to an almost automatic level.
4) IBM Says:
As indicated earlier, Oracle's data buffer concept is equivalent to DB2's bufferpool; however, DB2 allows for multiple bufferpools to exist. There is no predefined number of bufferpools that you can create, and they can have any name.Again, not entirely true. While less flexible, Oracle 9i/10g allows for multiple db buffers with different block size. The limitation is one buffer per one block size while in DB2 it is one buffer per tablespace.
Data warehouse v Database
Author: Nishith
| Data warehouse vs. database is a long and old debate. In this article I am going to discuss the difference between data warehouse and database. I will also discuss that which one is better to use. Similarities in Database and Data warehouse:
|
Difference between Database and Data warehouse:
So is that ‘Data warehouse' really different from the tables in you application? And if the two aren't really different, maybe you can just run your queries and reports directly from your application databases!
Well, to be fair, that may be just what you are doing right now, running some end-of-day reports as complex SQL queries and shipping them off to those who need them. And this scheme might just be serving you fine right now. Nothing wrong with that if it works for you.
But before you start patting yourself on the back for having avoided a data warehouse altogether, do spend a moment to understand the differences, and to appreciate the pros and cons of either approach.
- The Application database is not your Data Warehouse for the simple reason that your application database is never designed to answer queries.
- The database is designed and optimized to record while the data warehouse is designed and optimized to respond to analysis questions that are critical for your business.
- Application databases are On-Line Transaction processing systems where every transition has to be recorded, and super-fast at that.
- A Data Warehouse on the other hand is a database that is designed for facilitating querying and analysis.
- A data warehouse is designed as On-Line Analytical processing systems . A data warehouse contains read-only data that can be queried and analyzed far more efficiently as compared to regular OLTP application databases. In this sense an OLAP system is designed to be read-optimized.
- Separation from your application database also ensures that your business intelligence solution is scalable, better documented and managed and can answer questions far more efficiently and frequently.
Data warehouse is better than a database:
The Data Warehouse is the foundation of any analytics initiative. You take data from various data sources in the organization, clean and pre-process it to fit business needs, and then load it into the data warehouse for everyone to use. This process is called ETL which stands for ‘Extract, transform, and load'.
Suppose you are running your reports off the main application database. Now the question is would the solution still work next year with 20% more customers, 50% more business, 70% more users, and 300% more reports? What about the year after next? If you are sure that your solution will run without any changes, great!! However, if you have already budgeted to buy new state-of-the-art hardware and 25 new Oracle licenses with those partition-options and the 33 other cool-sounding features, you might consider calling up Oracle and letting them know. There's a good chance they'd make you their brand ambassador.
Creation of a data warehouse leads to a direct increase in quality of analyses as you can keep only the needed information in simpler tables, standardized, and denormalized to reduce the linkages between tables and the corresponding complexity of queries.
A data warehouse drastically reduces the cost-per-analysis and thus permits more analysis per FTE.
It's probably more sensible and simpler to create a new data warehouse exclusively for your needs. And if you are cash strapped, you could easily do that at extremely low costs by using excellent open source databases like MySQL.
How do I delete an O/S file from within PL/SQL
The pl/sql package utl_file allows me to create, read and write flat files at the O/S level on the server. Also the dbms_lob package allows me to read files from the server and load them into the database. But how do I delete an O/S file from within pl/sql after I have finished with it.
One 'near-soultion' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file.
One 'near-soultion' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file.
Another approach is to write a short piece of Java, which can then be called from PL/SQL. Java currently offers far more flexibility than PL/SQL when dealing with O/S files, for example you could use Java to invoke and load a directory listing from PL/SQL so that you know what files exist for deletion. (See further reading).
A pure simple PL/SQL solution, however, appears to exist in the dbms_backup_restore package. This is virtually undocumented (other than in the script dbmsbkrs.sql) in 8.1.7, but contains a simple deletefile() procedure.
begin
dbms_backup_restore.deletefile('/tmp/temp.txt');
end;
/ This appears to work as required with no side-effects.
Update for 9.2
In version 9.2, the utl_file package has been enhanced with the fremove() procedure for deleting a file.
Note: The call to dbms_backup_restore.deletefile() always gives the message: “PL/SQL procedure successfully completed” even if the path or file is not present or file or path name is invalid or if open. So we can not depend on this package's output.
Read file names using PL/SQL
In 10g there is a new procedure hidden away in the undocumented DBMS_BACKUP_RESTORE package. It's called searchfiles, which is a bit of a giveaway and appears to have been introduced for the new backup features in 10g, as RMAN now needs to know about files in the recovery destination.
Calling this procedure populates an in memory table called x$krbmsft, which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful).
The code sample below will only really run as sys, due to the select from x$krbmsft, it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the second parameter is of no consequence to us and should be left as NULL.
Even though they are IN OUT, testing shows they don't appear to change. The first parameter is the string to search for, in much the same format as you would pass in a call to dir (Windows) or ls (Unix).
DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*.csv'; -- /tmp/*.csv
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/
This procedure appears to raise no exceptions, passing an invalid search string, such as a non-existant path or one with no permissions, simply results in an empty x$krbmsft. However, if the database parameter db_recovery_file_dest is not set, you will get ORA-19801.
Interestingly, this procedure recursively searches sub directories found in the search string. So passing a string of 'C:\windows' (for example) populates x$krbmsft with not only the files found in that directory but all the files found in all directories beneath, such as C:\windows\system32.
As x$krbmsft is an in memory table, you have been warned! Calling this procedure on a directory with thousands of sub directories and files has the potential to consume large amounts of memory (or more likely just generate an exception).
Calling this procedure populates an in memory table called x$krbmsft, which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful).
The code sample below will only really run as sys, due to the select from x$krbmsft, it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the second parameter is of no consequence to us and should be left as NULL.
Even though they are IN OUT, testing shows they don't appear to change. The first parameter is the string to search for, in much the same format as you would pass in a call to dir (Windows) or ls (Unix).
DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*.csv'; -- /tmp/*.csv
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/
This procedure appears to raise no exceptions, passing an invalid search string, such as a non-existant path or one with no permissions, simply results in an empty x$krbmsft. However, if the database parameter db_recovery_file_dest is not set, you will get ORA-19801.
Interestingly, this procedure recursively searches sub directories found in the search string. So passing a string of 'C:\windows' (for example) populates x$krbmsft with not only the files found in that directory but all the files found in all directories beneath, such as C:\windows\system32.
As x$krbmsft is an in memory table, you have been warned! Calling this procedure on a directory with thousands of sub directories and files has the potential to consume large amounts of memory (or more likely just generate an exception).
How can I identify which index represents which primary or unique key constraint?
The connection between constraints and the indexes which are used to check these constraints for the current user can be described by this query:
select --+ rule
o.owner index_owner,
o.object_name index_name,
n.name constraint_name
from sys.cdef$ c, dba_objects o, sys.con$ n
where c.enabled = o.object_id
and c.con# = n.con#
and n.owner# = uid;
If you leave away the condition and n.owner# = uid you get all the constraints. You may further limit this query to your constraint name by adding the condition and n.name = 'your_constraint_name'.
Why can indexes and constraints be so different? In particular, you may use for example an index on columns (c, a, b) to enable a unique constraint on columns (a, b, c). Remember a constraint is a logical structure whereas an index is a physical one. So a unique or a primary constraint just describe the uniqueness. If (c, a, b) is unique then all other permutations are unique as well.
Further, these indexes may also be non-unique. You need this if you have a deferred constraint that is checked only at commit time. If you would insist on a unique index the attempt to insert duplicate values would fail before the commit although another command may have undone the duplicate entry.
This original query was contributed to comp.databases.oracle.server by Thomas Kyte.
Difference between unique index and unique constraints-Oracle
A constraint is defined by Oracle as a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.
The difference between a unique index and a unique key/primary key constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command: create index or as part of a create table with primary key or unique key constraints or an alter table command that adds these constraints (see SQL Manual).
Briefly the constraints are:
Not Null - Column value must be present.
Unique Key - Column(s) value(s) must be unique in table or null (see note below).
Primary Key - Unique key + Not Null which equates to every column in the key must have a value and this value is unique so the primary key uniquely identifies each and every row in the table.
Foreign Key - Restricts values in a table column to being a value found in the primary key or unique key Constraint on the referenced table (parent/child relationship).
Check - Tests the column value against an expression (rule).
Technically it would be possible for Oracle, to support primary key and unique key constraints without using an index at all. In the case of a unique key or primary key constraint Oracle could perform a full table scan to check for the presence of a key value before performing the insert but the performance cost of doing this for anything other than a very small table would be excessive probably rendering Oracle useless.
Prior to Oracle 8 if you defined a primary key or a unique key constraint the Oracle RDBMS would create a unique index to support enforcement of the constraint. If an index already existed on the constrained columns Oracle would use it rather than define another index on the same columns.
Starting with Oracle version 8 Oracle has the ability to enforce primary key and unique key constraints using non-unique indexes. The use of non-unique indexes supports deferring enforcement of the constraint until transaction commit time if the constraint is defined at creation time as deferrable. Also starting with version 8 Oracle has the ability to place constraints on tables where the existing data does not meet the requirements imposed by the constraint through use of a novalidate option (see SQL Manual).
The practical difference between using a unique index to support data integrity and a unique key or primary key on the same columns since Oracle will build an index to support the constraint if you do not is that you can define foreign key constraints when the primary key or unique key constraint exist. Also in the case of a primary key constraint Oracle will convert the columns in the constraint to be not null constrained when it is added to meet the primary key requirement to uniquely identify each and every row in the table.
There is no such restriction on a unique index. The primary key and unique key constraints along with foreign key constraints that reference them also provide a form of documentation on the relationships between objects.
The Oracle RDBMS Data Dictionary views All/DBA/USER_CONSTRAINTS and ALL/DBA/USER_CONS_COLUMNS may be used to locate constraints on a table and the columns being constrained.
If you drop or disable a primary key or unique key constraint that is supported by a unique index the index is dropped with the constraint. If a non-unique index is used to support the constraint the index is not dropped with the constraint. This second condition is effective only with version 8 and higher.
Note – Unique key constraints allow the constrained column to be NULL. Nulls values are considered to be valid and do not violate the constraint.
The difference between a unique index and a unique key/primary key constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command: create index or as part of a create table with primary key or unique key constraints or an alter table command that adds these constraints (see SQL Manual).
Briefly the constraints are:
Not Null - Column value must be present.
Unique Key - Column(s) value(s) must be unique in table or null (see note below).
Primary Key - Unique key + Not Null which equates to every column in the key must have a value and this value is unique so the primary key uniquely identifies each and every row in the table.
Foreign Key - Restricts values in a table column to being a value found in the primary key or unique key Constraint on the referenced table (parent/child relationship).
Check - Tests the column value against an expression (rule).
Technically it would be possible for Oracle, to support primary key and unique key constraints without using an index at all. In the case of a unique key or primary key constraint Oracle could perform a full table scan to check for the presence of a key value before performing the insert but the performance cost of doing this for anything other than a very small table would be excessive probably rendering Oracle useless.
Prior to Oracle 8 if you defined a primary key or a unique key constraint the Oracle RDBMS would create a unique index to support enforcement of the constraint. If an index already existed on the constrained columns Oracle would use it rather than define another index on the same columns.
Starting with Oracle version 8 Oracle has the ability to enforce primary key and unique key constraints using non-unique indexes. The use of non-unique indexes supports deferring enforcement of the constraint until transaction commit time if the constraint is defined at creation time as deferrable. Also starting with version 8 Oracle has the ability to place constraints on tables where the existing data does not meet the requirements imposed by the constraint through use of a novalidate option (see SQL Manual).
The practical difference between using a unique index to support data integrity and a unique key or primary key on the same columns since Oracle will build an index to support the constraint if you do not is that you can define foreign key constraints when the primary key or unique key constraint exist. Also in the case of a primary key constraint Oracle will convert the columns in the constraint to be not null constrained when it is added to meet the primary key requirement to uniquely identify each and every row in the table.
There is no such restriction on a unique index. The primary key and unique key constraints along with foreign key constraints that reference them also provide a form of documentation on the relationships between objects.
The Oracle RDBMS Data Dictionary views All/DBA/USER_CONSTRAINTS and ALL/DBA/USER_CONS_COLUMNS may be used to locate constraints on a table and the columns being constrained.
If you drop or disable a primary key or unique key constraint that is supported by a unique index the index is dropped with the constraint. If a non-unique index is used to support the constraint the index is not dropped with the constraint. This second condition is effective only with version 8 and higher.
Note – Unique key constraints allow the constrained column to be NULL. Nulls values are considered to be valid and do not violate the constraint.
Disabling a Command for a User
Let’s say we have an instance where in there are 100 tables in a schema and that Schema Owner is not supposed to use delete on any of those tables.
Then instead of getting into the mess of Grants / revokes, we can use one of the facilities that are provided by Oracle.
There is a table called as product_profile and it can be created (if not present) by executing the PUBBLD.SQL
The PRODUCT_PROFILE table is owned by SYSTEM and has the following structure:
Name Null? Type
------------------------------------
PRODUCT NOT NULL VARCHAR2(30)
USERID VARCHAR2(30)
ATTRIBUTE VARCHAR2(240)
SCOPE VARCHAR2(240)
NUMERIC_VALUE NUMBER(15,2)
CHAR_VALUE VARCHAR2(240)
DATE_VALUE DATE
LONG_VALUE LONG
To disable a command for a user, insert a row into the PRODUCT_PROFILE table. You should normally log in as SYSTEM, and your INSERT statement should look like this:
INSERT INTO product_profile (product, userid, attribute, char_value) VALUES ('SQL*Plus','USERNAME',
Example:
INSERT INTO product_profile (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT',
Now, SCOTT cannot use the DELETE command on any table. IF you want to allow him to use the delete command then, simply login as system again and drop the record from product_profile.
Example: DELETE FROM product_profile;
FAQ on Oracle HTTP Server (OHS)
The answer for this question took me and Google hours. And finally I got the answer:
What is OHS?
Oracle HTTP Server is often called Oracle HTTP Server.
What version of Apache is Oracle HTTP Server based on?
Two versions. Apache version 1.3.34 for OHS based on Apache 1.3 and Apache version 2.0.55 for OHS based on Apache 2.0.
Are Apache modules, not provided by Oracle, supported when integrated with OHS?
Oracle only provides support for modules included in the Oracle distribution. Oracle does not support modules obtained from any other source, including the Apache Software Foundation. However, OHS will still be supported when non-Oracle provided modules are included. If Oracle Support suspects that a non-Oracle provided module is contributing to a reported problem, customers may be requested to reproduce the problem without that module being included.
What is Oracle’s policy on fixing security bugs found in OHS?
Oracle’s policy and process for fixing security vulnerabilities can be found on Oracle’s SecAlert OTN page.
Is Oracle HTTP Server available as a standalone product too?
Yes. A new ‘Web Server and Process Management’ installation option let you install just the Oracle HTTP Server based on Apache 1.3 (with Oracle Process Manager and Notification Server). Also, Oracle HTTP Server based on Apache 2.0 is available to be installed as a standalone product off the Oracle Application Server 10g (10.1.3) Companion CD.
Is Apache v2.0 version of OHS supported with this release?
Yes. However, OHS based on Apache 2.0 is only supported in a standalone deployment version. It has the same functionality as OHS based on Apache 1.3 except for the following:
• IPv6 is supported in OHS based on Apache 2.0 but not in OHS based on Apache 1.3
• mod_oradav is not supported in OHS based on Apache 2.0
• mod_dms is not supported in OHS based on Apache 2.0
• mod_plsql is not supported in OHS based on Apache 2.0
Does OHS need to run as root?
No. OHS needs to run as root only when users wants to use port less than 1024. If this will never be the case, then they can run OHS as the user that installed Oracle Application Server rather than root. In order to do this, perform the following steps:
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
8 If you are using mod_osso, re-register mod_osso
9 Exit root
10 Restart OHS
Can I compress output from OHS (ex. gzip)?
In general, the recommendation is to use Web Cache for this purpose. There are other freeware modules (for example, mod_gzip) that may be plugged in for this purpose - but their use is not supported.
Why do I see a warning about the use of EAPI when starting OHS with a non-Oracle provided module?
Oracle HTTP Server is compiled with EAPI (Extended API) support. The EAPI is an extension to the Apache module API provided by mod_ssl (see http://www.modssl.org). If you see a message similar to the following example when starting OHS with a non-Oracle provided module it means that the module being loaded was not compiled with the EAPI.
[Mon Oct 31 12:11:37 2005] [warn] Loaded DSO libexec/mod_python.so uses plain Apache 1.3 API, this module might crash under EAPI! (please recompile it with -DEAPI)
In most cases, this warning message can safely be ignored. To eliminate the error message, the module can be recompiled using the -DEAPI compile time option and either the Apache header files provided with OHS in $ORACLE_HOME/Apache/Apache/include or the header files from a generic Apache instance that includes modssl and the corresponding EAPI additions.
Can a standalone OHS based on Apache 2.0 communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. Standalone OHS based on Apache 2.0 can be configured to communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 2 ‘Configuring Standalone Oracle HTTP Server with Oracle Application Server’ of Oracle HTTP Server Standalone Administrator’s Guide Based on Apache 2.0 for details on how to configure it.
Can an OHS of Oracle Application Server 10g Release 2 (10.1.2) be used to route requests to Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. You can configure an OHS of Oracle Application Server 10g Release 2 (10.1.2) middle-tier instance to communicate with Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 6 ‘Reconfiguring Application Server Instances’ of Oracle Application Server Administrator’s Guide 10g Release 3 (10.1.3) for details on how to configure it.
What is mod_oc4j?
Mod_oc4j is the load balancer for the requests going to the Oracle Application Server Container for J2EE (OC4J) Instances in Oracle Application Server. It is an OHS module that provides routing between OHS and OC4J. The Oracle Process Manager and Notification Server (OPMN) component of Oracle Application Server keeps mod_oc4j aware of the status of different OC4J processes - thus, mod_oc4j routes only to the processes that are up and running. Mod_oc4j also understands the concepts of Oracle Application Server Cluster and OC4J groups, and routes accordingly to provide as much transparent failover as possible.
Does mod_oc4j work with web servers other than OHS?
Yes. Mod_oc4j is available as a plug-in too, called OC4J Plug-in, to work with non-Oracle web servers too including IIS, iPlanet, and generic Apache.
What are the different routing/load balancing algorithms?
Mod_oc4j provides three distinct kinds of routing: (a) round robin, (b) random and (c) metric based. The effective performance of round robin and random algorithms is the same. The latter, metric based routing, is based on OC4J process informing mod_oc4j of a metric based on its internal resource availability (ex. connection pools). Mod_oc4j then uses this metric to make routing decisions.
These load balancing/routing algorithms also have a flavor - affinity based. In this mode (it is the default mode), these algorithms will always route to the local node, except in cases when no process is available on the local node. The random and round robin algorithms have an extra flavor - weight based. In case of weight based, mod_oc4j distributes requests according to the routing weight configured for each host. Refer to Oracle HTTP Server Administrator’s Guide for more details on load balancing algorithms.
Can mod_oc4j talk to OC4J using SSL?
Yes, the AJP communication between mod_oc4j and OC4J processes can now be over AJP/SSL. Previously, this was in the clear text. Also, the SSL negotiation does not happen each time the two need to talk - resulting in less performance impact.
There are no Oc4jMount directives in my mod_oc4j.conf file, how does mod_oc4j know where to route the requests?
In previous releases of Oracle Application Server (version 10.1.2 and earlier), OC4J mount points were statically configured in mod_oc4j.conf file. Thus, when a user deployed or un-deployed an application, mod_oc4j.conf file was updated and OHS restarted.
In Oracle Application Server 10g Release 3 (10.1.3), OC4Js announce their mount-point(s) in the notifications they send out and mod_oc4j dynamically adjusts its routing table using this information. This eliminates the need for static mount point configuration and enables mod_oc4j to update its mount point configuration dynamically (without restarting OHS).
Can I still use the old static mount point configuration?
Yes. Although dynamic mount point creation is enabled by default, you do have the option of continuing to use statically configured mount points. You can configure this by setting a new directive called Oc4jRoutingMode to ‘Static’ in mod_oc4j.conf file. Directive Oc4jRoutingMode specifies the routing behavior and can take one of the following values:
• Dynamic – This specifies that the new dynamic routing functionality is used and any old style routing configuration is ignored.
• Static – This informs mod_OC4J to use 9.0.4/10.1.2 style routing configuration (where mount points are explicitly listed). Dynamic routing is not used.
• DynamicOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the dynamically specified mount points.
• StaticOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the statically specified mount points.
Is it possible to dump/view the list of current mount points mod_oc4j is using?
Yes, you can dump/view the in-memory mod_oc4j routing table contents by going to the following URL:
http://localhost:/oc4j-status
The information you will find here includes configured load balancing algorithm, routing mode, routing id, application name, context and the OC4J process(s) the application requests are routing to.
The URI for this pages is configurable, and by default is located in the dms.conf file in $ORACLE_HOME/Apache/Apache/conf directory. The default configuration only allows it to be accessible from the localhost (127.0.0.1) Virtual Host. The configuration snippet looks like this:
Oc4jSet StatusUri /oc4j-status
You can place this snippet anywhere in the httpd.conf if you want to see that status page under the default server, instead of only accessing it from the localhost Virtual Host. If you do that, the URL to dump/view routing table will be:
http://:/oc4j-status
What is routing ID?
The routing ID specifies a routing relationship between OC4Js and OHSs. In other words, an OHS routes to every OC4J that it shares a routing ID with. Every OC4J is assigned a routing ID, similarly each OHS is assigned one or more routing IDs to route to.
OPMN passes the routing ID to OC4J as a system property and to OHS as an environment variable when it is started. OC4J adds this routing ID to the ONS notifications it publishes. OHS listens for notifications from OC4J. When an OHS sees the first notification from an OC4J containing a routing ID on its list, it begins routing to it.
The addition of routing IDs and mount point discovery in Oracle Application Server 10g Release 3 (10.1.3) version of OHS allows mod_OC4J to dynamically discover all aspects of OC4J routing.
Should I configure routing ID for OHS in both mod_oc4j.conf and opmn.xml files?
No. Out of the box, OHS is configured to pick up its routing ID from opmn.xml file. Though it is possible to configure routing IDs for OHS in both opmn.xml and directly in mod_oc4j.conf file, but if OHS is configured with routing-id in both places, it considers it an error and fails to start. So routing IDs for OHS should either be configured in opmn.xml (specified as module data under element or under element of OHS) or mod_oc4j.conf but not at both places.
Source: http://www.oracle.com/technology/products/ias/ohs/htdocs/ohs-1013-faq.pdf
What is OHS?
Oracle HTTP Server is often called Oracle HTTP Server.
What version of Apache is Oracle HTTP Server based on?
Two versions. Apache version 1.3.34 for OHS based on Apache 1.3 and Apache version 2.0.55 for OHS based on Apache 2.0.
Are Apache modules, not provided by Oracle, supported when integrated with OHS?
Oracle only provides support for modules included in the Oracle distribution. Oracle does not support modules obtained from any other source, including the Apache Software Foundation. However, OHS will still be supported when non-Oracle provided modules are included. If Oracle Support suspects that a non-Oracle provided module is contributing to a reported problem, customers may be requested to reproduce the problem without that module being included.
What is Oracle’s policy on fixing security bugs found in OHS?
Oracle’s policy and process for fixing security vulnerabilities can be found on Oracle’s SecAlert OTN page.
Is Oracle HTTP Server available as a standalone product too?
Yes. A new ‘Web Server and Process Management’ installation option let you install just the Oracle HTTP Server based on Apache 1.3 (with Oracle Process Manager and Notification Server). Also, Oracle HTTP Server based on Apache 2.0 is available to be installed as a standalone product off the Oracle Application Server 10g (10.1.3) Companion CD.
Is Apache v2.0 version of OHS supported with this release?
Yes. However, OHS based on Apache 2.0 is only supported in a standalone deployment version. It has the same functionality as OHS based on Apache 1.3 except for the following:
• IPv6 is supported in OHS based on Apache 2.0 but not in OHS based on Apache 1.3
• mod_oradav is not supported in OHS based on Apache 2.0
• mod_dms is not supported in OHS based on Apache 2.0
• mod_plsql is not supported in OHS based on Apache 2.0
Does OHS need to run as root?
No. OHS needs to run as root only when users wants to use port less than 1024. If this will never be the case, then they can run OHS as the user that installed Oracle Application Server rather than root. In order to do this, perform the following steps:
1 Shutdown OHS
2 Become root
3 cd $ORACLE_HOME/Apache/Apache/bin
4 chown root .apachectl
5 chmod 6750 .apachectl
6 cd $ORACLE_HOME/Apache/Apache/logs
7 rm -f *
8 If you are using mod_osso, re-register mod_osso
9 Exit root
10 Restart OHS
Can I compress output from OHS (ex. gzip)?
In general, the recommendation is to use Web Cache for this purpose. There are other freeware modules (for example, mod_gzip) that may be plugged in for this purpose - but their use is not supported.
Why do I see a warning about the use of EAPI when starting OHS with a non-Oracle provided module?
Oracle HTTP Server is compiled with EAPI (Extended API) support. The EAPI is an extension to the Apache module API provided by mod_ssl (see http://www.modssl.org). If you see a message similar to the following example when starting OHS with a non-Oracle provided module it means that the module being loaded was not compiled with the EAPI.
[Mon Oct 31 12:11:37 2005] [warn] Loaded DSO libexec/mod_python.so uses plain Apache 1.3 API, this module might crash under EAPI! (please recompile it with -DEAPI)
In most cases, this warning message can safely be ignored. To eliminate the error message, the module can be recompiled using the -DEAPI compile time option and either the Apache header files provided with OHS in $ORACLE_HOME/Apache/Apache/include or the header files from a generic Apache instance that includes modssl and the corresponding EAPI additions.
Can a standalone OHS based on Apache 2.0 communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. Standalone OHS based on Apache 2.0 can be configured to communicate with an existing Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 2 ‘Configuring Standalone Oracle HTTP Server with Oracle Application Server’ of Oracle HTTP Server Standalone Administrator’s Guide Based on Apache 2.0 for details on how to configure it.
Can an OHS of Oracle Application Server 10g Release 2 (10.1.2) be used to route requests to Oracle Application Server 10g Release 3 (10.1.3) instance?
Yes. You can configure an OHS of Oracle Application Server 10g Release 2 (10.1.2) middle-tier instance to communicate with Oracle Application Server 10g Release 3 (10.1.3) instance. Refer to chapter 6 ‘Reconfiguring Application Server Instances’ of Oracle Application Server Administrator’s Guide 10g Release 3 (10.1.3) for details on how to configure it.
What is mod_oc4j?
Mod_oc4j is the load balancer for the requests going to the Oracle Application Server Container for J2EE (OC4J) Instances in Oracle Application Server. It is an OHS module that provides routing between OHS and OC4J. The Oracle Process Manager and Notification Server (OPMN) component of Oracle Application Server keeps mod_oc4j aware of the status of different OC4J processes - thus, mod_oc4j routes only to the processes that are up and running. Mod_oc4j also understands the concepts of Oracle Application Server Cluster and OC4J groups, and routes accordingly to provide as much transparent failover as possible.
Does mod_oc4j work with web servers other than OHS?
Yes. Mod_oc4j is available as a plug-in too, called OC4J Plug-in, to work with non-Oracle web servers too including IIS, iPlanet, and generic Apache.
What are the different routing/load balancing algorithms?
Mod_oc4j provides three distinct kinds of routing: (a) round robin, (b) random and (c) metric based. The effective performance of round robin and random algorithms is the same. The latter, metric based routing, is based on OC4J process informing mod_oc4j of a metric based on its internal resource availability (ex. connection pools). Mod_oc4j then uses this metric to make routing decisions.
These load balancing/routing algorithms also have a flavor - affinity based. In this mode (it is the default mode), these algorithms will always route to the local node, except in cases when no process is available on the local node. The random and round robin algorithms have an extra flavor - weight based. In case of weight based, mod_oc4j distributes requests according to the routing weight configured for each host. Refer to Oracle HTTP Server Administrator’s Guide for more details on load balancing algorithms.
Can mod_oc4j talk to OC4J using SSL?
Yes, the AJP communication between mod_oc4j and OC4J processes can now be over AJP/SSL. Previously, this was in the clear text. Also, the SSL negotiation does not happen each time the two need to talk - resulting in less performance impact.
There are no Oc4jMount directives in my mod_oc4j.conf file, how does mod_oc4j know where to route the requests?
In previous releases of Oracle Application Server (version 10.1.2 and earlier), OC4J mount points were statically configured in mod_oc4j.conf file. Thus, when a user deployed or un-deployed an application, mod_oc4j.conf file was updated and OHS restarted.
In Oracle Application Server 10g Release 3 (10.1.3), OC4Js announce their mount-point(s) in the notifications they send out and mod_oc4j dynamically adjusts its routing table using this information. This eliminates the need for static mount point configuration and enables mod_oc4j to update its mount point configuration dynamically (without restarting OHS).
Can I still use the old static mount point configuration?
Yes. Although dynamic mount point creation is enabled by default, you do have the option of continuing to use statically configured mount points. You can configure this by setting a new directive called Oc4jRoutingMode to ‘Static’ in mod_oc4j.conf file. Directive Oc4jRoutingMode specifies the routing behavior and can take one of the following values:
• Dynamic – This specifies that the new dynamic routing functionality is used and any old style routing configuration is ignored.
• Static – This informs mod_OC4J to use 9.0.4/10.1.2 style routing configuration (where mount points are explicitly listed). Dynamic routing is not used.
• DynamicOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the dynamically specified mount points.
• StaticOverride – Both dynamic and 9.0.4/10.1.2 style routing are used by mod_oc4j. If there are conflicts, OHS routes to the statically specified mount points.
Is it possible to dump/view the list of current mount points mod_oc4j is using?
Yes, you can dump/view the in-memory mod_oc4j routing table contents by going to the following URL:
http://localhost:
The information you will find here includes configured load balancing algorithm, routing mode, routing id, application name, context and the OC4J process(s) the application requests are routing to.
The URI for this pages is configurable, and by default is located in the dms.conf file in $ORACLE_HOME/Apache/Apache/conf directory. The default configuration only allows it to be accessible from the localhost (127.0.0.1) Virtual Host. The configuration snippet looks like this:
Oc4jSet StatusUri /oc4j-status
You can place this snippet anywhere in the httpd.conf if you want to see that status page under the default server, instead of only accessing it from the localhost Virtual Host. If you do that, the URL to dump/view routing table will be:
http://
What is routing ID?
The routing ID specifies a routing relationship between OC4Js and OHSs. In other words, an OHS routes to every OC4J that it shares a routing ID with. Every OC4J is assigned a routing ID, similarly each OHS is assigned one or more routing IDs to route to.
OPMN passes the routing ID to OC4J as a system property and to OHS as an environment variable when it is started. OC4J adds this routing ID to the ONS notifications it publishes. OHS listens for notifications from OC4J. When an OHS sees the first notification from an OC4J containing a routing ID on its list, it begins routing to it.
The addition of routing IDs and mount point discovery in Oracle Application Server 10g Release 3 (10.1.3) version of OHS allows mod_OC4J to dynamically discover all aspects of OC4J routing.
Should I configure routing ID for OHS in both mod_oc4j.conf and opmn.xml files?
No. Out of the box, OHS is configured to pick up its routing ID from opmn.xml file. Though it is possible to configure routing IDs for OHS in both opmn.xml and directly in mod_oc4j.conf file, but if OHS is configured with routing-id in both places, it considers it an error and fails to start. So routing IDs for OHS should either be configured in opmn.xml (specified as module data under
Source: http://www.oracle.com/technology/products/ias/ohs/htdocs/ohs-1013-faq.pdf