Search This Blog

Saturday, July 13, 2013

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.

No comments:

Post a Comment