R12 Apps importance Scripts

How to check apps HTTP_Server,OC4J status:-

[oracle@r0469 scripts]$ ./adopmnctl.sh status

 You are running adopmnctl.sh version 120.6

 Checking status of OPMN managed processes...

 Processes in Instance: VIS_r0469.r0469.oracle.com

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status 

---------------------------------+--------------------+---------+---------

OC4JGroup:default_group          | OC4J:oafm          |   12248 | Alive  

OC4JGroup:default_group          | OC4J:forms         |   12182 | Alive  

OC4JGroup:default_group          | OC4J:oacore        |   12100 | Alive  

HTTP_Server                      | HTTP_Server        |   12048 | Alive  

 adopmnctl.sh: exiting with status 0

 adopmnctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adopmnctl.txt for more information ... 

How to check apps Concurrent Manager status:-

[oracle@r0469 scripts]$ ./adcmctl.sh status

You are running adcmctl.sh version 120.17.12010000.3

 Enter the APPS username : apps

 Enter the APPS password :apps

Internal Concurrent Manager is Active.

 adcmctl.sh: exiting with status 0

 adcmctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adcmctl.txt for more information ... 

 R12 Apps Scripts Locations:-

 [oracle@r0469 ]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts

[oracle@r0469 scripts]$ ll

total 176

-rwx------ 1 oracle dba  7301 Aug 31 12:33 adalnctl.sh

-rwx------ 1 oracle dba  8243 Aug 31 12:34 adapcctl.sh

-rwx------ 1 oracle dba  1540 Aug 31 12:33 adautocfg.sh

-rwx------ 1 oracle dba 16497 Aug 31 12:33 adcmctl.sh

-rwx------ 1 oracle dba  7215 Aug 31 12:33 adexecsql.pl

-rwx------ 1 oracle dba  8728 Aug 31 12:34 adformsctl.sh

-rwx------ 1 oracle dba 10208 Aug 31 12:34 adformsrvctl.sh

-rwx------ 1 oracle dba  7996 Aug 31 12:34 adoacorectl.sh

-rwx------ 1 oracle dba  8004 Aug 31 12:34 adoafmctl.sh

-rwx------ 1 oracle dba  7282 Aug 31 12:34 adopmnctl.sh

-rwx------ 1 oracle dba 15298 Aug 31 12:33 adpreclone.pl

-rwx------ 1 oracle dba  5801 Aug 31 12:33 adstpall.sh

-rwx------ 1 oracle dba  5945 Aug 31 12:33 adstrtal.sh

-rwx------ 1 oracle dba  2240 Aug 31 12:34 gsmstart.sh

drwxrwxrwx 2 oracle dba  4096 May  5 14:53 ieo

-rwx------ 1 oracle dba  2277 Aug 31 12:34 java.sh

-rwx------ 1 oracle dba  6388 Aug 31 12:36 jtffmctl.sh

drwxrwxrwx 2 oracle dba  4096 May  5 14:53 msc

-rwxrwxr-- 1 oracle dba  6632 Aug 31 12:36 mwactl.sh

-rwxrwxr-- 1 oracle dba  6645 Aug 31 12:36 mwactlwrpr.sh

-rwxrwxrwx 1 oracle dba  1438 May 16 11:56 sqlnet.log

How to check apps Listener status:-

[oracle@r0469 scripts]$ ./adalnctl.sh status

adalnctl.sh version 120.3

Checking status for listener process APPS_VIS.

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 20-SEP-2012 12:52:08

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=r0469)(Port=1626))

STATUS of the LISTENER

------------------------

Alias                     APPS_VIS

Version                   TNSLSNR for Linux: Version 10.1.0.5.0 - Production

Start Date                20-SEP-2012 09:47:09

Uptime                    0 days 3 hr. 4 min. 58 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/VIS/inst/apps/VIS_r0469/ora/10.1.2/network/admin/listener.ora

Listener Log File         /oracle/VIS/inst/apps/VIS_r0469/logs/ora/10.1.2/network/apps_vis.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1626)))

Services Summary...

Service "FNDFS" has 1 instance(s).

  Instance "FNDFS", status UNKNOWN, has 1 handler(s) for this service...

Service "FNDSM" has 1 instance(s).

  Instance "FNDSM", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

adalnctl.sh: exiting with status 0

