APPS DBA Scripts

Oracle Applications System Administration Scripts [ID 214088.1]
		Use These First! If the associated script does not allow you to solve your issue, then please upload the output file from the script when you first log a Service Request (SR)

Applications Performance

Locks for given Session ID

• Note: 200590.1 bde_session_locks.sql(<-- Click here for details & download)
  This script creates a report with locks for one session that is not responding (hanging), because of a Lock.




---

Validates and Rebuilds Fragmentated Indexes

• Note: 182699.1 bde_rebuild.sql(<-- Click here for details & download)
  This script validates and proceeds to generate a dynamic script to rebuild them. 




---

Analyze Indexes to determine Fragmentation

• Note: 182049.1 bde_analyze_indexes.sql(<-- Click here for details & download)
  This script will analyze indexes to determine fragmentation




---

Current, Required and Recommended Apps 11i init.ora params

• Note: 174605.1 bde_chk_cbo.sql(<-- Click here for details & download)
  This script displays the current value, the required value (if any), the recommended value (if any), and the default value (if any). 




---

Simple Explain Plan for given SQL Statement

• Note: 174603.1  bde_x.sql(<-- Click here for details & download)
  Script generates a Simple Explain Plan for one SQL statement, required to diagnose apps performance issues (transaction tuning). 




---

Expensive SQL and Resources Utilization for a Given Session ID

• Note: 169630.1 bde_session.sql(<-- Click here for details & download)
  This script creates a report with relevant information for one session that is either performing poorly or not responding (hanging). 




---

Verifies Statistics for all Installed Apps Modules 11.5

• Note: 163208.1 bde_last_analyzed.sql(<-- Click here for details & download)
  Script verifies statistics for all installed Apps modules 11.5




---

Changes CBO Stats Selectivity for a Given Index and Column

• Note: 157276.1 bde_chg_stats.sql(<-- Click here for details & download)
  Script that changes CBO stats selectivity for a given index and column 




---

Clone Views Across Instances for SQL Tuning Exercises

• Note: 156972.1 coe_view.sql(<-- Click here for details & download)
  This script facilitates cloning views across instances for SQL tuning exercises




---

Trace Apps Online Transactions with Event 10046

• Note: 156969.1 coe_trace.sql(<-- Click here for details & download)
  This script will Generates SQL Trace with bind variables and waits information for an Oracle Applications Form (equivalent of Event 10046 level 12) 




---

Automate CBO Stats Gathering

• Note: 156968.1 coe_stats.sql(<-- Click here for details & download)
  This script automates CBO stats gathering using FND_STATS and table sizes




---

Top 10 Expensive SQL from SQL Area

• Note: 156967.1  coe_sqlarea.sql(<-- Click here for details & download)
  This script scans sql area and sql text v$ dynamic performance views and displays Top n SQL Statements in terms of resources utilization. 




---

SQL Tracing online transactions using Event 10046

• Note: 156966.1 coe_event_10046.sql(<-- Click here for details & download)
  Process and Session info for one Concurrent Request




---

Session and Serial# for Locked Rows

• Note: 156965.1 coe_locks.sql(<-- Click here for details & download)
  This script displays all sessions holding a lock on a table or row. 




---

Import CBO Stats from COE_STATTAB_XYZ

• Note: 156964.1 coe_import_stattab.sql(<-- Click here for details & download)
  This script uploads data dictionary statistics from table COE_STATTAB_XYZ...




---

Enhanced Explain Plan for given SQL Statement

• Note: 215187.1 SQLTXPLAIN.sql(<-- Click here for details & download)
  Given one SQL Statement as input, generate a comprehensive report.. (sql tuning).




---

Audit Trail

Active Users executing FORMs or Conc Programs

• Note: 233871.1 FNDFindActiveUsers115.sql(<-- Click here for details & download)
  This script generates a list of active apps users with at least one open form, or executing at least one concurrent program. 




---

Trace

Enhanced Explain Plan for given SQL Statement

• Note: 179848.1 bde_system_event_10046.sql(<-- Click here for details & download)
  This script is used to turn SQL trace ON with LEVEL 12 for any concurrent program that starts its execution AFTER the EVENT 10046 is turned ON at the SYSTEM level.
  
• Note: 224270.1 TRCANLZR.sql(<-- Click here for details & download)
  Trace Analyzer - Interpreting Raw SQL Traces generated by EVENT 10046