Readme of Oct 2007 CPU DB patch 6395038 (for 9.2.0.8) has incorrect query

Today Sandeep pinged me asking for clarification about a DB patch I had recommended. Step 5 in section 3.3.2 of the readme of Oct2007 CPU DB (9.2.0.8) patch 6395038 reads like this:

This step is required only if your database contains an Identity Management Metadata Repository. If you are not sure whether you need to perform this step, you can you can enter the following query (a non-null result means you should perform the step):

SQL> select USERNAME from dba_users where upper(USERNAME) like '%ODS%';

This returns the result WEBMETHODS in our environment.

As we all know WEBMETHODS has nothing to do with OID or Identity Management Metadata. The Oracle Internet Directory runs on an Oracle database and creates two database users: ODS and ODSCOMMON. ODS is the schema owner that contains all of the database objects (tables, views, objects, etc.) used for OID functionality and directory storage. When the OID needs to login to the database, it uses the ODS database account which has a default password of ODS. You should secure this database user account before putting the LDAP directory into production.

So the correct query should be:

select username from dba_users where upper(username) in ('ODS','ODSCOMMON');

I logged an SR to get this corrected. Oracle was quick in response:

ISSUE CLARIFICATION
====================
Readme for PATCH 6395038 SECTION 3.3.2 POINT 5 includes a very unreliable
method for testing for the presence of OID. The test is to perform the query:

select USERNAME from dba_users where upper(USERNAME) like '%ODS%';

For this customer they have a schema named WEBMETHODS which causes the test
to incorrectly identify the instance as one that contains OID.


.
ISSUE VERIFICATION
===================
Verified

.
CAUSE DETERMINATION
====================
Unclear logic

CAUSE JUSTIFICATION
====================
Unclear logic

.
PROPOSED SOLUTION(S)
======================

Modify the query to be:

select USERNAME from dba_users where upper(USERNAME) like 'ODS';

PROPOSED SOLUTION JUSTIFICATION(S)
====================================
REASON: Any instance which contains OID will have the 'ODS' schema
specifically. Older versions of OID also included the ODS_COMMON schema,
however even in these cases the ODS schema was always present.

.
SOLUTION / ACTION PLAN
=======================
As we are specifically looking for the schema's ODS or ODS_COMMON schemas, we can ignore any result other than these.

.
KNOWLEDGE CONTENT
=================
Platform / Port Specific? = NO

Created a new note: TBC
Created Doc Bug : Bug: 6531776

26-OCT-07 23:50:15 GMT

Vikram,
Documentation bug 6531776 and Metalink Note: 464734.1 have been created for this issue. It may take a few days for the note to be reviewed and become
available.
For clarity, the workaround is to ignore any value returned other than the 2 specific values of 'ODS' and 'ODS_COMMON'.

Eddie

26-OCT-07 23:51:55 GMT

.
UPDATE
=======
Hi Vikram,

I am inactivating this Service Request, as I believe I have provided you with the solution to your issue. If this is not the case, please update the Service Request within the next two weeks and I will be glad
to assist you further. Otherwise, no update is necessary and after two weeks, th
e Service Request will automatically close.

Best Regards,
Eddie
Global Customer Services

27-OCT-07 00:49:02 GMT

Hi Eddie,

Thanks for the quick response. However I have two points:

1. The OID schemas are ODS and ODSCOMMON. You have mentioned the schema name as ODS_COMMON which is again incorrect.
2. Metalink Note 464734.1 which you mention is not visible to me. Maybe you have
classified as internal only.

- Vikram

29-OCT-07 15:55:04 GMT

Vikram,
ODS_COMMON is indeed correct, however it is a LEGACY OID schema and not present with the later versions of OID. As I mentioned the note will be review
ed before becoming available to you.
Eddie

29-OCT-07 15:55:19 GMT

I get zero hits when I query for ODS_COMMON on metalink or google. However I get a lot of hits when I query for ODSCOMMON. Was ODSCOMMON schema called ODS_COMMON in previous versions. If yes, then it is very strange that ODS_COMMON doesn't return anything.

- Vikram