adalnctl.sh: check the logfile

 /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adalnctl.txt for more information ... 

Database and Application Information

Below are some of the queries that can be used to get the database and Application information.

1) Get Product Version

SQL> SELECT product

     , VERSION

     , status

FROM   product_component_version;

 2) Get Applications Version and Patch Information

SQL> SELECT   SUBSTR (a.application_name, 1, 60) Application_Name

       , SUBSTR (i.product_version, 1, 4) Version

       , i.patch_level

       , i.application_id

       , i.last_update_date

FROM     apps.fnd_product_installations i

       , apps.fnd_application_all_view a

WHERE    i.application_id = a.application_id

ORDER BY a.application_name;

3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.

SQL> SELECT applied_patch_id

     , patch_name

     , patch_type

     , source_code

     , creation_date

     , last_update_date

FROM   ad_applied_patches;

4) Check if the application is setup for Multi-Org

SQL> SELECT multi_org_flag

FROM   fnd_product_groups;

M

-

Y

Add Responsibility using Sqlplus:

BEGIN

FND_USER_RESP_GROUPS_API.LOAD_ROW

(X_USER_NAME => 'ANATESH',

X_RESP_KEY => 'APPLICATION_DEVELOPER',

X_APP_SHORT_NAME => 'FND',

X_SECURITY_GROUP => 'STANDARD',

X_OWNER => 'SYSADMIN',

X_START_DATE => Trunc(sysdate),

X_END_DATE => NULL,

X_DESCRIPTION => NULL,

X_LAST_UPDATE_DATE => Trunc(sysdate));

COMMIT;

END

 Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG');

 Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.

Type of DB Links
There are 3 types of DB links. They are as follows:

1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.

3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.

Types of Logins:
In dblink we can use 2 types of login. They are as follows:

1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’

2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’

Note: To create the public DBLINK the user must have create public database link system privileges.

path:D:\app\prasanthb\product\11.1.0\db_1\NETWORK\ADMIN

oracle2test = 

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.7.107)(PORT = 1526))

      )

    (CONNECT_DATA =

      (SERVICE_NAME =VIS)

      (SRVR = DEDICATED)

    )

  )

Script to delete the concurrent program from Oracle

Begin

fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End; 

Find out the URL of the Application in the database

If you want to find out the URL of the Application in the database in which table you can check ?

Option 1:
SQL> select * from icx_parameters;

Option 2:
SELECT PROFILE_OPTION_VALUE
  FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
                                                             FROM FND_PROFILE_OPTIONS
                                                           WHERE PROFILE_OPTION_NAME ='APPS_FRAMEWORK_AGENT')
                                                                AND LEVEL_VALUE=0;

 Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id "Concurrent Process ID",

       pid "System Process ID", os_process_id "Oracle Process ID",

       q.concurrent_queue_name "Concurrent Manager Name",

       p.process_status_code "Status of Concurrent Manager",

       TO_CHAR(p.process_start_date,'MM-DD-YYYY HH:MI:SSAM') "Concurrent Manager Started at"

  FROM fnd_concurrent_processes p,

       fnd_concurrent_queues q,

       fnd_v$process

 WHERE q.application_id = queue_application_id

   AND q.concurrent_queue_id = p.concurrent_queue_id

   AND spid = os_process_id

   AND process_status_code NOT IN ('K', 'S')

ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name;

Query to find who and when update an Oracle Application user's profile

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

Query to fetch Responsibilities attached to a user:

The below query will fetch the responsibilities assigned to a particular user.

SELECT

    fu.user_id,

    fu.user_name,

    fr.responsibility_name,

    fr.description,

    fa.application_name

FROM fnd_user fu,

     fnd_user_resp_groups g,

     fnd_application_tl fa,

     fnd_responsibility_tl fr

WHERE

     g.user_id(+) = fu.user_id

     AND g.responsibility_application_id = fa.application_id

     AND fa.application_id = fr.application_id

     AND g.responsibility_id = fr.responsibility_id

     AND fu.user_name  =UPPER('User_Name');

 How to get Login Passwords for application & Data Base instances

Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.

There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.

Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class "oracle.apps.fnd.security.WebSessionManagerProc".

For decryption and encryption, the following calls are made:

APPS.FND_WEB_SEC >

oracle.apps.fnd.security.WebSessionManagerProc>

oracle.apps.fnd.security.AolSecurity>

