Oracle Queries

1. Identify the special character:

SELECT *
  FROM  table_name
 WHERE REGEXP_LIKE(column_name, '[char]');

Eg:-

select * from per_all_people_f where regexp_like (full_name,'[-]');

2. Concurrent Request Run time:

select ((actual_completion_date-actual_start_date)*24*60) MINUTES, 

actual_start_date ,

  actual_completion_date ,

 decode(PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phase_code,

  decode(STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') Status

  from apps.fnd_concurrent_requests

where request_id = <request_id>;

3. Oracle Database Version:

select version from v$instance;

4. Oracle Application Version:

SELECT release_name, applications_system_name, aru_release_name

  FROM fnd_product_groups;

5. Oracle Application Patch Details:

--table which stores the application patch details

SELECT *   FROM ad_applied_patches ;

6. Language Setup in SQL Developer:

ALTER SESSION SET NLS_LANUAGE = ‘AMERICAN’;

7. Query to get Database port number:

select listener_name, port db_port from FND_TNS_LISTENERS FTL, FND_TNS_LISTENER_PORTS FTLP

where ftl.listener_guid = ftlp.listener_guid

and listener_name like '%_DB';

8. Query to get Database Host/SID:

select HOST||'.'||domain HOST_NAME, VIRTUAL_IP||'.'||domain HOST_NAME1, INSTANCE_NAME SERVICE_ID from v$instance ins, FND_NODES fn

where upper(fn.host) = upper(ins.host_name)

and support_db = 'Y';

9. Query to get Supplier bank details:

----query to fetch AP supplier bank details with assignment level (Supplier/Site level)

SELECT APS.VENDOR_ID,

  VENDOR_NAME "Supplier Name",

  IEP.supplier_site_id VENDOR_SITE_ID,

  ASSA.VENDOR_SITE_CODE "Site Name",

  bank.bank_name "bank name",

  branch.bank_branch_name "Branch Name",

  ieb.bank_account_num "Account Number",

  DECODE(IEP.supplier_site_id, NULL, 'Supplier', 'Site') assignment_level

FROM IBY_PMT_INSTR_USES_ALL IPI,

  AP_SUPPLIERS APS,

  AP_SUPPLIER_SITES_ALL ASSA,

  iby_external_payees_all IEP,

  iby_ext_bank_accounts ieb,

  IBY_EXT_BANKS_V bank,

  IBY_EXT_BANK_BRANCHES_V branch

WHERE IPI.instrument_type  = 'BANKACCOUNT'

AND IPI.payment_function   = 'PAYABLES_DISB'

AND IPI.EXT_PMT_PARTY_ID   = IEP.EXT_PAYEE_ID

AND IEP.supplier_site_id   = ASSA.VENDOR_SITE_ID(+)

AND APS.VENDOR_ID          = NVL(ASSA.VENDOR_ID, APS.VENDOR_ID)

AND bank.bank_party_id     = ieb.bank_id

AND branch.branch_party_id = ieb.branch_id

AND IEP.PAYEE_PARTY_ID     = APS.PARTY_ID

AND IPI.instrument_id      = ieb.ext_bank_account_id;

10. Query to get locked session in ORACLE:

SELECT VS.SID ||', '||VS.SERIAL# KILL_SESSION,

  VS.USERNAME,   DBO.owner, DBO.object_name, DBO.object_type, vs.module, vs.machine, VS.OSUSER,

  VLO.ORACLE_USERNAME, FU.USER_NAME LOCKING_FND_USER_NAME, fl.start_time locking_fnd_user_login_time,

  vp.pid, vp.spid AS os_process, vs.status, vs.saddr, vs.audsid, vs.process

FROM V$SESSION VS,

  V$PROCESS VP,

  V$LOCKED_OBJECT VLO,

  DBA_OBJECTS DBO,

  FND_LOGINS FL,

  fnd_user fu

WHERE VS.PADDR    = VP.ADDR

AND VS.SID        = VLO.SESSION_ID

AND DBO.OBJECT_ID = VLO.OBJECT_ID

AND vp.spid       = fl.process_spid(+)

AND vp.pid        = fl.pid(+)

AND FL.USER_ID    = FU.USER_ID(+)

AND DBO.OBJECT_NAME LIKE NVL('%'|| UPPER (:OBJECT_NAME)|| '%', DBO.OBJECT_NAME)

AND NVL (VS.STATUS, 'XX') != 'KILLED';

11. Query to find all running concurrent requests

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;

12. QUERY to get the programs submitted by users

SELECT REQUEST_ID ,
  REQUESTOR ,
  PROGRAM ,
  DECODE (phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running') Phase,
  DECODE (STATUS_CODE, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Error', 'F', 'Scheduled', 'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting') status,
  COMPLETION_TEXT ,
  TO_CHAR(actual_start_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE ,
  TO_CHAR(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE ,
  APPLICATIONS_SYSTEM_NAME
FROM apps.fnd_conc_req_summary_v req,
  apps.FND_PRODUCT_GROUPS db
WHERE 1                            =1
AND REQUESTOR                      = 'SYSADMIN' --- Pass the requestor USER_NAME
AND PROGRAM                        = 'Workflow Background Process'  --- Pass the concurrent program name
AND TRUNC(actual_completion_date) >= TRUNC(SYSDATE-7)
ORDER BY request_id ASC;


13. QUERY to get concurrent programs created or updated by users

SELECT * FROM (
SELECT
  papp.application_name,
  user_concurrent_program_name,
  'Header Details' CONTEXT,
  fcpv.creation_date,
  (select user_name from fnd_user fu where user_id = fcpv.created_by) created_by, 
  fcpv.last_update_date,
  (select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
     apps.fnd_concurrent_programs_vl fcpv   
WHERE papp.application_id      = fcpv.application_id
UNION
SELECT papp.application_name,
  user_concurrent_program_name,
  'Parameter Details: SEQ:'
  ||COLUMN_SEQ_NUM CONTEXT,
   fcpv.creation_date,
  (select user_name from fnd_user fu where user_id = fcpv.created_by) created_by, 
  fcpv.last_update_date,
  (select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
  apps.fnd_concurrent_programs_vl fcpv,
  apps.FND_DESCR_FLEX_COLUMN_USAGES fdc
WHERE papp.application_id          = fcpv.application_id
AND fdc.descriptive_flexfield_name = '$SRS$.'||fcpv.concurrent_program_name
AND fdc.application_id = fcpv.application_id)
WHERE USER_CONCURRENT_PROGRAM_NAME = '1096 - Annual Summary and Transmittal of U.S. Information Returns'   --- Pass the concurrent program name
order by 2;