Home » Oracle » Useful Oracle provided sql scripts for R12.2

Useful Oracle provided sql scripts for R12.2

All the below scripts are present in $AD_TOP/sql

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).

ADZDSHOWLOGERR.sql

Show only error messages from online patching diagnostic log.

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.

See also  How to monitor transaction Rollback Progress

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.

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

 

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top