oracle.apps.fnd.security.AolSecurityPrivate

The actual encryption and decryption routines are in the "oracle.apps.fnd.security.AolSecurityPrivate" Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.

Create a package specification: get_pwd

-- Package Specification

CREATE OR REPLACE PAGE get_pwd AS

FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;

END get_pwd;

Create the package body: get_pwd

-- Package Body 

CREATE OR REPLACE PAGE BODY get_pwd AS

FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2 AS

LANGUAGE JAVA

NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

END get_pwd;

Call the package function as shown below:

/** Run this on toad, Get the DB apps password */

SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))

FROM DUAL)), usertable.encrypted_foundation_password)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')

,1

, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')

- 1

)

FROM DUAL))

Call the package function as shown below:

/** Run this on toad, Get the application usernames and passwords */

SELECT usertable.user_name

, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))

FROM DUAL)), usertable.encrypted_foundation_password)

FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE

UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')

,1

, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')

- 1

)

FROM DUAL))))

,usertable.encrypted_user_password)

FROM DUAL) AS encrypted_user_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER ('username') -- Here username is application login such as 'OPERATIONS'

Concurrent Program Monitoring Scripts

Most frequently in support projects we need to monitor and track the concurrent programs. Here are some the important queries to track the status of concurrent program. These are also very useful for the oracle apps system administrator

 
--------- Terminating requests----------

SELECT Request_Id Reqst,

 user_concurrent_program_name concurrent_program,

       User_Name Requestor,

 Oracle_Username Orcl,

       Fcr.Logfile_Name LN,

       Concurrent_Queue_Name Manager,

       TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,

       Single_Thread_Flag

FROM Fnd_Concurrent_Requests Fcr,   Fnd_Concurrent_Programs_tl Fcp,

     Fnd_Oracle_Userid O,
     Fnd_Concurrent_Processes P,

     Fnd_Concurrent_Queues Q,
     Fnd_User

WHERE

     Controlling_Manager = Concurrent_Process_ID

  AND ( P.Concurrent_Queue_ID  = Q.Concurrent_Queue_ID

  AND P.Queue_Application_ID = Q.Application_ID )

  AND O.Oracle_Id = Fcr.Oracle_Id

  AND ( Fcr.Program_Application_Id = Fcp.Application_Id

  AND Fcr.Concurrent_Program_Id  = Fcp.Concurrent_Program_Id )

  AND Requested_By = User_Id

  AND Phase_Code = 'R' AND Status_Code = 'T'

ORDER BY  Actual_Start_Date, Request_Id

------ Currently running requests ------------

SELECT Request_Id Reqst,

User_Name Requestor,

Oracle_Username Orcl,

      Concurrent_Queue_Name Manager,

Concurrent_Program_Name Program, 
user_concurrent_program_name conc_program,

      TO_CHAR(Actual_Start_Date, 'Mm-Dd-Yy Hh24:Mi') Started,

Fcr.Logfile_Name LN,

      Run_Alone_Flag, Single_Thread_Flag

FROM Fnd_Concurrent_Requests Fcr,

      fnd_concurrent_programs_tl fcpt,

      Fnd_Concurrent_Programs Fcp,

      Fnd_Oracle_Userid O,

Fnd_Concurrent_Processes P,

      Fnd_Concurrent_Queues Q, Fnd_User

WHERE

      Controlling_Manager = Concurrent_Process_ID

  AND ( P.Concurrent_Queue_ID  = Q.Concurrent_Queue_ID

  AND P.Queue_Application_ID = Q.Application_ID )

  AND O.Oracle_Id = Fcr.Oracle_Id

  AND ( Fcr.Program_Application_Id = Fcp.Application_Id

  AND Fcr.Concurrent_Program_Id  = Fcp.Concurrent_Program_Id )

  AND (Fcpt.concurrent_program_id = Fcp.Concurrent_program_id)

  AND Requested_By = User_Id

  AND Phase_Code = 'R' AND Status_Code   = 'R'

ORDER BY  Actual_Start_Date, Request_Id

--- Summary of concurrent request execution since Date ---

SELECT L1.Meaning Request_Type,

       L2.Meaning Status,

       COUNT(Status_Code) NR

FROM   Fnd_Concurrent_Requests R,

       Fnd_Concurrent_Programs P,

       Fnd_Lookups L1,

       Fnd_Lookups L2

