Sunday, May 20, 2007

Oracle Applications SQL scripts

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.

adcompsc.pls
Compile objects in a given schema

adcpresp.sql
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.

aderrch2.sql
Reports all compilation errors for a given schema.

aderrchk.sql
Same as aderrch2.sql plus it fails if there are any errors

adtresp.sql
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.

adutcobj.sql
Count objects by object type in schema

adutconf.sql
Utility script to display configuration of Applications

adutfip.sql
Utility script to display worker information

adutfpd.sql
Utility script to display product dependency information

ADXANLYZ.sql
Analyze all tables in an ORACLE ID with estimate sample 20%

ADXCKPIN.sql
Query the shared_pool area to determine space used by PL/SQL objects and whether they have been pinned.

ADXGNPIN.sql
Creates and runs a "pin" script for all packages and functions in a give schema

ADXGNPNS.sql
Creates and runs a "pin" script for all sequences in a give schema

ADXINMAI.sql
Install tables and views used by the Applications*DBA sql scripts.

adxirc.sql
AD - index - report columns

ADXLMCBC.sql
Live Monitor, Categorize Block Contention

ADXLMLSO.sql
Live Monitor, List Session Objects

ADXLMQMS.sql
Live Monitor, Query Monitor Statistics

ADXLPFLS.sql
Lock Problem, Find Lock Source

ADXLPSLU.sql
Lock Problem, Show Lock Users

adxpriv7.sql
grant privileges to a user

ADXRCSDC.sql
Report Configuration, Show Database Configuration formerly, config.sql (rollback, tablespace, data files)

ADXRCSTG.sql
Report Configuration, Select Table Grants

ADXRSEBH.sql
Estimate the effect of a bigger SGA cache on cache hit rate.

ADXRSESH.sql
Estimate the effect of a smaller SGA cache on cache hit rate.

ADXRSFIS.sql
Find the size (blocks, extents, extpct) of the given index.

ADXRSFTS.sql
Find the size (blocks, extents, extpct) of the given table.

ADXRSFUA.sql
Report the number of blocks used and the number of extents used for every table or index in every user in the database.

ADXRSLFS.sql
Report free extents in each tablespace.

ADXRSQDP.sql
Check for cache effectiveness for dc_xxxxx parameters' values.

ADXRSRTS.sql
Produce a brief database used space report.

ADXRSSIE.sql
Generate a list of tables and indexes whose next extent to be grabbed would be too large to be allocated in their corresponding tablespaces.

ADXRSSMF.sql
List tables and indexes with a number of allocated extents close to their max_extents.

ADXRSSMS.sql
Find space used for one's own segments.

ADXRSSRS.sql
Show v$rollstat statistics.

ADXRSSSU.sql
For a username, report the number of blocks used and the number of extents used for every table or index in that username.

ADXRSSTF.sql
Produce a brief report of database fragmentation by tablespace.

ADXRSSUS.sql
Report how much space each user has.

ADXUPLUP.sql
Generate a list of processes which the given user (NOT the database account's username) owns.

ADXUPSRU.sql
Show all users that have active transactions per Rollback Segment that they are writing to.

8 comments:

santosh said...

great job ,
Ive using only one or two sqls under
$AD_TOP/sql.
will start using some of these

rgds,
santosh

fhasweh said...

thanks santosh, i hope it will help you

fadi

Sam said...

Good Effort ,will be on my bookmrk for sure.

Sam
appsdbablog.com

fhasweh said...

thank you sam for your continues support i really appreciate it

fadi

OracleNewbie said...

This is excellent knowledge. THank you very much!

fhasweh said...

you are welcome oraclenewbie i hope it will help you
fadi

Suresh said...

Excellent piece of info!

suresh
http://applicationsdba.blogspot.com

fhasweh said...

thank you suresh, i liked your blog by the way

fadi