Oracle Applications has useful collection of ready SQL scripts under $AD_TOP/sql. The following list shows the description of each script. For more details like if a specific script is available on a specific Apps version you need to check metalink note 108207.1 you will find the script and its description below.
Compile objects in a given schema
The script duplicates rows in FND_RESPONSIBILITY in the following way: Find data_group_id dg_id for the given data_group_name. For each row with data_group_id 0 in FND_RESPONSIBILITY, look for a corresponding row with data_group_id dg_id, with the same application_id, and responsibility_name that only differs in the given suffix string suffix_string. If such a row does not exist for the data_group_id dg_id, insert it.
Reports all compilation errors for a given schema.
Same as aderrch2.sql plus it fails if there are any errors
A fix for customers who have more than one set of books and they installed languages other than AMERICAN English. The symptom of the bug is that responsibility names are not translated properly for non-Standard data groups.
Count objects by object type in schema
Utility script to display configuration of Applications
Utility script to display worker information
Utility script to display product dependency information
Analyze all tables in an ORACLE ID with estimate sample 20%
Query the shared_pool area to determine space used by PL/SQL objects and whether they have been pinned.
Creates and runs a "pin" script for all packages and functions in a give schema
Creates and runs a "pin" script for all sequences in a give schema
Install tables and views used by the Applications*DBA sql scripts.
AD - index - report columns
Live Monitor, Categorize Block Contention
Live Monitor, List Session Objects
Live Monitor, Query Monitor Statistics
Lock Problem, Find Lock Source
Lock Problem, Show Lock Users
grant privileges to a user
Report Configuration, Show Database Configuration formerly, config.sql (rollback, tablespace, data files)
Report Configuration, Select Table Grants
Estimate the effect of a bigger SGA cache on cache hit rate.
Estimate the effect of a smaller SGA cache on cache hit rate.
Find the size (blocks, extents, extpct) of the given index.
Find the size (blocks, extents, extpct) of the given table.
Report the number of blocks used and the number of extents used for every table or index in every user in the database.
Report free extents in each tablespace.
Check for cache effectiveness for dc_xxxxx parameters' values.
Produce a brief database used space report.
Generate a list of tables and indexes whose next extent to be grabbed would be too large to be allocated in their corresponding tablespaces.
List tables and indexes with a number of allocated extents close to their max_extents.
Find space used for one's own segments.
Show v$rollstat statistics.
For a username, report the number of blocks used and the number of extents used for every table or index in that username.
Produce a brief report of database fragmentation by tablespace.
Report how much space each user has.
Generate a list of processes which the given user (NOT the database account's username) owns.
Show all users that have active transactions per Rollback Segment that they are writing to.