Oracle provides a suite of built-in SQL scripts for E-Business Suite R12.2 to assist DBAs with advanced diagnostics and troubleshooting. Located primarily in the AD and FND directories, these scripts allow administrators to safely query the dual-file system, validate concurrent manager statuses, and generate critical system health reports for Oracle Support
All the below scripts are present in $AD_TOP/sql in R12.2.x
ADZDDBCC.sql
This sql is for database compliance checker, shows violations of the database object development standards described later in this document. Warning: this script takes a long time to run.
Usage sqlplus apps/<appspass> @$AD_TOP/sql/ADZDDBCC.sql
ADZDSHOWED.sql
This sql show database editions and current edition.
Usage sqlplus apps/<appspass> @$AD_TOP/sql/ADZDSHOWED.sql Edition Name Type Status Current? --------------- -------- -------- -------- ORA$BASE RETIRED V_20180202_149 OLD RETIRED V_20190105_2457 RUN ACTIVE CURRENT V_20190208_1436 PATCH ACTIVE
ADZDSHOWLOG.sql
Show full diagnostic log for online patching infrastructure.
Usage sqlplus apps/appspass @$AD_TOP/sql/ADZDSHOWLOG.sql
ADZDSHOWLOGEVT.sql
Show only event and error messages from online patching diagnostic log (a useful summary, without the detailed statement text).
Usage
sqlplus apps/appspass @ADZDSHOWLOGEVT.sql
ADZDSHOWLOGERR.sql
Show only error messages from online patching diagnostic log.
Usage
sqlplus apps/appspass @ADZDSHOWLOGERR.sql
ADZDSHOWEV.sql
It shows  editioning view column mapping for table.
Usage ADZDSHOWEV.sql TABLE_SYNONYM_NAME $AD_TOP/sql/ADZDSHOWEV.sql FND_NODES -- EV Column Mapping VIEW_COLUMN -> TABLE_COLUMN ------------------------------ ---- ------------------- NODE_ID = NODE_ID NODE_NAME = NODE_NAME COMMENTS = COMMENTS
ADZDSHOWTAB.sql
This sql shows table information and related objects.
Usage ADZDSHOWTAB.sql TABLE_SYNONYM_NAME
ADZDSHOWMV.sql
Show materialized view information and related objects.
Usage
ADZDSHOWMV.sql MVIEW_NAME
sqlplus @ADZDSHOWMV XYZ_SCHEMAS_MV
-- MV Objects
OBJECT_NAME OBJECT_TYPE STATUS DESCRIPTION
------------------------------ ------------------- ---------- -------------------
XYZ_SCHEMAS_MV MATERIALIZED VIEW VALID Materialized View
XYZ_SCHEMAS_MV TABLE VALID Container Table
XYZ_SCHEMAS_MV# VIEW VALID Logical View
-- MV Properties
MV_NAME REFERS REFRESH_ STALENESS
------------------------------ ------ -------- -------------------
XYZ_SCHEMAS_MV DEMAND FORCE FRESH
ADZDSHOWTS.sql
Show important tablespace status. Ensure that you have enough SYSTEM tablespace.
Usage sqlplus system/systempass @$AD_TOP/sql/ADZDSHOWTS.sql ========================================================================== Important Tablespace Status ========================================================================= TABLESPACE_NAME TOTAL_SPACE (GB) USED_SPACE (GB) FREE_SPACE (GB) PCT_USED ---------------- ---------------- ------------------------------ -------- APPS_TS_SEED 14.10 3.88 10.22 27.54 APPS_TS_TX_DATA 291.00 256.43 34.57 88.12 APPS_TS_TX_IDX 245.30 214.37 30.93 87.39 SYSTEM 52.99 17.04 52.99 17.04
ADZDCMPED.sql
Compare Patch Edition with Run Edition. Warning: this script may take a long time to run.
ADZDSHOWDDLS.sql
Show stored DDL summary by phase.
ADZDALLDDL.sql
Show stored DDL statement text.
ADZDDDLERROR.sql
Show stored DDL execution errors and messages.
adutlrcmp.sql
Recompile all objects, with before/after status report. Warning: this script may take a long time to run.
ADZDSHOWOBJS.sql
Show Object Summary per edition. Counts of actual and stub (inherited) editioned object per edition.
Usage
sqlplus system/systempass @$AD_TOP/sql/ADZDSHOWOBJS.sql
ADZDSHOWAOBJS.sql
Show Actual Objects in the current edition. These are the editioned objects that have been changed by the patch.
ADZDSHOWIOBJS.sql
Show Inherited Objects in the current edition. These are the editioned objects that remain untouched in the patch edition. This script is used to confirm that the adop actualize_all command has worked properly.
ADZDSHOWCOBJS.sql
Show Covered Object Summary per edition. Count of objects in old editions that have a replacement in the run edition. This script is used to confirm that the adop cleanup command has worked properly.
ADZDSHOWCOBJX.sql
Show Covered Object List. List of objects in old editions that have a replacement in the run edition.
ADZDSHOWSM.sql
Show Seed Manager status.
ADZDSHOWTM.sql
Show Table Manager status.
ADZDSHOWAD.sql
AD (online patching) database object status
ADZDSHOWSES.sql
Show sessions connected to the database (by edition).
ADZDSHOWDEP.sql
This sql script show objects that OBJECT_NAME depends on.
Usage sqlplus apps/<appspass> @$AD_TOP/sql/ADZDSHOWDEP.sql  OBJECT_NAME
Â
ADZDSHOWDEPTREE.sql
Show full dependency tree of objects that OBJECT_NAME depends on.
Usage sqlplus apps/<appspass> @$AD_TOP/sql/ADZDSHOWDEPTREE.sql OBJECT_NAME
 I hope your find this article useful
Related Articles
- R12.2 Online patching cycle summary
- ADOP explained
- Downtime and Hotpatch apply mode in adop R12.2
- How to rollback the patch after failed cutover phase in R12.2
- what happens in cutover phase of adop in R12.2
- what happens in the adop phase prepare