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