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.
|
Script to initialize the context of profiles
Sometimes you want to run queries that reference profile options. To do this, you should first initialize the context of the current session to the user, responsibility and application being used. You can also set the context of the current inventory organization for queries that reference the organization context.
a. Set context
PROMPT Initialize context of profiles, etc.
PROMPT Note you can query the user id, responsibility id,
PROMPT and application id from the FND tables.
execute fnd_global.APPS_INITIALIZE(&UserID, &ResponsibilityID, &ApplicationID);
b. Set organization
PROMPT Set organization
PROMPT Note you can query the organization idea from mtl_parameters.
EXECUTE fnd_client_info.set_org_context('&OrganizationID');
Script to find out the values of a profile option
Script to initialize the context of profiles:
No comments:
Post a Comment