In-built scripts with database.

List of inbuilt scripts with their usage:

Script Name Description
addmrpt.sql Automatic Database Diagnostic Monitor (ADDM) report
addmrpti.sql Automatic Database Diagnostic Monitor (ADDM) report
addmtmig.sql Post upgrade script to load new ADDM task metadata tables for task migration.
ashrpt.sql Active Session History (ASH) report
ashrpti.sql Active Session History (ASH) report. RAC and Standby Database support added in 2008.
ashrptistd.sql Active Session History (ASH) report helper script for obtaining user input when run on a Standby.
awrblmig.sql AWR Baseline Migration
awrddinp.sql Retrieves dbid,eid,filename for SWRF and ADDM Reports
awrddrpi.sql Workload Repository Compare Periods Report
awrddrpt.sql Produces Workload Repository Compare Periods Report
awrextr.sql Helps users extract data from the AWR
awrgdinp.sql AWR global compare periods report input variables
awrgdrpi.sql Workload repository global compare periods report
awrgdrpt.sql AWR global differences report
awrginp.sql AWR global input
awrinfo.sql Outputs general Automatic Workload Repository (AWR) information such as the size and data distribution
awrinput.sql Common code used for SWRF reports and ADDM
awrload.sql Uses DataPump to load information from dump files into the AWR
awrrpt.sql Automated Workload Repository (AWR) report
awrrpti.sql Automated Workload Repository (AWR) report
awrsqrpi.sql Reports on differences in values recorded in two different snapshots
awrsqrpt.sql Produces a workload report on a specific SQL statement
catalog.sql Builds the data dictionary views
catblock.sql Creates views that dynamically display lock dependency graphs
catclust.sql Builds DBMS_CLUSTDB built-in package
caths.sql Installs packages for administering heterogeneous services
catio.sql Allows I/O to be traced on a table-by-table basis
catnoawr.sql Script to uninstall AWR features
catplan.sql Builds PLAN_TABLE$: A public global temporary table version of PLAN_TABLE.
dbfs_create_filesystem.sql DBFS create file system script
dbfs_create_filesystem_advanced.sql DBFS create file system script
dbfs_drop_filesystem.sql DBFS drop file system
dbmshptab.sql Permanent structures supporting DBMS_HPROF hierarchical profiler
dbmsiotc.sql Analyzes chained rows in index-organized tables.
dbmspool.sql Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool.
dumpdian.sql Allows one to dump Diana out of a database in a human-readable format (exec dumpdiana.dump(‘DMMOD_LIB’);)


Shows various status of the embedded PL/SQL gateway and the XDB HTTP listener. It should be run by a user with XDBADMIN and DBA roles.
hangdiag.sql Hang analysis/diagnosis script
prgrmanc.sql Purges from RMAN Recovery Catalog the records marked as deleted by the user
recover.bsq Creates recovery manager tables and views … read the header very carefully if considering performing an edit
sbdrop.sql SQL*PLUS command file drop user and tables for readable standby
sbduser.sql SQL*Plus command file to DROP user which contains the standby statspack database objects
sbreport.sql This script calls sbrepins.sql to produce standby statspack report. It must run as the standby statspack owner, stdbyperf
scott.sql Creates the SCOTT schema objects and loads the data
secconf.sql Secure configuration script: Laughable but better than the default
spauto.sql SQL*PLUS command file to automate the collection of STATPACK statistics
spawrrac.sql Generates a global AWR report to report performance statistics on all nodes of a cluster
spcreate.sql Creates the STATSPACK user, table, and package
sppurge.sql Purges a range of STATSPACK data
sprepcon.sql STATSPACK report configuration.
sprepsql.sql Defaults the dbid and instance number to the current instance connected-to, then calls sprsqins.sql to produce the standard Statspack SQL report
sprsqins.sql STATSPACK report.
sql.bsq Drives the creation of the Oracle catalog and data dictionary objects.
tracetab.bsq Creates tracing table for the DBMS_TRACE built-in package.
userlock.sql Routines that allow the user to request, convert and release locks.
utlchain.sql Creates the default table for storing the output of the analyze list chained rows command.
utlchn1.sql Creates the default table for storing the output of the analyze list chained rows command.
utlconst.sql Constraint check utility to check for valid date constraints.
utldim.sql Build the Exception table for DBMS_DIMENSION.VALIDATE_DIMENSION.
utldtchk.sql This utility script verifies that a valid database object has correct dependency$ timestamps for all its parent objects. Violation of this invariant can show up as one of the following:

  • Invalid dependency references [DEP/INV] in library cache dumps
  •  ORA-06508: PL/SQL: could not find program unit being called
  • PLS-00907: cannot load library unit %s (referenced by %s)
  • ORA-00600[ kksfbc-reparse-infinite-loop]
