Blog

select * from v$version;

The below query displays all the locked tables in the given session:

SELECT  owner

        || ‘.’

        || object_name “Object                   “

       ,os_user_name “Terminal”

       ,session_id “Session ID”

       ,serial# “Serial”

       ,oracle_username “Locker”

       ,program “Program”

       ,nvl (lockwait

            ,’ACTIVE’) “Wait”

       ,decode (locked_mode

               ,2

               ,’ROW SHARE’

               ,3

               ,’ROW EXCLUSIVE’

               ,4

               ,’SHARE’

               ,5

               ,’SHARE ROW EXCLUSIVE’

               ,6

               ,’EXCLUSIVE’

               ,’UNKNOWN’) “Lockmode“

       ,object_type “Object Type”

       ,c.sid

FROM    sys.v_$locked_object a

       ,sys.all_objects b

       ,sys.v_$session c

WHERE   a.object_id = b.object_id

AND     c.sid = a.session_id

ORDER BY 1 ASC

        ,7 DESC;

To know the forms db session id, goto Forms, 

Navigate to Help > Diagnose > Examine 

The Examine window opens, there specify $SESSIONID and enter

the current DB session id of the Forms will be displayed in the Value section of the Examine window.

The below query displays the long running queries in the given session:

SELECT  sql_text “sql                        “

       ,sql_fulltext

       ,sesion.sid

       ,sesion.username

       ,optimizer_mode

       ,hash_value

       ,address

       ,cpu_time

       ,elapsed_time / 60000000

FROM    v$sqlarea sqlarea

       ,v$session sesion

WHERE   sesion.sql_hash_value = sqlarea.hash_value

AND     sesion.sql_address = sqlarea.address

AND     sesion.username IS NOT NULL

AND     sesion.sid = <provide session id here>;

At times, we can automate some tasks in Oracle Applications such as running a concurrent program, creating a person etc. since the Oracle Applications is a client-server based application, every task we perform using Forms/self service will occur in the context of the user/responsibility. Hence, while performing these tasks from the data base tier, we need to set the application/user/responsibility context in which we are going to perform that particular task.  To set this global security context, we need to call fnd_global.apps_initialize api.

The following is a sample call to fnd_global.apps_initialize api, which sets the apps global security context with proper user and responsibility:

fnd_global.APPS_INITIALIZE
  (USER_ID                         => l_user_id   — Mode: IN    Mandatory: true   Data Type: NUMBER
  ,RESP_ID                          =>  l_resp_id  — Mode: IN    Mandatory: true   Data Type: NUMBER
  ,RESP_APPL_ID               => l_resp_appl_id  — Mode: IN    Mandatory: true   Data Type: NUMBER
  ,SECURITY_GROUP_ID  => l_sec_grp_id   — Mode: IN    Mandatory: false  Data Type: NUMBER
  ,SERVER_ID                     => l_server_id  — Mode: IN    Mandatory: false  Data Type: NUMBER
  );

l_user_id is the user_id in fnd_users table, which will be utilized during the call

l_resp_id is the responsibility id of the user

l_resp_appl_id is the responsibility application id

l_sec_grp_id is the security group id

l_server_id is the server id

l_sec_grp_id and l_server_id are not mandatory and are seldom used.

Gather Statistics:

While processing huge number of records, the system may get exhaustive and the performance decreases due to incorrect statistics in the data base. To avoid this to happen, we need to run ‘Gather Statistics’ regularly.

The following is the pl/sql block which gathers the statistics for an entire schema:

declare
v_st timestamp;
v_end timestamp;
begin
v_st := sysdate;
dbms_output.put_line(v_st);
dbms_stats.gather_schema_stats
  (ownname                         => ‘HR’  — mode: in    mandatory: true   data type: varchar2
  );
v_end := sysdate;
dbms_output.put_line(v_end);
dbms_output.put_line(‘time elapsed’);
dbms_output.put_line(v_end-v_st);
end;

The above pl/sql block gathers statics of entire HR schema, and calculates the total time the system took to do this.