AOL Standard SQL Scripts

Oracle Apps Application Object Library SQL scripts

Oracle apps provides a number of scripts which are very useful for the data base administrator for DB monitoring & tracking purpose.

Here is the application server path were you can see the following SQL scripts.

Oracle Applications $FND_TOP/sql directory has a collection of scripts. Most of them can be used for different purposes.

SQL Script	Purpose
afchrchk.sql	Check requested table.columns for invalid trailing spaces and control characters. These characters cause 'FRM-40654: Record has been updated.' Errors when column is queried in a form. Please note that afchrchk.sql SHOULD NOT run against FND_FLEX_VALUES with option 3 (check for control characters) set to Yes, and automatic fix set to Yes. This will strip the control characters from the COMPILED_VALUE_ATTRIBUTES column. Hence, impacting the General Ledger natural account value sets as they lose all their segment qualifiers.
afcmcreq.sql	Prints the Log file names of the managers that can run a given request
afcmrrq.sql	For use by DBA's for quick look of all running requests
afcmstat.sql	Displays all defined Managers and shows current status of manager processes.
afffanld.sql	Diagnoses inconsistencies in key flexfield segment delimiters that may be introduced by the 10.7 upgrade because of an AutoInstall bug (492585).
affixcvr.sql	Fixes cross-validation rules and lines by deleting any which refer to non-existent flexfield structures, and by disabling any rules which have no lines. User must manually commit or rollback after execution.
afhlpfix.sql	Find data integrity problems in FND_HELP and delete the orphan rows.
afimchk.sql	Checks to see if the Concurrent Manager monitor is actually running
afimlock.sql	Script to detect if the Internal Concurrent Manager is in a gridlock situation with another oracle process. The script will print the user, process id, machine, program and the terminal which is causing the gridlock.
afimpmon.sql	Sets the PMON method based on the argument ( for CONC_PMON_METHOD )
afimveri.sql	Resets the Concurrent Manager monitor
afpub.sql	Grants select privileges and creates synonym for APPLSYS PUBlic account
afqpmex.sql	This script is used for sql tuning. The input is the name of a file containing a sql statement. The output is the execution plan for the statement along with information about the index columns and the sizes of the tables involved. The results are spooled to the file explain.out. This is an elaboration of the old-fashioned results from querying plan_table after running "explain plan for...". This script assumes the file contains a single, bare SQL statement, with no blank lines and no terminating ";" or "/". Also see afqpmexq.slq/explainq.sql
afqpmexq.sql	This script is used for sql tuning. (See description for afqpmex.sql)
afqpmind.sql	This script is used for sql tuning. The input is a table name (without a schema prefix) and the output is information about it's indexes and the total size of the allocated extents. The data for the DISTINCT_KEYS column comes from the analyze command.
afqpminq.sql	This script is used for sql tuning. The input is a table name (without a schema prefix) and the output is information about it's indexes. The data for the DISTINCT_KEYS column comes from the analyze command.Also see afqpmiq.sql/iq.sql.
afqpmiq.sql	This script is used for sql tuning.
afqpmlck.sql	This script is used for sql tuning. It lists all sessions that are waiting for a lock. The process holding the root of the lock tree is the left most process printed. Those printed to the right of it are waiting for locks to the left of it.
afqpmmws.sql	This script is used for performance monitoring. This script analyzes a table of wait samples. The single input parameter for the script is a table of wait samples which has the same columns as fnd_wait_samples.Some ways to create the input table:create or replace view wait_samples_view as select * from fnd_wait_samples; create table temp_wait_samples as select * from fnd_wait_samples;
afqpmmys.sql	This script is used for sql tuning. It lists the session id, user process id, server process id, OS user id, and Oracle user id for the current session.
afqpmmyw.sql	This script is used for sql tuning. It returns the cummulative totals for all wait events for the current session.
afqpmopd.sql	This script is used for sql tuning. The input is the process id of the Oracle server process. The output includes the session id, the user process id, and the OS user id.
afqpmpid.sql	This script is used for sql tuning. The input is an OS process id for the user process. The output includes the session id, server process id, and OS user id for the process.
afqpmrid.sql	This script is used for sql tuning. The input is a concurrent manager request_id, and the output is the operating system process id of the corresponding FNDLIBR process.
afqpmsid.sql	This script is used for sql tuning. The input is the Oracle session id and the output includes the user process id, the server process id, the OS user id, and the program being run.
afqpmsql.sql	This script is used for sql tuning. The input is the user process id and the output is a listing of the sql statement currently in progress.
afqpmsqx.sql	This script is used for sql tuning. The input is the user process id. The output is the execution plan and a description of the tables and their indexes (the same as is produced by qpmex.slq/explain.sql).
afqpmwta.sql	This script is used for sql tuning. It returns a snapshot of all the wait events in the database at this instant.
afqpmwti.sql	This script is used for sql tuning.The input is the user process id and the output includes the table or index name of the last I/O. This is a moderately long-running script. When the DELAY column contains "CPU - recent:" this indicates that the query is currently using CPU and the last non-CPU delay will be reported. When the current (or most recent) delay was not for IO, the TABLE_OR_INDEX_NAME and TYPE columns are null.
afqpmwtp.sql	This script is used for sql tuning. Input is the user process id and the output is a snapshot of the current action of the server process.
afqpmwtr.sql	This script is used for sql tuning. It returns a snapshot of all the wait events, but only for the real-time processes. (compare to afqpmwta.sql/wait_all.sql)
afrqpend.sql	Selects all the Pending Requests with status Q
afrqrun.sql	Lists all Running, Terminating, Paused Requests
afrqscm.sql	Prints the Log file names of the managers that can run a given request
afrqstat.sql	Summary of concurrent request execution since Date
afrqwait.sql	Selects all the Pending Requests with status Q
afsecchk.sql	Release 7.5 Referential Integrity Display
afsetpri.sql	Used to set Program level priority for concurrent programs
afsetseq.sql	Set ORACLE Sequence
afsetsqx.sql	Set ORACLE Sequence. The difference between this script (afsetsqx.sql) and afsetseq.sql is that this script performs EXIT at the end as it is called directly from driver files.
afsyn01.sql	Drop synonyms for old tables, views and sequences.
afuiddrv.sql	Sets all sequences
afwebdbg.sql	Print WebServer setup debugging information.
afxpmmws.sql	This script is used for performance monitoring. This script analyzes a table of wait samples. The single input parameter for the script is a table of wait samples which has the same columns as fnd_wait_samples.
fdmchk.sql	Check menu entry dangling references.
fdmfix.sql	Check & fix menu entry dangling references.
FNDATPRG.sql	Purge audit trail tables before a given date
FNDCPDC2.sql	Delete a concurrent program and SRS definition if needed, calls FNDCPDCP.sql
FNDCPDCP.sql	Delete a concurrent program and SRS definition if needed
fndfbdpm.sql	Deletes a flexbuilder parameter.
fndfbfxn.sql	Generate a list of FlexBuilder functions and parameters for a given application. Some parameters have multiple definitions, but this report lists each parameter only once.
fndfbprm.sql	Generate a list of FlexBuilder functions and parameters, including detailed information about how the parameter is defined for a given application. This report includes all definitions for each parameter, ordered by sequence number. The information in this report corresponds to information in the Define FlexBuilder Parameters form.
fndffbdd.sql	Finds independant segment values which don't have the default dependant segment value defined for them.
FNDFFCVS.sql	Copy flexfield value set from one database to another creates a sql script filename.sql which can be run on another database to copy value sets
FNDFFDDS.sql	Delete a descriptive flexfield definition from AOL tables
FNDFMFXR.sql	Forms Trigger Exception Report
fndgofpr.sql	Creates Grants Only for a Full PRivileged Oracle ID. This assumes fndgsspr.sql was run first to create a limited set of SELECT only privileges which are not recreated here.
fndgsnpa.sql	Revokes all AOL grants and drops all AOL synonyms from a user. Must also revoke synonym GL_CURRENCIES which uses FND_CURRENCIES.
fndgsspr.sql	Creates Grants and Synonyms for a Select only Privileged Oracle ID.
FNDMDCMR.sql	Creates a report of all messages for an application in the given language.The report is located in the application's message directory and has the name {language short name}.FDDTMFEXT. For example: /applications/fnd/5.0/mesg/usaeng.msg. The standalone FNDMDCMF (Create Message File) runs this report after creating the binary message file to produce a human-readable version.
fndmncpy.sql	Copy application menus to another, custom application (Rel. 9 to 10 backup)
FNDNLADD.sql	Add missing translation rows for FND _TL tables.
FNDNLCHK.sql	Check _TL tables for inconsistent or missing translation data. FNDNLCHK does not fix anything, it only reports bad data. Use FNDNLADD to fix errors found by FNDNLCHK.
fndnlhlp.sql	Updates fnd_loader_formats
FNDNLICR.sql	NLS Insert non-ISO currency
FNDNLINS.sql	Update _TL tables to populate rows for new language when a new language is installed.
FNDNLMVL.sql	Install multi-language versions of _VL views.
fndnmts.sql	Populate FND%_TL subtables with data from main FND% tables ( In 10.5 this script is superceded by FNDNLADD.sql and should not be used by customers. )
fndnstm.sql	Populate FND_ main tables with data from subtables.
fndrspfm.sql	Report on Users who Access a Given Form
FNDRSTST.sql	SRS Test Program - expects three arguments and prints them
fndscats.sql	Signon Audit Time Stamp
FNDSCETS.sql	Set end time stamp (FND_LOGINS, FND_LOGIN_RESPONSIBILITIES, FND_LOGIN_RESP_FORMS)
FNDSCGRP.sql	Reports on which applications, sets and programs have been assigned to which responsibility. Accepts application name and responsibility name.
FNDSCPRG.sql	Purge signon audit tables from given date
FNDUDUAL.sql	This script ensures only one record exists in FND_DUAL.
fndutcsq.sql	Converts rows in FND_UNIQUE_IDENTIFIER_CONTROL to Sequences
srstest.sql	SRS test script
TSTSQPLS.sql	SQL script to test execution methods for RTs.
wfbkg.sql	WorkFlow BacKGround engine, starts the background engine, running for the indicated number of minutes.
wfbkgchk.sql	WorkFlow Background Check. It displays a status report on background work waiting to be processed.
wfdirchk.sql	WorkFlow Directoy Servoices Data Model Check
WFNLADD.sql	Add missing translation rows for WF _TL tables.
wfnldat.sql	Add default language data for standalone Workflow install.
wfnlena.sql	Enable/disable an installed language (workflow)
wfntfsh.sql	WorkFlow NoTiFication SHow status
wfprot.sql	WorkFlow PROTection level reset. It resets the protection level for all objects in a specified item type to the supplied value. After resetting the protection level NOTHING in the item type will be customizable by a higher access level.
wfrefchk.sql	WorkFlow Primary,Unique and Foreign Key constraint checker. It checks for all invalid workflow data that is missing primary key data for a foreign key
wfretry.sql	WorkFlow Handle error'ed activity. It displays a list of errored activities for the indicated item. Type in the name of the activity, and command to skip, retry, or reset.
wfrmall.sql	WorkFlow ReMove ALL. It DELETES all workflow information. ALL OF IT.
wfrmbref.sql	Deletes all invalid workflow data that is missing primary key data for a foreign key
wfrmita.sql	WorkFlow ReMove ITem Attribute. It deletes all workflow information for the specified item attribute.
wfrmitms.sql	WorkFlow ReMove ITMeS. It removes item status information for items which match the supplied type and key patterns.
wfrmitt.sql	WorkFlow ReMove ITemType. It deletes ALL workflow information for the specified item type.
wfrmtype.sql	WorkFlow ReMove TYPE. It purges ALL runtime data associated with a given item type.
wfrun.sql	WorkFlow RUN a process. It creates and starts the specified process.
wfstat.sql	WorkFlow item STATUS report. It displays a status report on the indicated item (132 charachter output).
wfstatus.sql	WorkFlow item STATUS report. It displays a status report on the indicated item (132 charachter output).
wfver.sql	WorkFlow VERsion display. It displays version information for all WF source.
wfverchk.sql	WorkFlow Version Check. It checks all workflow activities for potentially invalid version histories (more than one version of an activity active at any given time). Correct any errors found.