Archive for October, 2013

AWR Report – How to get it through EM

Thursday, October 31st, 2013

We need a database user/pwd


But we can generate it using SQLPLUS as well :

AWR Snapshot Reports
Oracle provides reports that you can run to analyze the data in the AWR. These reports are much like the statspack reports prior to Oracle Database 10g. There are two reports: awrrpt.sql and awrrpti.sql, which are available in the directory $ORACLE_HOME/ rdbms/ admin.
The output of these reports is essentially the same, except that awrrpti.sql script allows you to define a specific instance to report on. The reports are much like the statspack reports of old, in that you define a beginning and ending snapshot ID, and the output filename of the report. Additionally, you can opt to produce the report in either text format or HTML format.

[oaPROD usa.com01 PROD] $ sqlplus ‘/as sysdba’
SQL> @awrrpt.sql

Current Instance   DB Id    DB Name      Inst Num Instance

HOWTO: Checking options installed within the Database/Oracle Home

Thursday, October 31st, 2013

1)From Oracle Universal Installer: In OUI select installed products and click on list and expand, you will see a list of options installed in database.

You have to use OUI to check whether Oracle Secure Enterprise Search is installed or not.

TIP: Start Oracle Universal Installer from your system prompt (UNIX) with the following command:


2)From v$option:

SQL> set pages 100

SQL> select * from v$option;

PARAMETER                                                        VALUE

—————————————————————- —–

Partitioning                                                     TRUE

Objects                                                          TRUE

Real Application Clusters                                        FALSE

Advanced replication                                             TRUE

Bit-mapped indexes                                               TRUE

Connection multiplexing                                          TRUE

Connection pooling                                               TRUE

Database queuing                                                 TRUE

Incremental backup and recovery                                  TRUE

Instead-of triggers                                              TRUE

Parallel backup and recovery                                     TRUE

Parallel execution                                               TRUE

Parallel load                                                    TRUE

Point-in-time tablespace recovery                                TRUE

Fine-grained access control                                      TRUE

Proxy authentication/authorization                               TRUE

Forms in socket mode

Thursday, October 31st, 2013

Algun cliente alguna vez debe haber sufrido de un extraño issue con  los forms y se preguntara si vale la pena cambiarlos a ‘socket’ mode, y él se estará preguntando (con toda razón):

A.      When the forms are changed to socket mode:

1.      Will this affect the EBS application in anyway (will it be slow than now)

2.      Do we need to perform any change on our or the workstation’s side ?


Please look at the Note in Metalink 310976.1. It seems that the Java Console in JInitiator tells you at the beginning which mode you are using.

Verifying the Forms Client Connection to the Forms Server

Once the Applet has initialized and started, it connects to the Forms Listener, using the serverPort, serverHost and connectMode parameters displayed in the Java console.  Steps to verify this are:

HOWTO : Getting User Responsability on EBS 11i using a Database user (BOLINF or APPS)

Thursday, October 31st, 2013

Find below the SQL statement to generate an extract with active users together with their active responsibilities.

The statement extracts all the active assignments of responsibilities by using the FND_USER_RESP_GROUPS table which combines DIRECT and INDIRECT responsibilities.

If you need only the DIRECT responsibilities than you also have the option to use the seeded view FND_USER_RESP_GROUPS_DIRECT. If you need only the INDIRECT responsibilities (added by roles – like Application Diagnostics and others) than use the seeded view FND_USER_RESP_GROUPS_INDIRECT.

As always adjust the SQL to cover your needs.

FND_USER fuser
AND fuser.USER_ID = furg.USER_ID
AND (to_char(fuser.END_DATE) is null
OR fuser.END_DATE > sysdate)
AND (to_char(furg.END_DATE) is null
OR furg.END_DATE > sysdate)
, fuser.USER_NAME;

Ramakrishnan & Gehrke Sailors script for Oracle database

Saturday, October 19th, 2013

The chapter 5 of the  book “Database Management Systems” (Ramakrishnan, Raghu & Johannes Gehrke.) introduces the Sailors data model, a set of three tables named Sailors, Boats and Reserves

In this article, the SQL script that implements the creation of these relational structures is developed and adapted  for Oracle Database. This script is based on the script worked by Hjalmtyr Hafsteinsson for PostgreSQL database.

The script does the following tasks

Cleans Sailors Schema and tablespaces of previous script executions

Creates Sailors_data and Sailors_index  tablespaces

Creates Sailors Schema

Grants roles and privileges to Sailors user

Connects to Sailors user

Creates tables, constraints,  in the Sailors Schema

Populates tables

Adds new columns  and new constraints (that are not part of the original data model)

Creates B-tree indexes for columns that are used in WHERE clause