Handy Spool SQL

If we execute a single SQL*Plus script several times (for example because we run a report every week or when running a script in batch mode regularly), and the scriptspools the output to a file, we want to give a unique name to the spool file for each execution.

The following SQL*Plus script illustrates the timestamp method

column timecol new_value timestamp
select to_char(sysdate,’.MMDDYY_HHMISS’) timecol from sys.dual
/
spool output_&&timestamp
select sysdate from sys.dual
/
spool off

Depending on the operating system the spool file will have the suffix .LIS or .LST appended to it’s name by default. To replace the default ending with our own, for example ‘.txt’, change the script like suggested below.
 
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,’_MMDDYY_HHMISS’) timecol,’.txt’ spool_extension from sys.dual
/
spool output_&&timestamp&&suffix
select sysdate from sys.dual
/
spool off

Useful Queries Related to Concurrent Requests in 11i Applications

>Scheduled concurrent requests:

The below query will return all the concurrent requests which are scheduled using any of the above methods:
==========================================================
SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, ‘Report Set’, ‘Report Set:’ cr.description, cp.user_concurrent_program_name ) NAME, argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, ‘PERIODICALLY’, NVL2 (cr.release_class_id, ‘ON SPECIFIC DAYS’, ‘ONCE’) ) schedule_type, DECODE (NVL2 (cr.resubmit_interval, ‘PERIODICALLY’, NVL2 (cr.release_class_id, ‘ON SPECIFIC DAYS’, ‘ONCE’) ), ‘PERIODICALLY’, ‘EVERY ‘ cr.resubmit_interval ‘ ‘ cr.resubmit_interval_unit_code ‘ FROM ‘ cr.resubmit_interval_type_code ‘ OF PREV RUN’, ‘ONCE’, ‘AT :’ TO_CHAR (cr.requested_start_date, ‘DD-MON-RR HH24:MI’), ‘EVERY: ‘ fcr.class_info ) schedule, fu.user_name, requested_start_date FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu, apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = ‘P’ AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = ‘US’ AND fcr.release_class_id(+) = cr.release_class_id AND fcr.application_id(+) = cr.release_class_app_id;
==========================================================

Note: The “SCHEDULE” column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week. Positions 1 through 31: Specific day of the month. Position 32: Last day of the month Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments:

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules. Note: This query will return even though the request was submitted using a different responsibility.
==========================================================
SELECT request_id, NAME, argument_text, user_name FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, ‘Report Set’, ‘Report Set:’ cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = ‘P’ AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = ‘US’ AND fu.user_name NOT LIKE ‘PPG%’) t1 WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, ‘Report Set’, ‘Report Set:’ cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = ‘P’ AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = ‘US’ AND fu.user_name NOT LIKE ‘PPG%’) t2 WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text AND t1.user_name = t2.user_name GROUP BY NAME, argument_text, user_name HAVING COUNT (*) > 1) ORDER BY user_name, NAME
==========================================================

Average pending time per request:

This is a very useful query to check the performance of the concurrent managers. Average pending time for a request is calculated like below: (“Highest of Requested_start_date or Date_submitted” – Actual_start_date ) / Total requests A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc) We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

==========================================================
SELECT TO_CHAR (actual_start_date, ‘DD-MON-YYYY’) DAY, concurrent_queue_name, (SUM ( ( actual_start_date – (CASE WHEN requested_start_date > request_date THEN requested_start_date ELSE request_date END ) ) * 24 * 60 * 60 ) ) / COUNT (*) “Wait_Time_per_Req_in_Secs” FROM apps.fnd_concurrent_requests cr, apps.fnd_concurrent_processes fcp, apps.fnd_concurrent_queues fcq WHERE cr.phase_code = ‘C’ AND cr.actual_start_date IS NOT NULL AND cr.requested_start_date IS NOT NULL AND cr.controlling_manager = fcp.concurrent_process_id AND fcp.queue_application_id = fcq.application_id AND fcp.concurrent_queue_id = fcq.concurrent_queue_id GROUP BY TO_CHAR (actual_start_date, ‘DD-MON-YYYY’), concurrent_queue_name ORDER BY 2
==========================================================

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program:

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
==========================================================
SELECT user_concurrent_program_name, user_concurrent_queue_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc, apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = ‘P’ AND cqc.include_flag(+) = ‘I’ AND cp.LANGUAGE = ‘US’ AND cp.user_concurrent_program_name = ‘&USER_CONCURRENT_PROGRAM_NAME’ AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = ‘US’
==========================================================