WHERE

       L1.Lookup_Code = P.Execution_Method_Code

  AND  L1.Lookup_Type = 'CP_EXECUTION_METHOD_CODE'

  AND (R.Program_Application_ID = P.Application_ID 
  AND R.Concurrent_Program_ID  = P.Concurrent_Program_ID )

  AND (R.Status_Code  = L2.Lookup_Code

  AND L2.Lookup_Type = 'CP_STATUS_CODE' )

  AND R.Phase_Code = 'C'

  AND R.Actual_Completion_Date > SYSDATE - 5

GROUP BY

  L1.Meaning, L2.Meaning

Errored programs on a day

SELECT fcp.user_concurrent_program_name

FROM fnd_concurrent_requests fcr,

     fnd_concurrent_programs_tl fcp

WHERE status_code = 'E'

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND TRUNC(requested_start_date) = TRUNC(SYSDATE)

STATUS_CODE

A Waiting

B Resuming

C

D Cancelled

E Error

F Scheduled

G Warning

H On Hold

I

M No Manager

Q Standby

R

S Suspended

T Terminating

U Disabled

W Paused

X Terminated

Z Waiting

Managers that are currently running a request

SELECT Concurrent_Queue_Name Manager,

       Request_Id Request,

 User_name,

       Concurrent_Program_Name Program,

 Run_Alone_Flag,

       TO_CHAR(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started

  FROM Fnd_Concurrent_Queues Fcq,

 Fnd_Concurrent_Requests Fcr,

       Fnd_Concurrent_Programs Fcp,

 Fnd_User Fu,

 Fnd_Concurrent_Processes Fpro

 WHERE

       Phase_Code = 'R' AND

       Fcr.Controlling_Manager = Concurrent_Process_Id       AND               (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    AND 

Fcq.Application_Id      = Fpro.Queue_Application_Id ) AND (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id AND Fcr.Program_Application_Id = Fcp.Application_Id )     AND 

Fcr.Requested_By = User_Id;

 Oracle Performance Tuning related Queries

SQL to identify the query which takes long time

Step1 :  Run the first query ,  this will list all the programs that currently running in Application. Take the SID and use it in the second query.

SELECT
      f.user_name
      ,a.request_id "Req Id"
      ,a.concurrent_program_id "Prg Id"
      ,a.RESPONSIBILITY_ID Responsibility
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vs.serial# "Serial#"
      ,vp.spid
      ,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
      ,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM ASYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
    ,v$session vs
    ,v$process vp
WHERE
      a.controlling_manager = b.concurrent_process_id
  AND a.concurrent_program_id = c.concurrent_program_id
  AND a.program_application_id = c.application_id
  AND c2.concurrent_program_id = c.concurrent_program_id
  AND c2.application_id = c.application_id
  AND a.phase_code IN ('I','P','R','T')
  AND a.requested_by = f.user_id
  AND b.queue_application_id = q.application_id
  AND b.concurrent_queue_id = q.concurrent_queue_id
  AND c2.LANGUAGE = 'US'
  AND a.oracle_process_id = vp.spid
  AND vs.paddr = vp.addr
ORDER BY 9

Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query  ( select / insert / update ) which is actually taking time in your concurrent program.

SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE  

Currently running sessions in database

Often it requires keeping track of the currently running sessions and the Sql executed in the sessions. So here is the query by which you can find out.

Get all the active sessions in the database

select username, sid, serial#, process, status, module, action, osuser

from v$session

where username is not null

and status = 'ACTIVE'

-- Get the sqls executed by the active sessions

declare

    x number;

begin

    for x in

    ( select username||'('||sid||','||serial#||

                ') ospid = ' ||  process ||

                ' program = ' || program username,

             to_char(LOGON_TIME,' Day HH24:MI') logon_time,

             to_char(sysdate,' Day HH24:MI') current_time,

             sql_address,

             LAST_CALL_ET,

             osuser,

           machine

        from v$session

       where status = 'ACTIVE'

         rawtohex(sql_address) <> '00'

         and username is not null order by last_call_et )

    loop

        for y in ( select max(decode(piece,0,sql_text,null)) ||

                          max(decode(piece,1,sql_text,null)) ||

                          max(decode(piece,2,sql_text,null)) ||

                          max(decode(piece,3,sql_text,null))

                          sql_text

                     from v$sqltext_with_newlines

                    where address = x.sql_address

                      and piece < 4)

        loop

            if ( y.sql_text not like '%listener.get_cmd%' and

                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')

            then

                dbms_output.put_line( '--------------------' );

                dbms_output.put_line( x.username );

                dbms_output.put_line( x.logon_time || ' ' ||

                                      x.current_time||

                                      ' last et = ' ||

                                      x.LAST_CALL_ET);

                dbms_output.put_line(x.osuser||':'||x.machine);

                dbms_output.put_line(substr( y.sql_text, 1, 250 ) );

            end if;

        end loop;

    end loop;

end;

Get all the views that refer a particular table:

SELECT o.object_name, o.status, o.last_ddl_time

  FROM SYS.dba_objects o

 WHERE o.object_type = 'VIEW'

   AND EXISTS (

              SELECT 'x'

                   FROM SYS.dba_dependencies d

           WHERE d.referenced_type = 'TABLE'

                 AND d.TYPE = 'VIEW'

                    AND d.owner = 'APPS'

             AND d.NAME = o.object_name

                 AND d.referenced_name LIKE '<TABLE_NAME>');

Get all the tables with the column name:

SELECT

  TABLE_NAME

FROM

  all_tab_columns

WHERE

  column_name = '<COLUMN_NAME>'

Get SQL help from the Data base itself:

If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base

In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.

select *

from   system.help

where  upper(topic)=upper('string')

Find from which responsibility a concurrent program can be run:

SELECT distinct

  a.user_concurrent_program_name,

  a.description,

  request_group_name,

  e.responsibility_name

FROM

  fnd_concurrent_programs_tl a,

  fnd_request_groups b,

  fnd_request_group_units c,

  fnd_responsibility d,

  fnd_responsibility_tl e

WHERE

  a.concurrent_program_id = c.request_unit_id     

and b.request_group_id = c.request_group_id

and b.request_group_id = d.request_group_id

and d.responsibility_id = e.responsibility_id

and a.application_id = b.application_id

and b.application_id = c.application_id

and d.application_id = e.application_id

and a.concurrent_program_id = :p_conc_program_id

Find all running concurrent requests with their run times:

The below query is useful for analyzing concurrent program run times and identifying their performance

select request_id,

         fcpt.user_concurrent_program_name,

         completion_text,

         actual_start_date,

         actual_completion_date,

         to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration

 from fnd_concurrent_requests fcr,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt

where fcr.concurrent_program_id = fcp.concurrent_program_id

    and fcp.concurrent_program_id = fcpt.concurrent_program_id

    and fcr.actual_start_date > sysdate - 1

    order by actual_completion_date - actual_start_date desc

Search all packages for a line of code:

SELECT  *

FROM    ALL_SOURCE

WHERE   TYPE IN ('PACKAGE', 'PAGE BODY')

AND     TEXT LIKE '%XXXX%'

The below table shows all the data base object information:

select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'

Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name

select fct.user_concurrent_program_name, 
          fcp.concurrent_program_name,
          fe.execution_file_name,
          fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
        fnd_concurrent_programs fcp,
        fnd_executables fe,
        fnd_lookups fl
where
     upper(fct.user_concurrent_program_name) = upper('concurrent program')
and     fct.concurrent_program_id = fcp.concurrent_program_id
and     fe.executable_id = fcp.executable_id
and     fl.lookup_code = fe.execution_method_code
and     fl.lookup_type = 'CP_EXECUTION_OD_CODE'

List of all active workflow users and their roles

select  wu.name user_name,

        wr.name role_name

from    wf_users wu,

        wf_user_roles wur,

        wf_roles wr

where   wu.name = wur.user_name

and     wur.role_name = wr.name

and     wu.status = 'ACTIVE'

and     wr.status = 'ACTIVE'

and     wr.orig_system = 'WF_LOCAL_ROLES'

order by wu.name,

        wr.name

Concurrent requests raised in the last day

select request_id,

       decode(parent_request_id, -1, 'None') as parent_request_id,

       fcpt.user_concurrent_program_name,

       decode(  fcr.status_code,

                'A', 'Waiting',

                'B', 'Resuming',

                'C', '',

                'D', 'Cancelled',

                'E', 'Error',

                'F', 'Scheduled',

                'G', 'Warning',

                'H', 'On Hold',

                'I', '',

                'M', 'No Manager',

                'Q', 'Standby',

                'R', '',

                'S', 'Suspended',

                'T', 'Terminating',

                'U', 'Disabled',

                'W', 'Paused',

                'X', 'Terminated',

                'Z', 'Waiting') as status,       

       decode(  fcr.phase_code,

                'C', 'Completed',

                'I', 'Inactive',

                'P', 'Pending',

                'R', 'Running') as phase,

       fu.user_name,

       fr.responsibility_name,              

       completion_text,

       argument_text,

       request_date,

       requested_start_date,

       actual_start_date,

       actual_completion_date,

       fcp.concurrent_program_name,

       fe.executable_name,

       fe.execution_file_name

from   fnd_concurrent_requests fcr,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_executables fe,

       fnd_responsibility_vl fr,

       fnd_user fu

where  fcr.concurrent_program_id = fcp.concurrent_program_id

and    fcp.concurrent_program_id = fcpt.concurrent_program_id

and    fcp.executable_id = fe.executable_id

and    fcr.responsibility_id = fr.responsibility_id

and    fcr.requested_by = fu.user_id

and    fcr.request_date > sysdate - 1

--and    fcr.concurrent_program_id = XXXX

order by request_id desc

How to find the Trace file generated for a concurrent program

You can use the following query to locate the trace file, it takes as input the ' Concurrent Request id'

SELECT

'Request id: '||request_id , 
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.

In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.

Removing Table Lock

Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

This script will report the SQL text of some of the locks currently being held in the database

select s.username username, 

       a.sid sid, 

       a.owner||'.'||a.object object, 

       s.lockwait, 

       t.sql_text SQL

from   v$sqltext t, 

       v$session s, 

       v$access a

where  t.address=s.sql_address 

and    t.hash_value=s.sql_hash_value 

and    s.sid = a.sid 

and    a.owner != 'SYS'

and    upper(substr(a.object,1,2)) != 'V$'

Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:

 SELECT fu.user_id,

fu.user_name,

fur.responsibility_id,
          fr.responsibility_name
FROM fnd_user fu,

fnd_user_resp_groups fur,

fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.

order by user_name

Script to find Oracle API's for any module:

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name

Query used for audit point of view i.e. when a profile is changed and by which user 

SELECT t.user_profile_option_name,

profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date - v.last_update_date "Change Date",

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) "Created By",

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) "Last Update By"

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id =  :p_value

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = 'B'

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = 'B')