utldtree.sql Shows objects recursively dependent on given object.
utledtol.sql Creates the outline table OL$, OL$HINTS, and OL$NODES in a user schema for working with stored outlines
utlexcpt.sql Builds the Exception table for constraint violations.
utlexpt1.sql Creates the default table (EXCEPTIONS) for storing exceptions from enabling constraints. Can handle both physical and logical rowids.
utlip.sql Can be used to invalidate all existing PL/SQL modules (procedures, functions, packages, types, triggers, views) in a database so that they will be forced to be recompiled later either automatically or deliberately.
utllockt.sql Prints the sessions in the system that are waiting for locks, and the locks they are waiting for.
utlpwdmg.sql Creates the default Profile password VERIFY_FUNCTION.
utlrdt.sql recompiles all DDL triggers in UPGRADE mode at the end of one of three operations:

  • DB upgrade
  • utlirp to invalidate and recompile all PL/SQL
  • dbmsupgnv/dbmsupgin to convert PL/SQL to native/interpreted
utlrp.sql Recompiles all invalid objects in the database.
utlscln.sql Copies a snapshot schema from another snapshot site
utlsxszd.sql Calculates the required size for the SYSAUX tablespace.
utltkprf.sql Grants public access to all views used by TKPROF with verbose=y option
utluiobj.sql Outputs the difference between invalid objects post-upgrade and those objects that were invalid preupgrade
utlu112i.sql Provides information about databases prior to upgrade (Supported releases: 9.2.0, 10.1.0 and 10.2.0)
utlvalid.sql Creates the default table for storing the output of the analyze validate command on a partitioned table
utlxaa.sql Defines a user-defined workload table for SQL Access Advisor. The table is used as workload source for SQL Access Advisor where a
user can insert SQL statements and then specify the table as a workload source.
utlxplan.sql Builds PLAN_TABLE: Required for Explain Plan, DBMS_XPLAN, and AUTOTRACE (replaced by catplan.sql)
utlxplp.sql Displays Explain Plan from PLAN_TABLE using DBMS_XPLAN built-in. Includes parallel run information
utlxpls.sql Displays Explain Plan from PLAN_TABLE using DBMS_XPLAN built-in. Does not include parallel query information
utlxrw.sql Builds the REWRITE_TABLE for EXPLAIN_REWRITE tests
xdbvlo.sql Validates XML DB schema objects
glogin.sql SQL*Plus global login “site profile” file. Add SQL*Plus commands here to executed them when en a user starts SQL*Plus and/or connects
plustrce.sql Creates the PLUSTRACE role required to use AUTOTRACE
pupbld.sql Creates PRODUCT_USER_PROFILE
dfltAccPwd.sql Checks for default accounts with default passwords
hanganalyze.sql Hang analysis script for stand-alone databases
hanganalyzerac.sql Hang analysis script for RAC clusters

January 7, 2013 · Sanjay · No Comments
Posted in: 3. All Scripts

Leave a Reply

You must be logged in to post a comment.