To see all the pending / Running requests per each manager wise:

==========================================================
SELECT request_id, phase_code, status_code, user_name, user_concurrent_queue_name FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = ‘P’ OR cwr.phase_code = ‘R’) AND cwr.hold_flag != ‘Y’ AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id AND cq.LANGUAGE = 'US' AND cwr.requested_by = fu.user_id ORDER BY 5
==========================================================
Note: The same information can be seen in Administer Concurrent Manager form for each manager.

Checking the incompatibilities between the programs:

The below query can be used to find all incompatibilities in an application instance.
==========================================================
SELECT a2.application_name, a1.user_concurrent_program_name, DECODE (running_type, ‘P’, ‘Program’, ‘S’, ‘Request set’, ‘UNKNOWN’ ) “Type”, b2.application_name “Incompatible App”, b1.user_concurrent_program_name “Incompatible_Prog”, DECODE (to_run_type, ‘P’, ‘Program’, ‘S’, ‘Request set’, ‘UNKNOWN’ ) incompatible_type FROM apps.fnd_concurrent_program_serial cps, apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1, apps.fnd_application_tl a2, apps.fnd_application_tl b2 WHERE a1.application_id = cps.running_application_id AND a1.concurrent_program_id = cps.running_concurrent_program_id AND a2.application_id = cps.running_application_id AND b1.application_id = cps.to_run_application_id AND b1.concurrent_program_id = cps.to_run_concurrent_program_id AND b2.application_id = cps.to_run_application_id AND a1.language = ‘US’ AND a2.language = ‘US’ AND b1.language = ‘US’ AND b2.language = ‘US’
==========================================================

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

Useful Scripts

>To get list of responsibilities.
SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;
======================
To get Menus Associated with responsibility
SELECT DISTINCT a.responsibility_name, c.user_menu_name FROM apps.fnd_responsibility_tl a, apps.fnd_responsibility b, apps.fnd_menus_tl c, apps.fnd_menus d, apps.fnd_application_tl e, apps.fnd_application f WHERE a.responsibility_id(+) = b.responsibility_id AND a.responsibility_id = ‘20538’ AND b.menu_id = c.menu_id AND b.menu_id = d.menu_id AND e.application_id = f.application_id AND f.application_id = b.application_id AND a.LANGUAGE = ‘US’;
================
To get submenus and Function attached to this Main menu.
SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = ‘F4 UK PAY Navigator’;
=================
To get assigned responsibility to a user.
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);
================
To get responsibility and attached request groups.
SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name
=================
To get modified profile options.
SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date – v.last_update_date “Change Date”, (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) “Created By”, (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) “Last Update By” FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active = SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = ‘B’ UNION SELECT nls_language FROM fnd_languages WHERE installed_flag = ‘B’) ORDER BY user_profile_option_name;
=================
To get modified profile options.
SELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE, ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event, ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query, (SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by) “Created By ” FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID = ffft.function_id ORDER BY 1;
=====================
To get Patch Level.
SELECT a.application_name, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;
================
To get all Functions
SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl order by order by user_function_name;
==============
To get all Request attached to a responsibility
SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = ‘P’ AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name; /;
====================
To get all request with application
SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, ‘B’, ‘Request Set Stage Function’, ‘Q’, ‘SQL*Plus’, ‘H’, ‘Host’, ‘L’, ‘SQL*Loader’, ‘A’, ‘Spawned’, ‘I’, ‘PL/SQL Stored Procedure’, ‘P’, ‘Oracle Reports’, ‘S’, ‘Immediate’, fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description
===============
To Count Module Wise Report
SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, ‘B’, ‘Request Set Stage Function’, ‘Q’, ‘SQL*Plus’, ‘H’, ‘Host’, ‘L’, ‘SQL*Loader’, ‘A’, ‘Spawned’, ‘I’, ‘PL/SQL Stored Procedure’, ‘P’, ‘Oracle Reports’, ‘S’, ‘Immediate’, fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;
=========
To calculate request time
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) ‘ HOURS ‘ floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) – floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) ‘ MINUTES ‘ round((((f.actual_completion_date-f.actual_start_date)*24*60*60) – floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 – (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) – floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) ‘ SECS ‘ time_difference , DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name'[‘f.description’]’,p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV(‘Lang’) and f.actual_start_date is not null ORDER by f.actual_completion_date-f.actual_start_date desc; =================
Check responsibility assigned to a specific USER
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = ‘Purchasing’ ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)
===============
Check Current Applied Patch
SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC
=================