ORDER BY user_profile_option_name;

:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)

Check Current Applied Patch

SELECT patch_name, patch_type,

maint_pack_level,

creation_date

FROM applsys.ad_applied_patches

ORDER BY creation_date

Query used to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,

patch_level

FROM apps.fnd_application_vl a,

apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

This query will shows concurrent program processing time

SELECT f.request_id ,

pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference,

DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio

n||']',p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV('Lang')

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;

View all types of request Application wise

SELECT fa.application_short_name,

fcpv.user_concurrent_program_name,

description,

DECODE (fcpv.execution_method_code,

'B', 'Request Set Stage Function',

'Q', 'SQL*Plus',

'H', 'Host',

'L', 'SQL*Loader',

'A', 'Spawned',

'I', 'PL/SQL Stored Procedure',

'P', 'Oracle Reports',

'S', 'Immediate',

fcpv.execution_method_code

) exe_method,

output_file_type,

program_type,

printer_name,

minimum_width,

minimum_length,

concurrent_program_name,

concurrent_program_id

FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,

            Os_Process_ID Osid,

            Q.Concurrent_Queue_Name Manager,

            P.process_status_code Status,

            To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from   Fnd_Concurrent_Processes P,

            Fnd_Concurrent_Queues Q,

            FND_V$Process

where  Q.Application_Id = Queue_Application_ID

  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID

  and  Spid = Os_Process_ID

  and  Process_Status_Code not in ('K','S')

order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Print Oracle Apps versions

SELECT substr(a.application_short_name, 1, 5) code,

       substr(t.application_name, 1, 50) application_name,

       p.product_version version

FROM   fnd_application a,

       fnd_application_tl t,

       fnd_product_installations p

WHERE  a.application_id = p.application_id

AND    a.application_id = t.application_id

AND    t.language = USERENV('LANG');

************************************************END***************************