Script to purge old backups

>This script can be used to used to purge backups older than a given number of days (replace the argument after “mtime” with the required number of days). This script can be used in conjunction with the backup script in the previous post.

==========================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE=’/t2/backups/logs/appstech_backup_’${DATE}’.log’
ORACLE_BASE=/t2/oracle
ORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
LOG_HOME=/t2/backups/logs
APPS_BACKUP_LOC=/t2/backups/appstech
export ORACLE_BASE ORACLE_SID ORACLE_HOME LOG_HOME APPS_BACKUP_LOC
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp
SHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERMNLS_LANG=American_America.UTF8
export NLS_LANG
find $LOG_HOME -type f -mtime +7 xargs rm -f
find $APPS_BACKUP_LOC -type f -mtime +6 > $EXEC_DIR/apps_purge.list
if [ -s $EXEC_DIR/apps_purge.list ]
then
for file in `cat $EXEC_DIR/apps_purge.list`
do
rm ${file}
done
for address in `cat $EXEC_DIR/mail_list`
do
if [ “`echo $address cut -c1`” != “#” ]; then
mail -s “${ORACLE_SID} – Apps Purge Success Log” $address < $EXEC_DIR/apps_purge.list
sleep 1
fi
done
else
for address in `cat $EXEC_DIR/mail_list`doif [ “`echo $address cut -c1`” != “#” ]; then
echo “No Files found in backup directory to purge!!” mail -s “${ORACLE_SID} – Apps Purge Failiure Log” $address
fi
done
fi
find $EXEC_DIR/apps_purge.list xargs rm -f
==========================================================

Script to purge old database backups

>This script can be used to purge database backups taken using the script in the previous post. (replace the argument after “mtime” with the required number of days beyond which the backups can be deleted).

========================================================
DATE=`date +%d%b%y_%H_%M_%S`
LOGFILE=’/t2/backups/logs/dbtech_backup_’${DATE}’.log’
ORACLE_BASE=/t2/oracleORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0
ORACLE_SID=PROD
WORK=/t2/scripts
EXEC_DIR=/t2/scripts
LOG_HOME=/t2/backups/logs
DB_BACKUP_LOC=/t2/backups/dbtech
export ORACLE_BASE ORACLE_SID ORACLE_HOME LOG_HOME DB_BACKUP_LOC
TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp
SHELL=/usr/bin/ksh
LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
export TNS_ADMIN SHELL LD_LIBRARY_PATH
PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH
export PATH
TERM=vt100
export TERM
NLS_LANG=American_America.UTF8
export NLS_LANG
find $LOG_HOME -type f -mtime +7 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/bdump -type f -mtime +14 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/udump -type f -mtime +14 xargs rm -f
find $ORACLE_HOME/admin/PROD_perp/cdump -type f -mtime +14 xargs rm -f
find $DB_BACKUP_LOC -type f -mtime +6 > $WORK/purge.list
if [ -s $WORK/purge.list ]then
for
file in `cat $EXEC_DIR/purge.list`
do
rm ${file}
done
for address in `cat $EXEC_DIR/mail_list`
do
if [ “`echo $address cut -c1`” != “#” ]; then
mail -s “${ORACLE_SID} – DB Purge Success Log” $address < $WORK/purge.list
sleep 1
fi
done
else
for address in `cat $EXEC_DIR/mail_list`
do
if [ “`echo $address cut -c1`” != “#” ]; then
echo “No Files found in backup directory to purge!!” mail -s “${ORACLE_SID} – DB Purge Failure Log” $address
fi
done
fi
find $WORK/purge.list xargs rm -f
========================================================