AppsWorld
Friday, January 25, 2013Query to fetch the Latest CPU patch details from Database
column BUG format a8;
column PATCH format a60;
set linesize 100;
set pagesize 200;
select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in ('13979374','13979377','13979372','13979375','13621942','13621941','13621940',
'13621939','13322561','12794416','13322559','13322557','12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913','11660357''11660356','11660355','11660354')
order by patch;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Issue : ORA-06502: PL/SQL: numeric or value error: character string buffer too small
while creating the AWR report
FIX :
create table WRH$_SQLTEXT_BKP as select * from WRH$_SQLTEXT;
update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
Script to create new user with existing DB user Privs
userdetails_new.sql
===============
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept new_sch_password prompt "Enter new user password: "
set serveroutput on
spool CREATE_GRANT_SCH_spool.sql
-- Create user...
select 'create user &&newname identified by &&new_sch_password'||''||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant on base user objects...
select 'grant select,insert,update,delete,index on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('TABLE') and owner = upper('&&oldname');
select 'grant select,insert,update,delete on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('VIEW') and owner = upper('&&oldname');
select 'grant select on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE='SEQUENCE' and owner = upper('&&oldname');
select 'grant execute on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') and owner = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'" to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'"('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
spool off
set pages 1000 feed on veri on lines 500
PROMPT
PROMPT
PROMPT Please Execute : CREATE_GRANT_SCH_spool.sql
PROMPT
PROMPT
gen_synonym_sch_user.sh
===================
echo "Enter Base User Name"
read base_user
echo "Enter new SCH User Name"
read sch_user
${ORACLE_HOME}/bin/sqlplus -s /nolog << SQLEND
conn /as sysdba
set feedback off
set pages 0
set lines 150
set heading off
spool CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
select 'create SYNONYM ${sch_user}.' || OBJECT_NAME || ' for ' || OWNER||'.'||OBJECT_NAME ||';' from dba_objects where OBJECT_TYPE in ('TABLE','SEQUENCE','PROCEDURE','FUNCTION','VIEW') and owner = upper('${base_user}');
select 'create SYNONYM ${sch_user}.' || SYNONYM_NAME || ' for ' || TABLE_OWNER ||'.'||TABLE_NAME||';' from dba_SYNONYMs where OWNER=upper('${base_user}');
spool off
SQLEND
chmod 777 CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
echo " "
echo " "
echo "Please Execute : CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql "
echo " "
echo " "
For OTM Users
=============
create user otmgreportdev_sch identified by otmgreportdev#12sch;
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
or
create user otmgreportdev_sch identified by otmgreportdev#12sch
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
Advanced Compression in 11G
Script to find the Compression eligible objects
==================================================
set linesize 150
set pagesize 100
col owner format a15
col segment_name format a32
col segment_type for a15
col tablespace_name format a20
set lines 100
select owner,
segment_name,
segment_type,
tablespace_name,
Size_in_GB
from (select owner,
segment_name,
segment_type,
tablespace_name,
round((bytes/1024/1024/1024),2) Size_in_GB
from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_type in ('TABLE')
and segment_name not in (select table_name from dba_tables where (compression='ENABLED' or table_name like '%$%'))
order by 5 desc)
where rownum <= 40
/
Use below command to Move the obejctes
=======================================
alter table APPLSYS.WF_NOTIFICATION_OUT move compress for OLTP;
Check the UNUSABLE indexes and rebuild
=======================================
select 'alter index ' || TABLE_OWNER || '.' || INDEX_NAME || ' rebuild parallel 4 ;' from dba_indexes where status='UNUSABLE';
run gather stats on compressed objects
Monitor the respective tablespaces and temp tablespace ,and check pre and post invalids
li.sh --to check the invalids
======
echo "Purpose: To spool out the invalid objects before and after applying the patch"
if [ $# -ne 2 ]; then
echo "Usage: $0 <appspass> <pre|post>"
exit 1;
fi
genInvList(){
USER=apps
PASS=$1
TYPE=$2
DATEFMT=`date +%Y%m%d-%H%M`
if [ "x$ORACLE_SID" = "x" ]; then
if [ "x$TWO_TASK" = "x" ]; then
echo "set up the env first, duh!"
exit
else
DB=`echo $TWO_TASK`
fi
else
DB=`echo $ORACLE_SID`
fi
sqlplus -s /nolog<<_END_
conn ${USER}/${PASS}@${DB}
set pages 10000
col owner for a14
col object_type for a20
col status for a10
col OBJECT_NAME for a30
spool ${DB}-${TYPE}-invalid-${DATEFMT}.lst
select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
select instance_name,status from v\$instance;
select owner, object_type, object_name
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
select owner, count(status)
from dba_objects
where status = 'INVALID'
group by owner;
select 'Total Invalids: '||count(*) from dba_objects where status != 'VALID';
spool off
exit
_END_
}
genInvList $1 $2
exp : li appspwd nameof thefile
Daily Needs for a DBA
=================
to fetch the apps pwd from 11.5.10.2
============================
grep -i pass $IAS_CONFIG_HOME/A*/modplsql/cfg/*.app
To kill the hung concurrent requests
==========================
update fnd_concurrent_requests set status_code='X' , phase_code='C' where request_id='150120296';
To fetch the code from backend
===========================
set pagesize 0
set linesize 200
Set head off
Spool GEWE_IR_SR_APPROVE_PKG.24Jun12.sql
select text from dba_source where NAME like 'GEWE_IR_SR_APPROVE_PKG%'
and type like '%PACKAGE%';
spool off
To generate the RSA and DSA public keys
=============================
inputs will be NONE ..all ENTER
ssh-keygen -t rsa --to generate RSA public key
/usr/bin/ssh-keygen -t dsa --to generate DSA public key
Purge the log files
===============
find . -name "*.req" -mtime +15 -exec rm {} \; --to purge 15 days old
Cluster command to check the current clusters from root in solaris
===============================================
/opt/VRTS/bin/hares -state |grep instance name |grep `hostname`|egrep -v 'mount|public|dg|share|nfs|mnt'
/opt/VRTSvcs/bin/hagrp -display instance name| grep -i frozen --to chk the database cluster status
/opt/VRTSvcs/bin/hagrp -freeze erpshst1 -sys `uname -n` --to keep in freeze mode
/opt/VRTS/bin/hares -offline orpogpp1_app_X -sys `hostname` --to bring down
/opt/VRTS/bin/hares -clear orpogpp1_app_X -sys `hostname` --to clear if its failed
/opt/VRTS/bin/hares -online orpogpp1_app_X -sys `hostname` --to bring up
To check the printer from backend
==========================
lpstat -a DSALBCGEIDTAG1
set bachspace to remove in solaris
===============================
stty erase ^h
Compiling forms,libraries,reports in Oracle Apps
=====================================
You all are may be well aware that we use f60gen for compiling forms and libraries in Oracle Applications Version 11i,But this is deprecated in R12 and we need to use frmcmp(Form Compiler) To compile forms,menus,PL/SQL libraries.
Compiling library files
11.5.9 and 11.5.10
$ f60gen module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special
R12
$ frmcmp_batch module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special
Compiling forms
11.5.9 and 11.5.10
[applmgr@oracle vis11appl]$export FORMS60_PATH=$FORMS60_PATH:$AU_TOP/fomrs/US
[applmgr@oracle vis11appl]$ echo $FORMS60_PATH
/apps/appl/vis11appl/au/11.5.0/resource:/apps/appl/vis11appl/au/11.5.0/resource/stub:
/apps/appl/vis11appl/au/11.5.0/forms/US
[applmgr@oracle vis11appl]$
f60gen module=$AU_TOP/forms/US/APXSUMBA.fmb userid=apps/appspasswd
output_file=$PROD_TOP/forms/US/APXSUMBA.fmx module_type=form
batch=no compile_all=special
R12
$ frmcmp_batch module=<path to the fmb file> userid=apps/<apps pwd>
output_file=<path to the fmx file> compile_all=special batch=yes
Compiling reports
Syntax
adrepgen userid=APPS/APPS
source=/<full path of schema top>/reports/<report name>.rdf
dest=/<full path of schema top>/reports/<report name>.rdf stype=rdffile
dtype=rdffile logfile=<report name>.log overwrite=yes batch=yes dunit=character
Example
adrepgen userid=apps/appspasswd source=/test/applmgr/11510/ap/11.5.0/reports/US/APXPBBLD.rdf dest=/test/applmgr/11510/admin/TEST/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/test/applmgr/11510/admin/TEST/out/adrep001.txt overwrite=yes batch=yes compile_all=yes
****what is the diff between batch=yes and no *******
If batch=yes -> it won't display the output,If batch=no -> it will display output
To Enable trace using running sql sessions
==============================
connect / as sysdba
oradebug setospid 84
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
<wait for some considerable time>
oradebug event 10046 trace name context off
To Check the version of the files
=======================
strings -a ./hxc/11.5.0/patch/115/sql/hxcafnawf.pkb |grep '$Header'
adident Header ./hxc/11.5.0/patch/115/sql/hxcafnawf.pkb
Killing All processes at once:
===============================
Yesterday we come across a situation where we have to kill all look-a-like processes at once, this is what we followed to get rid of it.
kill -9 `ps -ef | grep -i applmgr | grep -i 889 | grep -v grep | awk '{print $2}'`
ps -efw | grep -i applmgr | grep | grep -v grep | awk '{print "kill -9 "$2}'
To avoid the relink set the below memory parameter
=====================================
Relink Issue:
$LDR_CNTRL="MAXDATA=0x40000000"
$export LDR_CNTRL
Move files for 30 days old
=====================
mv `find . -mtime +30` /opt/oracle/apps/common/OICP/inbound/XXATFAQS/processed/archive/ --move files older then 30 days
To verify the Stats
==============
set serveroutput on
exec fnd_stats.verify_stats('APPLSYS','FND_CONCURRENT_REQUESTS');--stats verification
SQL> exec dbms_stats.gather_table_stats ( -
ownname => 'INV', -
tabname => 'MTL_TRANSACTION_ACCOUNTS' -
)> > >
/
R12 Compile JSP:
==================
$FND_TOP/patch/115/bin
ojspCompile.pl
ojspCompile.pl --compile --fast --quiet
and clear persistence for login issues
Clear directory under persistence from below directory and restart all three OC4J
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*
SQL Profile from backend
=====================
Create task ..it needs sql id and Name of task
=================================
DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := dbms_sqltune.create_tuning_task (
sql_id => '7asddw0r2wykx'
,plan_hash_value => NULL
,SCOPE => 'COMPREHENSIVE'
,time_limit => 3000
,task_name => 'C2CTST_Issue2'
,description => NULL);
END;
/
Execute task
==========
exec dbms_sqltune.execute_tuning_task (task_name => 'C2CTST_Issue2');
View the report and analyze
====================
set long 100000
set longchunksize 10000
set linesize 10000
set pages 10000
SELECT dbms_sqltune.report_tuning_task ('C2CTST_Issue1') FROM DUAL;
Output is not feasible then drop using below
================================
exec dbms_sqltune.drop_tuning_task(task_name => 'OPRD_CMchange');
Query to check latest roll ups
===============================
select bug_number Bug, decode(bug_number,3262159, 'Patch 11i.FND.H',
3262919, 'Patch 11i.FWK.H',3126422, '11.5.9 CU1',
3171663, '11.5.9 CU2',
3140000, '11.5.10 Maintenance Pack',
3240000, '11.5.10 Consolidated Update 1 (CU1)',
3640000, '11.5.10.1 Maintenance Pack',
4017300, '11.5.10 Consolidated Update 1 (CU1) for ATG Product Family',
3460000, '11.5.10 Consolidated Update 2 (CU2)',
3480000, '11.5.10.2 Maintenance Pack',
4125550, '11.5.10 Consolidated Update (CU2) for ATG Product Family',
3438354, 'Patch 11i.ATG_PF.H',
4334965, '11i.ATG_PF.H Rollup 3',
4676589, '11i.ATG_PF.H.RUP4',
5473858, '11i.ATG_PF.H.RUP5',
5903765, '11i.ATG_PF.H.RUP6',
6241631, '11i.ATG_PF.H.RUP7') Patch,
b.LAST_UPDATE_DATE Last_update
from AD_BUGS b
where b.BUG_NUMBER in ('3262919','3262159','3126422','3171633','3140000','3240000','3640000','4017300','3460000','3480000','4125550','3438354','4334965','4676589','5473858','5903765','6241631')
order by patch;
User's Responsibilities:
============================
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = 'ORAKOUSHIKA'
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;
FIND REDO LOGS INFO:
===================
select a.group#, a.bytes/(1024*1024) mb, a.members, b.member
from v$log a, v$logfile b
where a.group#=b.group#
order by a.group#;
Monitoring Cursors:
===================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Querry to check Table-Defragmentation:
=====================================
select owner,table_name,round((blocks*8),2)/(1024) "TABLE SIZE",round((num_rows*avg_row_len/1024),2)/(1024) "ACTUAL DATA" ,
( round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) )/(1024) "Wasted Space in Table"
from dba_tables
WHERE owner not in ( 'SYS' , 'SYSTEM' ) and ( round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) )/(1024) is not null
order by 5 desc
DROP Issue
==============
SQL> DROP TABLE GEPSMFG.GEPS_LOB_TEMP CASCADE CONSTRAINTS;
SP2-0544: Command "drop" disabled in Product User Profile
SQL> select char_value from product_user_profile where ATTRIBUTE='DROP' and PRODUCT='SQL*Plus';
CHAR_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DISABLED
SQL> update product_user_profile set char_value='ENABLED' where ATTRIBUTE='DROP' and PRODUCT='SQL*Plus';
1 row updated.
SQL> commit;
Commit complete.
SQL>
DB Keep Pool
=============
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
Stored Outlines:
================
http://www.oracle-base.com/articles/misc/Outlines.php
SELECT SQL_ID,HASH_VALUE FROM V$SQL WHERE SQL_ID='bknbsrc9yaxfq';
SELECT SQL_TEXT FROM V$SQL WHERE HASH_VALUE='333805014';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='9.2.0';
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 333805014,
child_number => 0,
category => 'C2C2_OUTLINES2');
END;
/
SQL> SELECT name, category, sql_text FROM user_outlines WHERE category = 'C2C2_OUTLINES2';
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_11071507080129703 C2C2_OUTLINES2 SELECT v.company_code, v.site_code, v.receiving_number, v.package_number, v.purc
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'SYS_OUTLINE_11071507080129703';
SELECT name, category, used FROM user_outlines where category like '%C2C2%';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='11.1.0.7';
ALTER system SET query_rewrite_enabled=TRUE;
ALTER system SET use_stored_outlines=C2C2_OUTLINES2;
run the query now
then explain plan for query
then check the outline status
SELECT name, category, used FROM user_outlines where category like '%C2C2%';
Shrink datafile:
================
column cmd format a75 word_wrapped
select 'alter database datafile '||file_name||' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'m;' cmd
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
where a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) -ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
***************ROLES*************
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 999
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool roles_grant.sql
select 'grant ' ||GRANTED_ROLE || ' to ' ||GRANTEE|| ' with ADMIN OPTION ;'
from dba_role_privs where ADMIN_OPTION='YES'
UNION
select 'grant ' ||GRANTED_ROLE || ' to ' ||GRANTEE|| ';'
from dba_role_privs where ADMIN_OPTION='NO' ;
SELECT 'EXIT;' FROM DUAL;
SPOOL OFF
SET TERMOUT ON
EXIT;
***************object & sys privs***********
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
set feedback off
set heading off
spool sys_obj_privs_grant.sql
select 'grant ' || PRIVILEGE || ' to ' || GRANTEE|| ';'from dba_sys_privs
UNION
select 'grant ' ||PRIVILEGE|| ' on ' ||owner || '.'||TABLE_NAME|| ' to ' ||GRANTEE|| ' with grant OPTION ;'
from dba_tab_privs where GRANTABLE='YES' and owner not in (select username from dba_users where username like '%SYS%')
UNION
select 'grant ' ||PRIVILEGE|| ' on ' ||owner || '.'||TABLE_NAME|| ' to ' ||GRANTEE|| ';'
from dba_tab_privs where GRANTABLE='NO' and owner not in (select username from dba_users where username like '%SYS%');
SELECT 'EXIT;' FROM DUAL;
SPOOL OFF
SET TERMOUT ON
EXIT;
WF Sync from backend
=======================
begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPS_TS_TX_DATA'); END;
Resize datafiles
=================
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 and a.tablespace_name='APPS_TS_TX_DATA'
/
Query to find object growth
==========================
SQL> col object_name for a35
SQL> set pages 0
SQL> set linesize 200
SQL> select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type,
2 sum(space_used_delta)/1024/1024 "Growth (MB)"
3 from dba_hist_snapshot sn,
4 dba_hist_seg_stat a,
5 dba_objects b,
6 dba_segments c
7 where begin_interval_time > trunc(sysdate) - &days_back
8 and sn.snap_id = a.snap_id
9 and b.object_id = a.obj#
10 and b.owner = c.owner
11 and b.object_name = c.segment_name
12 and b.object_name = c.segment_name
13 group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
14 order by 3 asc;
Enter value for days_back: 30
old 7: where begin_interval_time > trunc(sysdate) - &days_back
new 7: where begin_interval_time > trunc(sysdate) - 30
OPP
====
UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
SSO Issue
===========
select fpot.user_profile_option_name, fpov.profile_option_value,fpov.last_update_date,fu.user_name
from apps.fnd_profile_options fpo, apps.fnd_profile_options_tl fpot, apps.fnd_profile_option_values fpov, apps.fnd_user fu
where fpo.profile_option_id = fpov.profile_option_id
and fpov.level_id=10001
and fpov.last_updated_by=fu.user_id
and fpo.profile_option_name in ('APPS_FRAMEWORK_AGENT','APPS_SSO','APPS_SSO_LDAP_SYNC','APPS_SSO_LOCAL_LOGIN','APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS','APPS_SSO_USER_CREATE_UPDATE','APPS_SSO_LINK_TRUTH_SRC','APPS_SSO_AUTO_LINK_USER','FND_OVERRIDE_SSO_LANG')
and fpo.profile_option_name=fpot.profile_option_name
and fpot.language='US'
order by 1;
select fpot.user_profile_option_name, fpov.profile_option_value,fpov.last_update_date,fu1.user_name
from apps.fnd_profile_options fpo, apps.fnd_profile_options_tl fpot, apps.fnd_profile_option_values fpov, apps.fnd_user fu,apps.fnd_user fu1
where fpo.profile_option_id = fpov.profile_option_id
and fpov.level_id=10004
and fpov.level_value=fu.user_id
and fpov.last_updated_by=fu1.user_id
and fpo.profile_option_name=fpot.profile_option_name
and fpot.language='US'
and fu.user_name='502064474'
order by 1;
select user_name,end_date,user_guid from apps.fnd_user where user_name='502078503';
Out of Memory
==============
SQL> !prtconf | grep Mem
Memory size: 131072 Megabytes
id -p --need to run in oracle user to find the projid
orapgre2@tsgsd5904 $ id -p
uid=502064261(orapgre2) gid=502064270(dbapgre2) projid=1001(user.orapgre2)
login as root
==============
Please managed user's shared memory appropriately. You will have to remove any shared memory in use in order to restart the database, user can manage.
tsgsd3900 > ipcs -m | grep oraogpx2
m 1912602658 0xbe82356d --rw-rw---- oraogpx2 dbaogpx2
m 587202582 0xbe82356c --rw-rw---- oraogpx2 dbaogpx2
use ipcrm command to remove memory in use to restart.
prctl -n project.max-shm-memory -i project 3 --to check the correct allocated memory
project: 1001: user.orapgre2
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 5.00GB - deny -
system 16.0EB max deny -
prctl -n project.max-shm-memory -r -v 37G -i project 3 --increased to 7G from 5G (pls verify before execute )
add responsibility using sqlplus
================================
BEGIN
fnd_user_pkg.addresp ('DBAREAD','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','System Administrator Responsibility',SYSDATE,SYSDATE + 2);
COMMIT;
END;
/
Pinning objects
===========
select 'execute dbms_shared_pool.keep('''||owner||'.'||name||''','''||decode(type,'PACKAGE BODY','P','PACKAGE','P','FUNCTION','P','PROCEDURE','P','TRIGGER','R','TYPE','T','SEQUENCE','Q')||''');' FROM v$db_object_cache
WHERE type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE','INVALID TYPE','CURSOR')AND loads>1 AND executions>loads AND executions>100 AND kept='NO'ORDER BY owner,namespace,type,executions desc;
RAC cluster commands
==================
crsctl status resource -t |grep -i jd1|egrep -v "acfs|vip|dg|agent"
ora.GPSPGJP1.lsnr
gpspgjp1_X
gpspgjp1_conc_mgr
gpspgjp1_dollaru
gpspgjp1_ebs_app_apppgjp101
gpspgjp1_ebs_app_apppgjp102
gpspgjp1_ebs_app_apppgjp1x01
gpspgjp1_ebs_app_apppgjp1x02
ora.gpspgjp1.db
tsgsp5010#
Status Services :
============================
crsctl status resource gpspgjp1_ebs_app_apppgjp1x01
crsctl status resource gpspgjp1_ebs_app_apppgjp1x02
crsctl status resource gpspgjp1_ebs_app_apppgjp101
crsctl status resource gpspgjp1_ebs_app_apppgjp102
crsctl status resource gpspgjp1_dollaru
crsctl status resource gpspgjp1_conc_mgr
crsctl status resource gpspgjp1_X
Stop Application Services
==========================
crsctl stop resource gpspgjp1_ebs_app_apppgjp1x01
crsctl stop resource gpspgjp1_ebs_app_apppgjp1x02
crsctl stop resource gpspgjp1_ebs_app_apppgjp101
crsctl stop resource gpspgjp1_ebs_app_apppgjp102
crsctl stop resource gpspgjp1_dollaru
crsctl stop resource gpspgjp1_conc_mgr
crsctl stop resource gpspgjp1_X
check for any third party Application like Aventx
=================================================== 8:14 AM
i. login to admin node as application user (apppgjp1)
ii. fcmgr -admin -c stop ( to stop the Aventx unix services)
cd $FCHOME/bin
fcmgr -admin
>lc ( to check the status of Aventx unix services)
>stop ( to stop the Aventx unix services if running)
>e (exit from the prompt)
iii. $FCHOME/cfg/scripts/srvctl stop ( to stop fcsrv services)
iv. sh $CATALINA_HOME/bin/shutdown.sh ( to stop tomcat web manager services)
v. sh /gpspgjp1/erpapp/appl/tools/AventX/oa/extproc/AventXDaemon/stop.sh
==================================================
Database and Listener
crsctl stop resource ora.GPSPGJP1.lsnr
crsctl stop resource ora.gpspgjp1.db
Status Services :
============================
crsctl status resource gpspgjp1_ebs_app_apppgjp1x01
crsctl status resource gpspgjp1_ebs_app_apppgjp1x02
crsctl status resource gpspgjp1_ebs_app_apppgjp101
crsctl status resource gpspgjp1_ebs_app_apppgjp102
crsctl status resource gpspgjp1_dollaru
crsctl status resource gpspgjp1_conc_mgr
crsctl status resource gpspgjp1_X 8:14 AM
Database and Listener
=====================================
crsctl start resource ora.gpspgjp1.db
crsctl start resource ora.GPSPGJP1.lsnr
Start Application Services
==========================
crsctl start resource gpspgjp1_conc_mgr
crsctl start resource gpspgjp1_X
crsctl start resource gpspgjp1_dollaru
crsctl start resource gpspgjp1_ebs_app_apppgjp1x01
crsctl start resource gpspgjp1_ebs_app_apppgjp1x02
crsctl start resource gpspgjp1_ebs_app_apppgjp101
crsctl start resource gpspgjp1_ebs_app_apppgjp102
check for any third party Application like Aventx
===================================================
i. login to admin node as application user (apppgjp1)
ii. fcmgr -admin -c start ( to start the Aventx unix services)
cd $FCHOME/bin
fcmgr -admin
>lc ( to check the status of Aventx unix services)
>stop ( to stop the Aventx unix services if running)
>e (exit from the prompt)
iii. $FCHOME/cfg/scripts/srvctl start ( to stop fcsrv services)
iv. sh $CATALINA_HOME/bin/startup.sh ( to stop tomcat web manager services)
v. sh /gpspgjp1/erpapp/appl/tools/AventX/oa/extproc/AventXDaemon/start.sh
IMAP
==========
-bash-3.2$ telnet gpsgnep1.tsg.ge.com 143
Trying 3.21.56.208...
Connected to gpsgnep1.tsg.ge.com (3.21.56.208).
Escape character is '^]'.
* OK Dovecot ready.
* login mailgnep xyz00abc
* OK Logged in.
* list * "*"
* LIST (\NoInferiors \UnMarked) "/" "DISCARD"
* LIST (\NoInferiors \UnMarked) "/" "PROCESSED"
* LIST (\NoInferiors \UnMarked) "/" "Trash"
* LIST (\NoInferiors \UnMarked) "/" "INBOX"
* OK List completed.
* logout
* BYE Logging out
* OK Logout completed.
Connection closed by foreign host.
Cluster name modify
====================
crsctl relocate resource appafmx102.appvip -n tsgxd2101 -f
To Fetch Version of Package from backend
===========================================
SELECT name ||' '|| text
FROM dba_source
WHERE line = 2
AND type LIKE 'PACKAGE BODY'
AND owner = 'APPS'
AND name = 'CSI_PROCESS_TXN_GRP'
ORDER BY name, type;
Script to check the running Concurrent jobs
==============================
cmrun2.sql
========
set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 999999999
col "Parent" form a8
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
order by 1,2
/
Or
jobs.sql
=========
set pages 66
set line 132
-- spool progs1.lst
col user_name format a20 word_wrapped
column ProgName format a25 word_wrapped
column requestId format 9999999999
column StartDate format a20 word_Wrapped
column OS_PROCESS_ID format a6
column ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
select sess.sid,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
order by 5 DESC
/
-- spool off
To check the running concurrent requests or Programs under particular Concurrent Manager
=============================================================
qdjobs.sql
=========
set pagesize 132
col USER_CONCURRENT_PROGRAM_NAME format a70 wrap
col StartDate format a20 wrap
SELECT
REQUEST_ID,
PHASE_CODE,
STATUS_CODE,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
REQUESTED_BY,
fcp.user_concurrent_program_name
FROM
FND_CONCURRENT_WORKER_REQUESTS FCWR,
FND_CONCURRENT_PROGRAMS_TL FCP
WHERE
(Phase_Code = 'P' OR Phase_Code = 'R')
AND hold_flag != 'Y'
AND Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE in ( 'R','P') AND STATUS_CODE IN ('I', 'Q')))
AND (CONCURRENT_QUEUE_ID in (select CONCURRENT_QUEUE_ID FROM FND_CONCURRENT_QUEUES_VL WHERE USER_CONCURRENT_QUEUE_NAME like
'%&manager_name%' ))
--AND (QUEUE_APPLICATION_ID=0)
AND FCP.CONCURRENT_PROGRAM_ID = FCWR.CONCURRENT_PROGRAM_ID
and fcp.language = 'US'
ORDER BY fcp.user_concurrent_program_name, STATUS_CODE,actual_Start_date,Priority, Priority_Request_ID, Request_ID
/
Concurrent Program previous history
===========================
conc_hist.sql
===========
set linesize 152
set pages 1000
col username for a10
col status for a10
col phase for a10
col PNAME for a15
col mins for 99999
col start_date for a18
col end_date for a18
col request_id for 99999999999
select
b.user_name username,
request_id as REQ_ID,
a.controlling_manager cm,
decode(a.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown') phase_code ,
decode(a.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'G','Warning',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
) status_code ,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
to_char(a.REQUESTED_START_DATE,'DD-MON-YY HH24-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24-MI-SS') END_DATE,
(a.ACTUAL_COMPLETION_DATE - a.REQUESTED_START_DATE)*24*60 "mins"
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
--status_code in ('E') and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
and rownum<100order by ACTUAL_COMPLETION_DATE;
To check the request details
======================
analreq.sql
=========
set serveroutput on size 10000
set feedback off
set verify off
set heading off
set timing off
variable help_text varchar2(2000);
prompt
DECLARE
req_id number(15) := &1;
FUNCTION get_status(p_status_code varchar2) return varchar2 AS
c_status fnd_lookups.meaning%TYPE;
BEGIN
SELECT nvl(meaning, 'UNKNOWN')
INTO c_status
FROM fnd_lookups
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
AND LOOKUP_CODE = p_status_code;
return rtrim(c_status);
END get_status;
FUNCTION get_phase(p_phase_code varchar2) return varchar2 AS
c_phase fnd_lookups.meaning%TYPE;
BEGIN
SELECT nvl(meaning, 'UNKNOWN')
INTO c_phase
FROM fnd_lookups
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
AND LOOKUP_CODE = p_phase_code;
return rtrim(c_phase);
END get_phase;
PROCEDURE manager_check (req_id in number,
cd_id in number,
mgr_defined out boolean,
mgr_active out boolean,
mgr_workshift out boolean,
mgr_running out boolean,
run_alone out boolean) is
cursor mgr_cursor (rid number) is
select running_processes, max_processes,
decode(control_code,
'T','N', -- Abort
'X','N', -- Aborted
'D','N', -- Deactivate
'E','N', -- Deactivated
'Y') active
from fnd_concurrent_worker_requests
where request_id = rid
and not((queue_application_id = 0)
and (concurrent_queue_id in (1,4)));
run_alone_flag varchar2(1);
begin
mgr_defined := FALSE;
mgr_active := FALSE;
mgr_workshift := FALSE;
mgr_running := FALSE;
for mgr_rec in mgr_cursor(req_id) loop
mgr_defined := TRUE;
if (mgr_rec.active = 'Y') then
mgr_active := TRUE;
if (mgr_rec.max_processes > 0) then
mgr_workshift := TRUE;
end if;
if (mgr_rec.running_processes > 0) then
mgr_running := TRUE;
end if;
end if;
end loop;
if (cd_id is null) then
run_alone_flag := 'N';
else
select runalone_flag
into run_alone_flag
from fnd_conflicts_domain d
where d.cd_id = manager_check.cd_id;
end if;
if (run_alone_flag = 'Y') then
run_alone := TRUE;
else
run_alone := FALSE;
end if;
end manager_check;
PROCEDURE print_mgrs(p_req_id number) AS
CURSOR c_mgrs(rid number) IS
SELECT user_concurrent_queue_name name, fcwr.running_processes active,
decode(fcwr.control_code, 'A', fl.meaning,
'D', fl.meaning,
'E', fl.meaning,
'N', fl.meaning,
'R', fl.meaning,
'T', fl.meaning,
'U', fl.meaning,
'V', fl.meaning,
'X', fl.meaning,
NULL, 'Running',
'** Unknown Status **') status
FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
WHERE fcwr.request_id = rid
AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
AND fcwr.concurrent_queue_id not in (1, 4)
AND fl.lookup_code (+) = fcwr.control_code
AND fl.lookup_type (+) = 'CP_CONTROL_CODE';
BEGIN
for mgr_rec in c_mgrs(p_req_id) loop
DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status
|| ' (' || mgr_rec.active || ' active processes)');
end loop;
END print_mgrs;
PROCEDURE analyze_request(p_req_id number) AS
reqinfo fnd_concurrent_requests%ROWTYPE;
proginfo fnd_concurrent_programs_vl%ROWTYPE;
c_status fnd_lookups.meaning%TYPE;
m_buf fnd_lookups.meaning%TYPE;
conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name fnd_application_vl.application_name%TYPE;
tmp_id number(15);
tmp_status fnd_concurrent_requests.status_code%TYPE;
tmp_date date;
conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id number(15);
resp_name varchar2(100);
rclass_name fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name fnd_executables.execution_file_name%TYPE;
c_user fnd_user.user_name%TYPE;
last_user fnd_user.user_name%TYPE;
fcd_phase varchar2(48);
fcd_status varchar2(48);
traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;
icount number;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;
live_child boolean;
mgr_defined boolean;
mgr_active boolean;
mgr_workshift boolean;
mgr_running boolean;
run_alone boolean;
reqlimit boolean := false;
mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
filename varchar2(255);
qcf fnd_concurrent_programs.queue_control_flag%TYPE;
apps_version varchar2(3);
sep varchar2(200) := '------------------------------------------------------';
REQ_NOTFOUND exception;
CURSOR c_wait IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_req_id;
CURSOR c_inc IS
SELECT to_run_application_id, to_run_concurrent_program_id
FROM fnd_concurrent_program_serial
WHERE running_application_id = conc_app_id
AND running_concurrent_program_id = conc_id;
CURSOR c_ireqs IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code = 'R'
AND program_application_id = traid
AND concurrent_program_id = trcpid
AND cd_id = conc_cd_id;
CURSOR c_userreqs(uid number, s date) IS
SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code IN ('R', 'P')
AND requested_by = uid
AND requested_start_date < s
AND hold_flag = 'N';
BEGIN
BEGIN
SELECT *
INTO reqinfo
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise REQ_NOTFOUND;
END;
DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
DBMS_OUTPUT.PUT_LINE(sep);
-- Program information
DBMS_OUTPUT.PUT_LINE('Program information:');
SELECT fvl.*
INTO proginfo
FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.concurrent_program_id = fvl.concurrent_program_id
AND fcr.program_application_id = fvl.application_id;
DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name
|| ' (' || proginfo.concurrent_program_name || ')');
SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
INTO conc_app_name
FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.program_application_id = fvl.application_id;
DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);
SELECT nvl(meaning, 'UNKNOWN')
INTO m_buf
FROM fnd_lookups
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lookup_code = proginfo.execution_method_code;
SELECT nvl(execution_file_name, 'NONE')
INTO exe_file_name
FROM fnd_executables
WHERE application_id = proginfo.executable_application_id
AND executable_id = proginfo.executable_id;
DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || ' (' || proginfo.execution_method_code || ')');
DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);
if proginfo.concurrent_class_id is not null then
select request_class_name
into rclass_name
from fnd_concurrent_request_class
where application_id = proginfo.class_application_id
and request_class_id = proginfo.concurrent_class_id;
DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
end if;
if proginfo.execution_options is not null then
DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
end if;
if proginfo.enable_trace = 'Y' then
DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
end if;
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('
');
DBMS_OUTPUT.PUT_LINE(sep);
-- Submission information
DBMS_OUTPUT.PUT_LINE('Submission information:');
begin
SELECT user_name into c_user from fnd_user
where user_id = reqinfo.requested_by;
exception
when no_data_found then
c_user := '-- UNKNOWN USER --';
end;
begin
SELECT user_name into last_user from fnd_user
WHERE user_id = reqinfo.last_updated_by;
exception
when no_data_found then
last_user := '-- UNKNOWN USER --';
end;
DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
SELECT responsibility_name
INTO resp_name
FROM fnd_responsibility_vl
WHERE responsibility_id = reqinfo.responsibility_id
AND application_id = reqinfo.responsibility_application_id;
DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));
DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);
c_status := get_status(reqinfo.status_code);
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('
');
DBMS_OUTPUT.PUT_LINE(sep);
-- Analysis
DBMS_OUTPUT.PUT_LINE('Analysis:');
-- Completed Requests
-------------------------------------------------------------------------------------------------------------
IF reqinfo.phase_code = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
DBMS_OUTPUT.PUT_LINE('It completed on: '||
nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO COMPLETION DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id =
(select CONCURRENT_QUEUE_ID
from fnd_concurrent_processes
where CONCURRENT_PROCESS_ID = reqinfo.controlling_manager);
DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
END;
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));
-- Running Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'R' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id = reqinfo.controlling_manager;
DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
IF reqinfo.status_code = 'Z' THEN
-- Waiting request, See what it is waiting on
FOR child in c_wait LOOP
DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
child.request_id||' phase = '||get_phase(child.phase_code)||
' status = '||get_status(child.status_code));
END LOOP;
ELSIF reqinfo.status_code = 'W' THEN
-- Paused, check and see if it is a request set, and if its children are running
SELECT nvl(concurrent_program_name, 'UNKNOWN')
INTO conc_prog_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id;
DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
IF conc_prog_name = 'FNDRSSTG' THEN
DBMS_OUTPUT.PUT_LINE('This program is a Request Set Stage.');
END IF;
IF instr(conc_prog_name, 'RSSUB') > 0 THEN
DBMS_OUTPUT.PUT_LINE('This program is a Request Set parent program.');
END IF;
live_child := FALSE;
FOR child in c_wait LOOP
DBMS_OUTPUT.PUT_LINE('It has a child request: '||
child.request_id||' (phase = '||get_phase(child.phase_code)||
' - status = '||get_status(child.status_code)||')');
IF child.phase_code != 'C' THEN
live_child := TRUE;
END IF;
END LOOP;
IF live_child = FALSE THEN
DBMS_OUTPUT.PUT_LINE('This request has no child requests
that are still running. You may need to wake this request up manually.');
END IF;
END IF;
-- Pending Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE(' (phase_code = P) (status_code = '||reqinfo.status_code||')');
-- could be a queue control request
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;
IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
GOTO diagnose;
END IF;
-- why is it pending?
-- could be scheduled
IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
GOTO diagnose;
END IF;
-- could be on hold
IF reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on
'||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
GOTO diagnose;
END IF;
-- could be disabled
IF proginfo.enabled_flag = 'N' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name
||' needs to be enabled for this request to run.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
GOTO diagnose;
END IF;
-- check queue_method_code
-- unconstrained requests
IF reqinfo.queue_method_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready
to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though
it is unconstrained. It will not be run by any manager.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
END IF;
-- constrained requests
ELSIF reqinfo.queue_method_code = 'B' THEN
DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released
this request, and it is in a "Pending/Normal" status.');
DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status.
The Conflict Resolution manager will need to release it before it can be run.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
END IF;
-- incompatible programs
SELECT program_application_id, concurrent_program_id, cd_id
INTO conc_app_id, conc_id, conc_cd_id
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
icount := 0;
FOR progs in c_inc LOOP
traid := progs.to_run_application_id;
trcpid := progs.to_run_concurrent_program_id;
OPEN c_ireqs;
LOOP
FETCH c_ireqs INTO ireqid, pcode, scode;
EXIT WHEN c_ireqs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is
waiting, or will have to wait, on an incompatible request: '|| ireqid );
DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
icount := icount + 1;
END LOOP;
CLOSE c_ireqs;
END LOOP;
IF icount = 0 THEN
DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
END IF;
-- could be a runalone itself
IF proginfo.run_alone_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
END IF;
-- single threaded
IF reqinfo.single_thread_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Sequential Requests is set.');
reqlimit := true;
END IF;
-- request limit
IF reqinfo.request_limit = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Active Request Limit is set.');
reqlimit := true;
END IF;
IF reqlimit = true THEN
DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id ||
' Requested start date: ' || progs.start_date);
DBMS_OUTPUT.PUT_LINE(' Phase: ' || get_phase(progs.phase_code)
|| ' Status: ' || get_status(progs.status_code));
END LOOP;
END IF;
-- error, invalid queue_method_code
ELSE
DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
GOTO diagnose;
END IF;
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');
-- check the managers
manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);
-- could be a runalone ahead of it
IF run_alone = TRUE THEN
DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
select user_cd_name into conflict_domain from fnd_conflicts_domain
where cd_id = reqinfo.cd_id;
DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);
-- see what is running
begin
select request_id, status_code, actual_start_date
into tmp_id, tmp_status, tmp_date
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where fcp.run_alone_flag = 'Y'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.phase_code = 'R'
and fcr.cd_id = reqinfo.cd_id;
DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
', which is running with status '||get_status(tmp_status));
DBMS_OUTPUT.PUT_LINE('It has been running since: '||
nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
', but there is no runalone request running');
end;
ELSIF mgr_defined = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each
manager to make sure they are defined correctly.');
ELSIF mgr_active = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There are one or more managers defined
that can run this request, but none of them are currently active');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
print_mgrs(p_req_id);
ELSIF mgr_workshift = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running
in an active workshift that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- display details about the workshifts
ELSIF mgr_running = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is one or more managers available
to run this request, but none of them are running');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
print_mgrs(p_req_id);
ELSE
-- print out the managers available to run it
DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
print_mgrs(p_req_id);
END IF;
-- invalid phase code
ELSE
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');
END IF;
<<diagnose>>
BEGIN
FND_CONC.DIAGNOSE(p_req_id, fcd_phase, fcd_status, :help_text);
EXCEPTION
WHEN OTHERS THEN
:help_text := 'The FND_CONC package has not been installed on this system.';
END;
DBMS_OUTPUT.PUT_LINE(sep);
EXCEPTION
WHEN REQ_NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);
END analyze_request;
BEGIN
analyze_request(req_id);
END;
/
To Check the session details
======================
sess.sql
==========
def aps_prog = 'sessinfo.sql'
def aps_title = 'Session information'
col "Session Info" form A100
set verify off
accept sid prompt 'Please enter the value for Sid if known : '
accept terminal prompt 'Please enter the value for terminal if known : '
accept machine prompt 'Please enter the machine name if known : '
accept process prompt 'Please enter the value for Client Process if known : '
accept spid prompt 'Please enter the value for Server Process if known : '
accept osuser prompt 'Please enter the value for OS User if known : '
accept username prompt 'Please enter the value for DB User if known : '
select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| ' DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| ' Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
' OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| ' (Since) '||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
' Client Program Name : '||s.program||chr(10) ||
' Action / Module : '||s.action||' / '||s.module||chr(10) || chr(10) ||
' Wait Status : '||s.event || ' ' || s.seconds_in_wait || ' ' || s.state "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and s.sid = nvl('&SID',s.sid)
and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
and nvl(s.process,-1) = nvl('&Process',nvl(s.process,-1))
and p.spid = nvl('&spid',p.spid)
and s.username = nvl('&username',s.username)
and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/
undefine sid
To check the current running all sessions with wait events
=======================================
waits.sql
==========
set lines 132
set pagesize 1000
column sid format 99999
column event format a25 word_wrapped
column module format a15 word_wrapped
column username format a9 word_wrapped
column seconds_in_wait format 999999 word_wrapped
select b.sid, b.event, substr(a.action,1,1)||'-'||a.module module , a.username ,
b.p1, b.p2,b.p3 ,b.seconds_in_wait from v$session_wait b, v$session a
where b.event not in ('SQL*Net message from client','rdbms ipc message',
'pmon timer','smon timer', 'pipe get')
and a.sid = b.sid
order by 8
/
whywait.sql
==========
col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status
from v$session_wait a, v$session s
where a.sid=s.sid
and a.sid=&SID
/
To fetch the running sql from sid
========================
getsql.sql
=========
break on hash_value
set pagesize 1000
set long 200000
select hash_value, sql_text
from v$sqltext
where hash_value in
(select sql_hash_value from
v$session where sid = &sid)
order by piece
/
To check the session row movement
========================
sessrow.sql
==========
column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'
/
To fetch the explain plan from the running session
==================================
expl.sql
=======
--variable sqlid varchar2(13);
column sql_id new_value sqlid
select sql_id from v$session where sid=&sid
/
select plan_table_output from table(DBMS_XPLAN.display_cursor('&sqlid'))
/
You can also run explain plan for in sql propmt and provide sql and check explain plan using utlxpls.sql
=======================================================================
sql> explain plan for
select name from v$database;
explained
sql> @utlxpls.sql --to view the explain plan for particular sql stmt
utlxpls.sql
========
set markup html preformat on
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
To check the TEMP usage
==================
sortsize.sql
========
col used format 999,999,999,999
col free format 999,999,999,999
col maxused format 999,999,999,999
col totalsize format 999,999,999,999
SELECT tablespace_name, (TOTAL_BLOCKS * 8192) "totalsize",
(USED_BLOCKS * 8192 ) "used" , (FREE_BLOCKS * 8192) "free" ,
(MAX_USED_BLOCKS * 8192 ) "maxused"
FROM v$sort_segment;
psort.sql --to check the temp usage sessions
=========
col module format a15
col username format a15
col tablespace format a15
col size format 999,999,999,999
set lines 120
SELECT s.sid,s.username,s.module,u.contents,segtype,TABLESPACE, sum((u.blocks * 8192)) "size"
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
and u.extents > 10
group by s.sid,s.username,s.module,u.contents,segtype,tablespace
order by 7,2,3;
To check the inactive form sessions
==========================
inactive_frm.sql
==============
set pages 200 lines 200
SELECT a.sid,a.serial#, substr(b.object_name,1,40) object_name,
DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) "Lock Time"
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;
inactive_frm_kill.sql
==============
set pages 999
set head off
select 'alter system kill session '''||a.sid||','||a.serial#||''''||' immediate;'
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;
To check the stale objects
====================
stale.sql
=========
set pages 2000
set linesize 200
set heading off
set feedback off
select 'exec fnd_stats.gather_table_stats(ownname =>'''||owner||''',tabname =>'''||table_name||''',percent => 30, cascade => TRUE);'
from dba_tab_statistics where stale_stats='YES'
and owner not in ('SYS','SYSTEM','TRCANLZR','OLAPSYS','XDB')
and table_name not like 'BIN%' order by 1;
To check the Patch details including patch stage
=================================
patch_details.sql
============
set line 132
col patch_top for a30
col name for a10
select a.PATCH_TOP,
c.DRIVER_FILE_NAME,
b.name,
a.CREATION_DATE
from
AD_PATCH_RUNS a,
AD_APPL_TOPS b,
AD_PATCH_DRIVERS C
where
a.APPL_TOP_ID=b.APPL_TOP_ID
and c.PATCH_DRIVER_ID=a.PATCH_DRIVER_ID
and c.DRIVER_FILE_NAME like '%&Patch_Name%'
order by a.CREATION_DATE, c.DRIVER_FILE_NAME;
To Kill toad/sql developer sessions
=========================
select 'alter system disconnect session '''||sid||','||serial#||'''immediate;' from v$session where program like '%T.O.A.D%' or program in('plsqldev.exe','SQL Developer','TOAD.exe','PLSQLDev.exe');
To check ASM disk utilization
=======================
select name, state, sum(total_mb/1024), sum(free_mb/1024) from v$asm_diskgroup where name like upper('%&Instance_name%') group by name,state;
Users details from machine
=====================
user_AT.sql
=========
col machine for a25
select count(distinct d.user_name) ,b.machine from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1) group by b.machine;
Patch details including language
========================
col PATCH_NAME for a15
select a.PATCH_NAME, a.APPLIED_PATCH_ID, b.LANGUAGE from ad_applied_patches a, ad_patch_driver_langs b where a.APPLIED_PATCH_ID=b.PATCH_DRIVER_ID and a.PATCH_NAME like '%&PatchNumber%';
To check the ICM status
=================
select apps.fnd_oam_em.get_icm_status from dual;
To check the FND Profiles
======================
set lines 200
col PROFILE_OPTION_NAME for a25
col PROFILE_OPTION_VALUE for a65
select a.profile_option_name, b.profile_option_value, a.profile_option_id, a.application_id from fnd_profile_options a, fnd_profile_option_values b where a.application_id = b.application_id and a.profile_option_id = b.profile_option_id
and b.profile_option_value like '%&value%';
To fetch the trace file name from request
==============================
req_tracefile.sql
============
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 90
set pages 22
set head off
select 'Request id: '||request_id , 'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace, 'Trace Name: '||dest.value||
'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module from
fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname
where req.request_id = &request and req.oracle_process_id=proc.spid(+) and
proc.addr = ses.paddr(+) and dest.name='user_dump_dest' and dbnm.name='db_name' and
req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;
To check the RMAN backup details
===========================
rman_bkp_dtl.sql
===============
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-3
order by session_key;
dbms_rman_job.sql
===============
select job_name,job_type,number_of_arguments,job_action,repeat_interval,enabled from dba_scheduler_jobs where JOB_NAME like '%RMAN%';
dbms_rman_pgm.sql
================
select OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,NUMBER_OF_ARGUMENTS,ENABLED from dba_scheduler_programs where PROGRAM_NAME like '%RMAN%';
To check the locks
===============
lock_waits.sql --to check dead locks with details
===============
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
cursor c2 (v_id1 v$lock.ID1%TYPE,v_id2 v$lock.ID2%TYPE) is select sid , type from v$lock
where id1 = v_id1
and id2 = v_id2
and request = 0
and lmode != 4;
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
/*
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
*/
for c2_rec in c2(c1_rec.id1,c1_rec.id2) loop
wsid:=c2_rec.sid;
wtype:=c2_rec.type;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end loop;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
lckdtl.sql --to chk blocking locks
=========
set feedback on
set lines 132
column sess format a8 word_wrapped
column id1 format 99999999
column id2 format 99999999
column req format 999
column type format a4
column event format a15 word_wrapped
column seconds_in_wait format 999999
column status format a10
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(vl.request,0,0,5),' ')||vl.sid sess
, vl.id1
, vl.id2
, vl.lmode
, vl.request req, vl.type,
sw.event event, sw.seconds_in_wait, s.status
FROM V$LOCK vl, v$session_wait sw, v$session s
WHERE vl.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and vl.sid = sw.sid
and vl.sid = s.sid
ORDER BY id1,request
/
locks.sql-To check all the locks in the database
========
set pagesize 1000
set line 132
break on "SID"
col Object format a35 wrap
col Locker format a10
col Type format a10
col Program format a30 wrap
col Lockmode format a15 wrap
col Wait format 9999999
col Status format a8
SELECT
SESSION_ID "SID",
OWNER||'.'||OBJECT_NAME "Object",
ORACLE_USERNAME "Locker",
-- nvl(lockwait,mysessf(lockwait)) "Wait",
lockwait "Wait",
DECODE(LOCKED_MODE,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode",
OBJECT_TYPE "Type",
c.ROW_WAIT_ROW#,
c.Status "Status"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/
To check the particular user sessions details
==============================
user_sess.sql
==========
def aps_prog = 'sessinfo.sql'
def aps_title = 'Session information'
col "Session Info" form A80
col dbuser form A8
col mach form A8
col program format a20 trunc
col description format a20 trunc
col module format a10 wrap
col action format a10 wrap
set verify off
set lines 132
select s.sid , substr(s.username,1,8) dbuser, substr(s.machine,1,10) mach, s.process,p.spid,f.login_type
,s.action,s.module,to_char(start_time,'DD-MON-YY HH24:MI') stime,
fu.description
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='&user_name'
order by f.start_time
/
To kill particular user sessions
=====================
set pages 0
set lines 132
!rm /tmp/user_sessions.sql
prompt Here is a list of SID's that correspond to the user entered:
set feedback off
spool /tmp/user_sessions.sql
prompt set echo on
set head off
set pages 100
set linesize 255
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';'
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='&user_name'
order by f.start_time;
prompt set echo off
spool off
!chmod 777 /tmp/user_sessions.sql
set feedback on
prompt Verify file @/tmp/user_sessions.sql and run it to kill them .
set head on
set echo off
To kill particular sessions
=================
undefine sid
set serveroutput on
--set heading off
DECLARE
CURSOR get_ps IS
SELECT distinct sid,serial# from v$session
where sid in (&sid);
ps get_ps%ROWTYPE;
sql_cmd VARCHAR2(1000);
sql_cur INTEGER;
sql_row_cnt INTEGER;
BEGIN
sql_cur := dbms_sql.open_cursor;
OPEN get_ps;
LOOP
FETCH get_ps INTO ps;
EXIT WHEN get_ps%NOTFOUND;
dbms_output.put_line ('Killing session '||ps.sid||','||ps.serial#);
sql_cmd := 'ALTER SYSTEM KILL SESSION ' ||''''||ps.sid||','|| ps.serial#||'''';
-- dbms_output.put_line ( sql_cmd );
dbms_sql.parse ( sql_cur , sql_cmd , dbms_sql.v7 );
sql_row_cnt := dbms_sql.execute ( sql_cur );
dbms_output.put_line ( sql_cmd );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_sql.close_cursor ( sql_cur );
END;
/
To check the long ops
==================
set verify off
col opname format a40 trunc
col pctdone format 999
col mintogo format 9,999.90
accept trgtsid number default 0 prompt 'Limit to which SID : '
select s.sid,o.opname,s.sql_hash_value,o.sofar,o.totalwork,o.elapsed_seconds,
round(o.time_remaining/60,2) mintogo,
round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)
/
or
select sid, opname, target, sofar, totalwork, units,
time_remaining, elapsed_seconds, message, username,
to_date(last_update_time,'dd-MON-yy hh:mi:ss')
from v$session_longops
where sid=&sid
/
Library Cache pin locks
================
libcachepin.sql
============
select /*+ ordered */ w1.sid waiting_session,h1.sid holding_session,w.kgllktype lock_or_pin,w.kgllkhdl
address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;
prompt To find out the object being referred, Please run the following select script;
prompt -----------------------------------------------------------------------------;
prompt select distinct to_name from v$object_dependency where to_address = '<address>';
prompt -----------------------------------------------------------------------------;
To check the DB Links
----------------------
set lines 132
set pages 100
col owner format a10
col db_link format a15
col username format a10
col host format a50
select * from dba_db_links
/
Friday, January 25, 2013Query to fetch the Latest CPU patch details from Database
column BUG format a8;
column PATCH format a60;
set linesize 100;
set pagesize 200;
select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in ('13979374','13979377','13979372','13979375','13621942','13621941','13621940',
'13621939','13322561','12794416','13322559','13322557','12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913','11660357''11660356','11660355','11660354')
order by patch;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Issue : ORA-06502: PL/SQL: numeric or value error: character string buffer too small
while creating the AWR report
FIX :
create table WRH$_SQLTEXT_BKP as select * from WRH$_SQLTEXT;
update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
Script to create new user with existing DB user Privs
userdetails_new.sql
===============
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept new_sch_password prompt "Enter new user password: "
set serveroutput on
spool CREATE_GRANT_SCH_spool.sql
-- Create user...
select 'create user &&newname identified by &&new_sch_password'||''||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant on base user objects...
select 'grant select,insert,update,delete,index on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('TABLE') and owner = upper('&&oldname');
select 'grant select,insert,update,delete on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('VIEW') and owner = upper('&&oldname');
select 'grant select on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE='SEQUENCE' and owner = upper('&&oldname');
select 'grant execute on ' || owner ||'."' || OBJECT_NAME || '" to '||upper('&&newname')||' ;' from dba_objects where OBJECT_TYPE in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY') and owner = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'" to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'."'||table_name||'"('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/
spool off
set pages 1000 feed on veri on lines 500
PROMPT
PROMPT
PROMPT Please Execute : CREATE_GRANT_SCH_spool.sql
PROMPT
PROMPT
gen_synonym_sch_user.sh
===================
echo "Enter Base User Name"
read base_user
echo "Enter new SCH User Name"
read sch_user
${ORACLE_HOME}/bin/sqlplus -s /nolog << SQLEND
conn /as sysdba
set feedback off
set pages 0
set lines 150
set heading off
spool CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
select 'create SYNONYM ${sch_user}.' || OBJECT_NAME || ' for ' || OWNER||'.'||OBJECT_NAME ||';' from dba_objects where OBJECT_TYPE in ('TABLE','SEQUENCE','PROCEDURE','FUNCTION','VIEW') and owner = upper('${base_user}');
select 'create SYNONYM ${sch_user}.' || SYNONYM_NAME || ' for ' || TABLE_OWNER ||'.'||TABLE_NAME||';' from dba_SYNONYMs where OWNER=upper('${base_user}');
spool off
SQLEND
chmod 777 CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql
echo " "
echo " "
echo "Please Execute : CREATE_SYNONYMS_${ORACLE_SID}_${sch_user}.sql "
echo " "
echo " "
For OTM Users
=============
create user otmgreportdev_sch identified by otmgreportdev#12sch;
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
or
create user otmgreportdev_sch identified by otmgreportdev#12sch
grant administer database trigger to otmgreportdev_sch;
grant create session to otmgreportdev_sch;
grant create trigger to otmgreportdev_sch;
connect glogowner/glogowner@otmtest
PROMPT
@@../script8/chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
grant execute on vpd to otmgreportdev_sch;
grant execute on GLOG_UTIL to otmgreportdev_sch;
Connect reportowner/reportowner@otmtest
PROMPT
@@chk_queue_status
DECLARE
CURSOR c_cur IS
SELECT uo.OBJECT_NAME, DECODE(uo.OBJECT_TYPE,'TABLE',0,'VIEW',1,'SEQUENCE',2,3) "OBJECT_TYPE"
FROM user_objects uo
WHERE uo.OBJECT_TYPE IN ('TABLE','PACKAGE','SEQUENCE','FUNCTION','PROCEDURE','VIEW','TYPE')
and object_name not like 'EUL4%'
and object_name not like '%$%';
BEGIN
FOR crec IN c_cur LOOP
BEGIN
-- for tables and views
IF crec.OBJECT_TYPE<2 THEN
execute IMMEDIATE 'grant select on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
IF crec.OBJECT_TYPE=0 THEN
execute IMMEDIATE 'grant references on '||crec.OBJECT_NAME|| ' to otmgreportdev_sch';
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(crec.object_name||': '||SUBSTR(SQLERRM,1,255));
END;
END LOOP;
END;
/
connect otmgreportdev_sch/author_otm@otmtest
CREATE or replace TRIGGER set_vpd_trig AFTER LOGON ON DATABASE
BEGIN
vpd.set_user('DBA.ADMIN');
END;
/
Advanced Compression in 11G
Script to find the Compression eligible objects
==================================================
set linesize 150
set pagesize 100
col owner format a15
col segment_name format a32
col segment_type for a15
col tablespace_name format a20
set lines 100
select owner,
segment_name,
segment_type,
tablespace_name,
Size_in_GB
from (select owner,
segment_name,
segment_type,
tablespace_name,
round((bytes/1024/1024/1024),2) Size_in_GB
from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_type in ('TABLE')
and segment_name not in (select table_name from dba_tables where (compression='ENABLED' or table_name like '%$%'))
order by 5 desc)
where rownum <= 40
/
Use below command to Move the obejctes
=======================================
alter table APPLSYS.WF_NOTIFICATION_OUT move compress for OLTP;
Check the UNUSABLE indexes and rebuild
=======================================
select 'alter index ' || TABLE_OWNER || '.' || INDEX_NAME || ' rebuild parallel 4 ;' from dba_indexes where status='UNUSABLE';
run gather stats on compressed objects
Monitor the respective tablespaces and temp tablespace ,and check pre and post invalids
li.sh --to check the invalids
======
echo "Purpose: To spool out the invalid objects before and after applying the patch"
if [ $# -ne 2 ]; then
echo "Usage: $0 <appspass> <pre|post>"
exit 1;
fi
genInvList(){
USER=apps
PASS=$1
TYPE=$2
DATEFMT=`date +%Y%m%d-%H%M`
if [ "x$ORACLE_SID" = "x" ]; then
if [ "x$TWO_TASK" = "x" ]; then
echo "set up the env first, duh!"
exit
else
DB=`echo $TWO_TASK`
fi
else
DB=`echo $ORACLE_SID`
fi
sqlplus -s /nolog<<_END_
conn ${USER}/${PASS}@${DB}
set pages 10000
col owner for a14
col object_type for a20
col status for a10
col OBJECT_NAME for a30
spool ${DB}-${TYPE}-invalid-${DATEFMT}.lst
select to_char(sysdate,'dd-mon-yy hh24:mi:ss') from dual;
select instance_name,status from v\$instance;
select owner, object_type, object_name
from dba_objects
where status = 'INVALID'
order by owner, object_type, object_name;
select owner, count(status)
from dba_objects
where status = 'INVALID'
group by owner;
select 'Total Invalids: '||count(*) from dba_objects where status != 'VALID';
spool off
exit
_END_
}
genInvList $1 $2
exp : li appspwd nameof thefile
Daily Needs for a DBA
=================
to fetch the apps pwd from 11.5.10.2
============================
grep -i pass $IAS_CONFIG_HOME/A*/modplsql/cfg/*.app
To kill the hung concurrent requests
==========================
update fnd_concurrent_requests set status_code='X' , phase_code='C' where request_id='150120296';
To fetch the code from backend
===========================
set pagesize 0
set linesize 200
Set head off
Spool GEWE_IR_SR_APPROVE_PKG.24Jun12.sql
select text from dba_source where NAME like 'GEWE_IR_SR_APPROVE_PKG%'
and type like '%PACKAGE%';
spool off
To generate the RSA and DSA public keys
=============================
inputs will be NONE ..all ENTER
ssh-keygen -t rsa --to generate RSA public key
/usr/bin/ssh-keygen -t dsa --to generate DSA public key
Purge the log files
===============
find . -name "*.req" -mtime +15 -exec rm {} \; --to purge 15 days old
Cluster command to check the current clusters from root in solaris
===============================================
/opt/VRTS/bin/hares -state |grep instance name |grep `hostname`|egrep -v 'mount|public|dg|share|nfs|mnt'
/opt/VRTSvcs/bin/hagrp -display instance name| grep -i frozen --to chk the database cluster status
/opt/VRTSvcs/bin/hagrp -freeze erpshst1 -sys `uname -n` --to keep in freeze mode
/opt/VRTS/bin/hares -offline orpogpp1_app_X -sys `hostname` --to bring down
/opt/VRTS/bin/hares -clear orpogpp1_app_X -sys `hostname` --to clear if its failed
/opt/VRTS/bin/hares -online orpogpp1_app_X -sys `hostname` --to bring up
To check the printer from backend
==========================
lpstat -a DSALBCGEIDTAG1
set bachspace to remove in solaris
===============================
stty erase ^h
Compiling forms,libraries,reports in Oracle Apps
=====================================
You all are may be well aware that we use f60gen for compiling forms and libraries in Oracle Applications Version 11i,But this is deprecated in R12 and we need to use frmcmp(Form Compiler) To compile forms,menus,PL/SQL libraries.
Compiling library files
11.5.9 and 11.5.10
$ f60gen module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special
R12
$ frmcmp_batch module=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.pll userid=apps/passwd
output_file=/test/applmgr/11510/au/11.5.0/resource/OEXOELIN.plx module_type=library
batch=no compile_all=special
Compiling forms
11.5.9 and 11.5.10
[applmgr@oracle vis11appl]$export FORMS60_PATH=$FORMS60_PATH:$AU_TOP/fomrs/US
[applmgr@oracle vis11appl]$ echo $FORMS60_PATH
/apps/appl/vis11appl/au/11.5.0/resource:/apps/appl/vis11appl/au/11.5.0/resource/stub:
/apps/appl/vis11appl/au/11.5.0/forms/US
[applmgr@oracle vis11appl]$
f60gen module=$AU_TOP/forms/US/APXSUMBA.fmb userid=apps/appspasswd
output_file=$PROD_TOP/forms/US/APXSUMBA.fmx module_type=form
batch=no compile_all=special
R12
$ frmcmp_batch module=<path to the fmb file> userid=apps/<apps pwd>
output_file=<path to the fmx file> compile_all=special batch=yes
Compiling reports
Syntax
adrepgen userid=APPS/APPS
source=/<full path of schema top>/reports/<report name>.rdf
dest=/<full path of schema top>/reports/<report name>.rdf stype=rdffile
dtype=rdffile logfile=<report name>.log overwrite=yes batch=yes dunit=character
Example
adrepgen userid=apps/appspasswd source=/test/applmgr/11510/ap/11.5.0/reports/US/APXPBBLD.rdf dest=/test/applmgr/11510/admin/TEST/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/test/applmgr/11510/admin/TEST/out/adrep001.txt overwrite=yes batch=yes compile_all=yes
****what is the diff between batch=yes and no *******
If batch=yes -> it won't display the output,If batch=no -> it will display output
To Enable trace using running sql sessions
==============================
connect / as sysdba
oradebug setospid 84
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
<wait for some considerable time>
oradebug event 10046 trace name context off
To Check the version of the files
=======================
strings -a ./hxc/11.5.0/patch/115/sql/hxcafnawf.pkb |grep '$Header'
adident Header ./hxc/11.5.0/patch/115/sql/hxcafnawf.pkb
Killing All processes at once:
===============================
Yesterday we come across a situation where we have to kill all look-a-like processes at once, this is what we followed to get rid of it.
kill -9 `ps -ef | grep -i applmgr | grep -i 889 | grep -v grep | awk '{print $2}'`
ps -efw | grep -i applmgr | grep | grep -v grep | awk '{print "kill -9 "$2}'
To avoid the relink set the below memory parameter
=====================================
Relink Issue:
$LDR_CNTRL="MAXDATA=0x40000000"
$export LDR_CNTRL
Move files for 30 days old
=====================
mv `find . -mtime +30` /opt/oracle/apps/common/OICP/inbound/XXATFAQS/processed/archive/ --move files older then 30 days
To verify the Stats
==============
set serveroutput on
exec fnd_stats.verify_stats('APPLSYS','FND_CONCURRENT_REQUESTS');--stats verification
SQL> exec dbms_stats.gather_table_stats ( -
ownname => 'INV', -
tabname => 'MTL_TRANSACTION_ACCOUNTS' -
)> > >
/
R12 Compile JSP:
==================
$FND_TOP/patch/115/bin
ojspCompile.pl
ojspCompile.pl --compile --fast --quiet
and clear persistence for login issues
Clear directory under persistence from below directory and restart all three OC4J
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*
SQL Profile from backend
=====================
Create task ..it needs sql id and Name of task
=================================
DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := dbms_sqltune.create_tuning_task (
sql_id => '7asddw0r2wykx'
,plan_hash_value => NULL
,SCOPE => 'COMPREHENSIVE'
,time_limit => 3000
,task_name => 'C2CTST_Issue2'
,description => NULL);
END;
/
Execute task
==========
exec dbms_sqltune.execute_tuning_task (task_name => 'C2CTST_Issue2');
View the report and analyze
====================
set long 100000
set longchunksize 10000
set linesize 10000
set pages 10000
SELECT dbms_sqltune.report_tuning_task ('C2CTST_Issue1') FROM DUAL;
Output is not feasible then drop using below
================================
exec dbms_sqltune.drop_tuning_task(task_name => 'OPRD_CMchange');
Query to check latest roll ups
===============================
select bug_number Bug, decode(bug_number,3262159, 'Patch 11i.FND.H',
3262919, 'Patch 11i.FWK.H',3126422, '11.5.9 CU1',
3171663, '11.5.9 CU2',
3140000, '11.5.10 Maintenance Pack',
3240000, '11.5.10 Consolidated Update 1 (CU1)',
3640000, '11.5.10.1 Maintenance Pack',
4017300, '11.5.10 Consolidated Update 1 (CU1) for ATG Product Family',
3460000, '11.5.10 Consolidated Update 2 (CU2)',
3480000, '11.5.10.2 Maintenance Pack',
4125550, '11.5.10 Consolidated Update (CU2) for ATG Product Family',
3438354, 'Patch 11i.ATG_PF.H',
4334965, '11i.ATG_PF.H Rollup 3',
4676589, '11i.ATG_PF.H.RUP4',
5473858, '11i.ATG_PF.H.RUP5',
5903765, '11i.ATG_PF.H.RUP6',
6241631, '11i.ATG_PF.H.RUP7') Patch,
b.LAST_UPDATE_DATE Last_update
from AD_BUGS b
where b.BUG_NUMBER in ('3262919','3262159','3126422','3171633','3140000','3240000','3640000','4017300','3460000','3480000','4125550','3438354','4334965','4676589','5473858','5903765','6241631')
order by patch;
User's Responsibilities:
============================
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = 'ORAKOUSHIKA'
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;
FIND REDO LOGS INFO:
===================
select a.group#, a.bytes/(1024*1024) mb, a.members, b.member
from v$log a, v$logfile b
where a.group#=b.group#
order by a.group#;
Monitoring Cursors:
===================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Querry to check Table-Defragmentation:
=====================================
select owner,table_name,round((blocks*8),2)/(1024) "TABLE SIZE",round((num_rows*avg_row_len/1024),2)/(1024) "ACTUAL DATA" ,
( round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) )/(1024) "Wasted Space in Table"
from dba_tables
WHERE owner not in ( 'SYS' , 'SYSTEM' ) and ( round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) )/(1024) is not null
order by 5 desc
DROP Issue
==============
SQL> DROP TABLE GEPSMFG.GEPS_LOB_TEMP CASCADE CONSTRAINTS;
SP2-0544: Command "drop" disabled in Product User Profile
SQL> select char_value from product_user_profile where ATTRIBUTE='DROP' and PRODUCT='SQL*Plus';
CHAR_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DISABLED
SQL> update product_user_profile set char_value='ENABLED' where ATTRIBUTE='DROP' and PRODUCT='SQL*Plus';
1 row updated.
SQL> commit;
Commit complete.
SQL>
DB Keep Pool
=============
set pages 999
set lines 92
spool keep_syn.lst
drop table t1;
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
Stored Outlines:
================
http://www.oracle-base.com/articles/misc/Outlines.php
SELECT SQL_ID,HASH_VALUE FROM V$SQL WHERE SQL_ID='bknbsrc9yaxfq';
SELECT SQL_TEXT FROM V$SQL WHERE HASH_VALUE='333805014';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='9.2.0';
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 333805014,
child_number => 0,
category => 'C2C2_OUTLINES2');
END;
/
SQL> SELECT name, category, sql_text FROM user_outlines WHERE category = 'C2C2_OUTLINES2';
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_OUTLINE_11071507080129703 C2C2_OUTLINES2 SELECT v.company_code, v.site_code, v.receiving_number, v.package_number, v.purc
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'SYS_OUTLINE_11071507080129703';
SELECT name, category, used FROM user_outlines where category like '%C2C2%';
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='11.1.0.7';
ALTER system SET query_rewrite_enabled=TRUE;
ALTER system SET use_stored_outlines=C2C2_OUTLINES2;
run the query now
then explain plan for query
then check the outline status
SELECT name, category, used FROM user_outlines where category like '%C2C2%';
Shrink datafile:
================
column cmd format a75 word_wrapped
select 'alter database datafile '||file_name||' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'m;' cmd
from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b
where a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) -ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
***************ROLES*************
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 999
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
spool roles_grant.sql
select 'grant ' ||GRANTED_ROLE || ' to ' ||GRANTEE|| ' with ADMIN OPTION ;'
from dba_role_privs where ADMIN_OPTION='YES'
UNION
select 'grant ' ||GRANTED_ROLE || ' to ' ||GRANTEE|| ';'
from dba_role_privs where ADMIN_OPTION='NO' ;
SELECT 'EXIT;' FROM DUAL;
SPOOL OFF
SET TERMOUT ON
EXIT;
***************object & sys privs***********
SET TERMOUT OFF
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
set feedback off
set heading off
spool sys_obj_privs_grant.sql
select 'grant ' || PRIVILEGE || ' to ' || GRANTEE|| ';'from dba_sys_privs
UNION
select 'grant ' ||PRIVILEGE|| ' on ' ||owner || '.'||TABLE_NAME|| ' to ' ||GRANTEE|| ' with grant OPTION ;'
from dba_tab_privs where GRANTABLE='YES' and owner not in (select username from dba_users where username like '%SYS%')
UNION
select 'grant ' ||PRIVILEGE|| ' on ' ||owner || '.'||TABLE_NAME|| ' to ' ||GRANTEE|| ';'
from dba_tab_privs where GRANTABLE='NO' and owner not in (select username from dba_users where username like '%SYS%');
SELECT 'EXIT;' FROM DUAL;
SPOOL OFF
SET TERMOUT ON
EXIT;
WF Sync from backend
=======================
begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPS_TS_TX_DATA'); END;
Resize datafiles
=================
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 and a.tablespace_name='APPS_TS_TX_DATA'
/
Query to find object growth
==========================
SQL> col object_name for a35
SQL> set pages 0
SQL> set linesize 200
SQL> select * from (select c.TABLESPACE_NAME,c.segment_name "Object Name",b.object_type,
2 sum(space_used_delta)/1024/1024 "Growth (MB)"
3 from dba_hist_snapshot sn,
4 dba_hist_seg_stat a,
5 dba_objects b,
6 dba_segments c
7 where begin_interval_time > trunc(sysdate) - &days_back
8 and sn.snap_id = a.snap_id
9 and b.object_id = a.obj#
10 and b.owner = c.owner
11 and b.object_name = c.segment_name
12 and b.object_name = c.segment_name
13 group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
14 order by 3 asc;
Enter value for days_back: 30
old 7: where begin_interval_time > trunc(sysdate) - &days_back
new 7: where begin_interval_time > trunc(sysdate) - 30
OPP
====
UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
SSO Issue
===========
select fpot.user_profile_option_name, fpov.profile_option_value,fpov.last_update_date,fu.user_name
from apps.fnd_profile_options fpo, apps.fnd_profile_options_tl fpot, apps.fnd_profile_option_values fpov, apps.fnd_user fu
where fpo.profile_option_id = fpov.profile_option_id
and fpov.level_id=10001
and fpov.last_updated_by=fu.user_id
and fpo.profile_option_name in ('APPS_FRAMEWORK_AGENT','APPS_SSO','APPS_SSO_LDAP_SYNC','APPS_SSO_LOCAL_LOGIN','APPS_SSO_ALLOW_MULTIPLE_ACCOUNTS','APPS_SSO_USER_CREATE_UPDATE','APPS_SSO_LINK_TRUTH_SRC','APPS_SSO_AUTO_LINK_USER','FND_OVERRIDE_SSO_LANG')
and fpo.profile_option_name=fpot.profile_option_name
and fpot.language='US'
order by 1;
select fpot.user_profile_option_name, fpov.profile_option_value,fpov.last_update_date,fu1.user_name
from apps.fnd_profile_options fpo, apps.fnd_profile_options_tl fpot, apps.fnd_profile_option_values fpov, apps.fnd_user fu,apps.fnd_user fu1
where fpo.profile_option_id = fpov.profile_option_id
and fpov.level_id=10004
and fpov.level_value=fu.user_id
and fpov.last_updated_by=fu1.user_id
and fpo.profile_option_name=fpot.profile_option_name
and fpot.language='US'
and fu.user_name='502064474'
order by 1;
select user_name,end_date,user_guid from apps.fnd_user where user_name='502078503';
Out of Memory
==============
SQL> !prtconf | grep Mem
Memory size: 131072 Megabytes
id -p --need to run in oracle user to find the projid
orapgre2@tsgsd5904 $ id -p
uid=502064261(orapgre2) gid=502064270(dbapgre2) projid=1001(user.orapgre2)
login as root
==============
Please managed user's shared memory appropriately. You will have to remove any shared memory in use in order to restart the database, user can manage.
tsgsd3900 > ipcs -m | grep oraogpx2
m 1912602658 0xbe82356d --rw-rw---- oraogpx2 dbaogpx2
m 587202582 0xbe82356c --rw-rw---- oraogpx2 dbaogpx2
use ipcrm command to remove memory in use to restart.
prctl -n project.max-shm-memory -i project 3 --to check the correct allocated memory
project: 1001: user.orapgre2
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 5.00GB - deny -
system 16.0EB max deny -
prctl -n project.max-shm-memory -r -v 37G -i project 3 --increased to 7G from 5G (pls verify before execute )
add responsibility using sqlplus
================================
BEGIN
fnd_user_pkg.addresp ('DBAREAD','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','System Administrator Responsibility',SYSDATE,SYSDATE + 2);
COMMIT;
END;
/
Pinning objects
===========
select 'execute dbms_shared_pool.keep('''||owner||'.'||name||''','''||decode(type,'PACKAGE BODY','P','PACKAGE','P','FUNCTION','P','PROCEDURE','P','TRIGGER','R','TYPE','T','SEQUENCE','Q')||''');' FROM v$db_object_cache
WHERE type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE','INVALID TYPE','CURSOR')AND loads>1 AND executions>loads AND executions>100 AND kept='NO'ORDER BY owner,namespace,type,executions desc;
RAC cluster commands
==================
crsctl status resource -t |grep -i jd1|egrep -v "acfs|vip|dg|agent"
ora.GPSPGJP1.lsnr
gpspgjp1_X
gpspgjp1_conc_mgr
gpspgjp1_dollaru
gpspgjp1_ebs_app_apppgjp101
gpspgjp1_ebs_app_apppgjp102
gpspgjp1_ebs_app_apppgjp1x01
gpspgjp1_ebs_app_apppgjp1x02
ora.gpspgjp1.db
tsgsp5010#
Status Services :
============================
crsctl status resource gpspgjp1_ebs_app_apppgjp1x01
crsctl status resource gpspgjp1_ebs_app_apppgjp1x02
crsctl status resource gpspgjp1_ebs_app_apppgjp101
crsctl status resource gpspgjp1_ebs_app_apppgjp102
crsctl status resource gpspgjp1_dollaru
crsctl status resource gpspgjp1_conc_mgr
crsctl status resource gpspgjp1_X
Stop Application Services
==========================
crsctl stop resource gpspgjp1_ebs_app_apppgjp1x01
crsctl stop resource gpspgjp1_ebs_app_apppgjp1x02
crsctl stop resource gpspgjp1_ebs_app_apppgjp101
crsctl stop resource gpspgjp1_ebs_app_apppgjp102
crsctl stop resource gpspgjp1_dollaru
crsctl stop resource gpspgjp1_conc_mgr
crsctl stop resource gpspgjp1_X
check for any third party Application like Aventx
=================================================== 8:14 AM
i. login to admin node as application user (apppgjp1)
ii. fcmgr -admin -c stop ( to stop the Aventx unix services)
cd $FCHOME/bin
fcmgr -admin
>lc ( to check the status of Aventx unix services)
>stop ( to stop the Aventx unix services if running)
>e (exit from the prompt)
iii. $FCHOME/cfg/scripts/srvctl stop ( to stop fcsrv services)
iv. sh $CATALINA_HOME/bin/shutdown.sh ( to stop tomcat web manager services)
v. sh /gpspgjp1/erpapp/appl/tools/AventX/oa/extproc/AventXDaemon/stop.sh
==================================================
Database and Listener
crsctl stop resource ora.GPSPGJP1.lsnr
crsctl stop resource ora.gpspgjp1.db
Status Services :
============================
crsctl status resource gpspgjp1_ebs_app_apppgjp1x01
crsctl status resource gpspgjp1_ebs_app_apppgjp1x02
crsctl status resource gpspgjp1_ebs_app_apppgjp101
crsctl status resource gpspgjp1_ebs_app_apppgjp102
crsctl status resource gpspgjp1_dollaru
crsctl status resource gpspgjp1_conc_mgr
crsctl status resource gpspgjp1_X 8:14 AM
Database and Listener
=====================================
crsctl start resource ora.gpspgjp1.db
crsctl start resource ora.GPSPGJP1.lsnr
Start Application Services
==========================
crsctl start resource gpspgjp1_conc_mgr
crsctl start resource gpspgjp1_X
crsctl start resource gpspgjp1_dollaru
crsctl start resource gpspgjp1_ebs_app_apppgjp1x01
crsctl start resource gpspgjp1_ebs_app_apppgjp1x02
crsctl start resource gpspgjp1_ebs_app_apppgjp101
crsctl start resource gpspgjp1_ebs_app_apppgjp102
check for any third party Application like Aventx
===================================================
i. login to admin node as application user (apppgjp1)
ii. fcmgr -admin -c start ( to start the Aventx unix services)
cd $FCHOME/bin
fcmgr -admin
>lc ( to check the status of Aventx unix services)
>stop ( to stop the Aventx unix services if running)
>e (exit from the prompt)
iii. $FCHOME/cfg/scripts/srvctl start ( to stop fcsrv services)
iv. sh $CATALINA_HOME/bin/startup.sh ( to stop tomcat web manager services)
v. sh /gpspgjp1/erpapp/appl/tools/AventX/oa/extproc/AventXDaemon/start.sh
IMAP
==========
-bash-3.2$ telnet gpsgnep1.tsg.ge.com 143
Trying 3.21.56.208...
Connected to gpsgnep1.tsg.ge.com (3.21.56.208).
Escape character is '^]'.
* OK Dovecot ready.
* login mailgnep xyz00abc
* OK Logged in.
* list * "*"
* LIST (\NoInferiors \UnMarked) "/" "DISCARD"
* LIST (\NoInferiors \UnMarked) "/" "PROCESSED"
* LIST (\NoInferiors \UnMarked) "/" "Trash"
* LIST (\NoInferiors \UnMarked) "/" "INBOX"
* OK List completed.
* logout
* BYE Logging out
* OK Logout completed.
Connection closed by foreign host.
Cluster name modify
====================
crsctl relocate resource appafmx102.appvip -n tsgxd2101 -f
To Fetch Version of Package from backend
===========================================
SELECT name ||' '|| text
FROM dba_source
WHERE line = 2
AND type LIKE 'PACKAGE BODY'
AND owner = 'APPS'
AND name = 'CSI_PROCESS_TXN_GRP'
ORDER BY name, type;
Script to check the running Concurrent jobs
==============================
cmrun2.sql
========
set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 999999999
col "Parent" form a8
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
order by 1,2
/
Or
jobs.sql
=========
set pages 66
set line 132
-- spool progs1.lst
col user_name format a20 word_wrapped
column ProgName format a25 word_wrapped
column requestId format 9999999999
column StartDate format a20 word_Wrapped
column OS_PROCESS_ID format a6
column ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
select sess.sid,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
order by 5 DESC
/
-- spool off
To check the running concurrent requests or Programs under particular Concurrent Manager
=============================================================
qdjobs.sql
=========
set pagesize 132
col USER_CONCURRENT_PROGRAM_NAME format a70 wrap
col StartDate format a20 wrap
SELECT
REQUEST_ID,
PHASE_CODE,
STATUS_CODE,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
REQUESTED_BY,
fcp.user_concurrent_program_name
FROM
FND_CONCURRENT_WORKER_REQUESTS FCWR,
FND_CONCURRENT_PROGRAMS_TL FCP
WHERE
(Phase_Code = 'P' OR Phase_Code = 'R')
AND hold_flag != 'Y'
AND Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE in ( 'R','P') AND STATUS_CODE IN ('I', 'Q')))
AND (CONCURRENT_QUEUE_ID in (select CONCURRENT_QUEUE_ID FROM FND_CONCURRENT_QUEUES_VL WHERE USER_CONCURRENT_QUEUE_NAME like
'%&manager_name%' ))
--AND (QUEUE_APPLICATION_ID=0)
AND FCP.CONCURRENT_PROGRAM_ID = FCWR.CONCURRENT_PROGRAM_ID
and fcp.language = 'US'
ORDER BY fcp.user_concurrent_program_name, STATUS_CODE,actual_Start_date,Priority, Priority_Request_ID, Request_ID
/
Concurrent Program previous history
===========================
conc_hist.sql
===========
set linesize 152
set pages 1000
col username for a10
col status for a10
col phase for a10
col PNAME for a15
col mins for 99999
col start_date for a18
col end_date for a18
col request_id for 99999999999
select
b.user_name username,
request_id as REQ_ID,
a.controlling_manager cm,
decode(a.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown') phase_code ,
decode(a.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'G','Warning',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
) status_code ,
a.USER_CONCURRENT_PROGRAM_NAME as PNAME,
to_char(a.REQUESTED_START_DATE,'DD-MON-YY HH24-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'DD-MON-YY HH24-MI-SS') END_DATE,
(a.ACTUAL_COMPLETION_DATE - a.REQUESTED_START_DATE)*24*60 "mins"
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
--status_code in ('E') and
upper(a.USER_CONCURRENT_PROGRAM_NAME) like upper('%&str%') and
a.REQUESTED_BY=b.user_id
and rownum<100order by ACTUAL_COMPLETION_DATE;
To check the request details
======================
analreq.sql
=========
set serveroutput on size 10000
set feedback off
set verify off
set heading off
set timing off
variable help_text varchar2(2000);
prompt
DECLARE
req_id number(15) := &1;
FUNCTION get_status(p_status_code varchar2) return varchar2 AS
c_status fnd_lookups.meaning%TYPE;
BEGIN
SELECT nvl(meaning, 'UNKNOWN')
INTO c_status
FROM fnd_lookups
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
AND LOOKUP_CODE = p_status_code;
return rtrim(c_status);
END get_status;
FUNCTION get_phase(p_phase_code varchar2) return varchar2 AS
c_phase fnd_lookups.meaning%TYPE;
BEGIN
SELECT nvl(meaning, 'UNKNOWN')
INTO c_phase
FROM fnd_lookups
WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
AND LOOKUP_CODE = p_phase_code;
return rtrim(c_phase);
END get_phase;
PROCEDURE manager_check (req_id in number,
cd_id in number,
mgr_defined out boolean,
mgr_active out boolean,
mgr_workshift out boolean,
mgr_running out boolean,
run_alone out boolean) is
cursor mgr_cursor (rid number) is
select running_processes, max_processes,
decode(control_code,
'T','N', -- Abort
'X','N', -- Aborted
'D','N', -- Deactivate
'E','N', -- Deactivated
'Y') active
from fnd_concurrent_worker_requests
where request_id = rid
and not((queue_application_id = 0)
and (concurrent_queue_id in (1,4)));
run_alone_flag varchar2(1);
begin
mgr_defined := FALSE;
mgr_active := FALSE;
mgr_workshift := FALSE;
mgr_running := FALSE;
for mgr_rec in mgr_cursor(req_id) loop
mgr_defined := TRUE;
if (mgr_rec.active = 'Y') then
mgr_active := TRUE;
if (mgr_rec.max_processes > 0) then
mgr_workshift := TRUE;
end if;
if (mgr_rec.running_processes > 0) then
mgr_running := TRUE;
end if;
end if;
end loop;
if (cd_id is null) then
run_alone_flag := 'N';
else
select runalone_flag
into run_alone_flag
from fnd_conflicts_domain d
where d.cd_id = manager_check.cd_id;
end if;
if (run_alone_flag = 'Y') then
run_alone := TRUE;
else
run_alone := FALSE;
end if;
end manager_check;
PROCEDURE print_mgrs(p_req_id number) AS
CURSOR c_mgrs(rid number) IS
SELECT user_concurrent_queue_name name, fcwr.running_processes active,
decode(fcwr.control_code, 'A', fl.meaning,
'D', fl.meaning,
'E', fl.meaning,
'N', fl.meaning,
'R', fl.meaning,
'T', fl.meaning,
'U', fl.meaning,
'V', fl.meaning,
'X', fl.meaning,
NULL, 'Running',
'** Unknown Status **') status
FROM fnd_concurrent_queues_vl fcqv, fnd_concurrent_worker_requests fcwr, fnd_lookups fl
WHERE fcwr.request_id = rid
AND fcwr.concurrent_queue_id = fcqv.concurrent_queue_id
AND fcwr.concurrent_queue_id not in (1, 4)
AND fl.lookup_code (+) = fcwr.control_code
AND fl.lookup_type (+) = 'CP_CONTROL_CODE';
BEGIN
for mgr_rec in c_mgrs(p_req_id) loop
DBMS_OUTPUT.PUT_LINE('- ' || mgr_rec.name || ' | Status: ' || mgr_rec.status
|| ' (' || mgr_rec.active || ' active processes)');
end loop;
END print_mgrs;
PROCEDURE analyze_request(p_req_id number) AS
reqinfo fnd_concurrent_requests%ROWTYPE;
proginfo fnd_concurrent_programs_vl%ROWTYPE;
c_status fnd_lookups.meaning%TYPE;
m_buf fnd_lookups.meaning%TYPE;
conc_prog_name fnd_concurrent_programs.concurrent_program_name%TYPE;
exe_method_code fnd_concurrent_programs_vl.execution_method_code%TYPE;
conc_app_name fnd_application_vl.application_name%TYPE;
tmp_id number(15);
tmp_status fnd_concurrent_requests.status_code%TYPE;
tmp_date date;
conc_app_id fnd_concurrent_requests.program_application_id%TYPE;
conc_id fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id fnd_concurrent_requests.cd_id%TYPE;
v_enabled_flag fnd_concurrent_programs.enabled_flag%TYPE;
conflict_domain fnd_conflicts_domain.user_cd_name%TYPE;
parent_id number(15);
resp_name varchar2(100);
rclass_name fnd_concurrent_request_class.request_class_name%TYPE;
exe_file_name fnd_executables.execution_file_name%TYPE;
c_user fnd_user.user_name%TYPE;
last_user fnd_user.user_name%TYPE;
fcd_phase varchar2(48);
fcd_status varchar2(48);
traid fnd_concurrent_requests.program_application_id%TYPE;
trcpid fnd_concurrent_requests.concurrent_program_id%TYPE;
icount number;
ireqid fnd_concurrent_requests.request_id%TYPE;
pcode fnd_concurrent_requests.phase_code%TYPE;
scode fnd_concurrent_requests.status_code%TYPE;
live_child boolean;
mgr_defined boolean;
mgr_active boolean;
mgr_workshift boolean;
mgr_running boolean;
run_alone boolean;
reqlimit boolean := false;
mgrname fnd_concurrent_queues_vl.user_concurrent_queue_name%TYPE;
filename varchar2(255);
qcf fnd_concurrent_programs.queue_control_flag%TYPE;
apps_version varchar2(3);
sep varchar2(200) := '------------------------------------------------------';
REQ_NOTFOUND exception;
CURSOR c_wait IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = p_req_id;
CURSOR c_inc IS
SELECT to_run_application_id, to_run_concurrent_program_id
FROM fnd_concurrent_program_serial
WHERE running_application_id = conc_app_id
AND running_concurrent_program_id = conc_id;
CURSOR c_ireqs IS
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code = 'R'
AND program_application_id = traid
AND concurrent_program_id = trcpid
AND cd_id = conc_cd_id;
CURSOR c_userreqs(uid number, s date) IS
SELECT request_id, to_char(requested_start_date, 'DD-MON-RR HH24:MI:SS') start_date,
phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code IN ('R', 'P')
AND requested_by = uid
AND requested_start_date < s
AND hold_flag = 'N';
BEGIN
BEGIN
SELECT *
INTO reqinfo
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise REQ_NOTFOUND;
END;
DBMS_OUTPUT.PUT_LINE('Analyzing request '||req_id||':');
DBMS_OUTPUT.PUT_LINE(sep);
-- Program information
DBMS_OUTPUT.PUT_LINE('Program information:');
SELECT fvl.*
INTO proginfo
FROM fnd_concurrent_programs_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.concurrent_program_id = fvl.concurrent_program_id
AND fcr.program_application_id = fvl.application_id;
DBMS_OUTPUT.PUT_LINE('Program: '|| proginfo.user_concurrent_program_name
|| ' (' || proginfo.concurrent_program_name || ')');
SELECT nvl(application_name, '-- UNKNOWN APPLICATION --')
INTO conc_app_name
FROM fnd_application_vl fvl, fnd_concurrent_requests fcr
WHERE fcr.request_id = p_req_id
AND fcr.program_application_id = fvl.application_id;
DBMS_OUTPUT.PUT_LINE('Application: '||conc_app_name);
SELECT nvl(meaning, 'UNKNOWN')
INTO m_buf
FROM fnd_lookups
WHERE lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND lookup_code = proginfo.execution_method_code;
SELECT nvl(execution_file_name, 'NONE')
INTO exe_file_name
FROM fnd_executables
WHERE application_id = proginfo.executable_application_id
AND executable_id = proginfo.executable_id;
DBMS_OUTPUT.PUT_LINE('Executable type: ' || m_buf || ' (' || proginfo.execution_method_code || ')');
DBMS_OUTPUT.PUT_LINE('Executable file name or procedure: ' || exe_file_name);
DBMS_OUTPUT.PUT_LINE('Run alone flag: ' || proginfo.run_alone_flag);
DBMS_OUTPUT.PUT_LINE('SRS flag: ' || proginfo.srs_flag);
DBMS_OUTPUT.PUT_LINE('NLS compliant: ' || proginfo.nls_compliant);
DBMS_OUTPUT.PUT_LINE('Output file type: ' || proginfo.output_file_type);
if proginfo.concurrent_class_id is not null then
select request_class_name
into rclass_name
from fnd_concurrent_request_class
where application_id = proginfo.class_application_id
and request_class_id = proginfo.concurrent_class_id;
DBMS_OUTPUT.PUT_LINE('Request type: ' || rclass_name);
end if;
if proginfo.execution_options is not null then
DBMS_OUTPUT.PUT_LINE('Execution options: ' || proginfo.execution_options);
end if;
if proginfo.enable_trace = 'Y' then
DBMS_OUTPUT.PUT_LINE('SQL Trace has been enabled for this program.');
end if;
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('
');
DBMS_OUTPUT.PUT_LINE(sep);
-- Submission information
DBMS_OUTPUT.PUT_LINE('Submission information:');
begin
SELECT user_name into c_user from fnd_user
where user_id = reqinfo.requested_by;
exception
when no_data_found then
c_user := '-- UNKNOWN USER --';
end;
begin
SELECT user_name into last_user from fnd_user
WHERE user_id = reqinfo.last_updated_by;
exception
when no_data_found then
last_user := '-- UNKNOWN USER --';
end;
DBMS_OUTPUT.PUT_LINE('It was submitted by user: '||c_user);
SELECT responsibility_name
INTO resp_name
FROM fnd_responsibility_vl
WHERE responsibility_id = reqinfo.responsibility_id
AND application_id = reqinfo.responsibility_application_id;
DBMS_OUTPUT.PUT_LINE('Using responsibility: ' || resp_name);
DBMS_OUTPUT.PUT_LINE('It was submitted on: ' || to_char(reqinfo.request_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('It was requested to start on: '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Parent request id: ' || reqinfo.parent_request_id);
DBMS_OUTPUT.PUT_LINE('Language: ' || reqinfo.nls_language);
DBMS_OUTPUT.PUT_LINE('Territory: ' || reqinfo.nls_territory);
DBMS_OUTPUT.PUT_LINE('Priority: ' || to_char(reqinfo.priority));
DBMS_OUTPUT.PUT_LINE('Arguments (' || reqinfo.number_of_arguments || '): ' || reqinfo.argument_text);
c_status := get_status(reqinfo.status_code);
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('
');
DBMS_OUTPUT.PUT_LINE(sep);
-- Analysis
DBMS_OUTPUT.PUT_LINE('Analysis:');
-- Completed Requests
-------------------------------------------------------------------------------------------------------------
IF reqinfo.phase_code = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has completed with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
DBMS_OUTPUT.PUT_LINE('It completed on: '||
nvl(to_char(reqinfo.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO COMPLETION DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id =
(select CONCURRENT_QUEUE_ID
from fnd_concurrent_processes
where CONCURRENT_PROCESS_ID = reqinfo.controlling_manager);
DBMS_OUTPUT.PUT_LINE('It was run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('It was run by an unknown manager.');
END;
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
DBMS_OUTPUT.PUT_LINE('It produced completion message: ');
DBMS_OUTPUT.PUT_LINE(nvl(reqinfo.completion_text, '-- NO COMPLETION MESSAGE --'));
-- Running Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'R' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is currently running with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE('It began running on: '||
nvl(to_char(reqinfo.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
'-- NO START DATE --'));
BEGIN
SELECT user_concurrent_queue_name
INTO mgrname
FROM fnd_concurrent_queues_vl
WHERE concurrent_queue_id = reqinfo.controlling_manager;
DBMS_OUTPUT.PUT_LINE('It is being run by manager: ' || mgrname);
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
SELECT nvl(reqinfo.logfile_name, '-- No logfile --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Logfile: ' || filename);
SELECT nvl(reqinfo.outfile_name, '-- No output file --')
INTO filename
FROM dual;
DBMS_OUTPUT.PUT_LINE('Output file: ' || filename);
IF reqinfo.status_code = 'Z' THEN
-- Waiting request, See what it is waiting on
FOR child in c_wait LOOP
DBMS_OUTPUT.PUT_LINE('It is waiting on request '||
child.request_id||' phase = '||get_phase(child.phase_code)||
' status = '||get_status(child.status_code));
END LOOP;
ELSIF reqinfo.status_code = 'W' THEN
-- Paused, check and see if it is a request set, and if its children are running
SELECT nvl(concurrent_program_name, 'UNKNOWN')
INTO conc_prog_name
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id;
DBMS_OUTPUT.PUT_LINE('A Running/Paused request is waiting on one or more child requests to complete.');
IF conc_prog_name = 'FNDRSSTG' THEN
DBMS_OUTPUT.PUT_LINE('This program is a Request Set Stage.');
END IF;
IF instr(conc_prog_name, 'RSSUB') > 0 THEN
DBMS_OUTPUT.PUT_LINE('This program is a Request Set parent program.');
END IF;
live_child := FALSE;
FOR child in c_wait LOOP
DBMS_OUTPUT.PUT_LINE('It has a child request: '||
child.request_id||' (phase = '||get_phase(child.phase_code)||
' - status = '||get_status(child.status_code)||')');
IF child.phase_code != 'C' THEN
live_child := TRUE;
END IF;
END LOOP;
IF live_child = FALSE THEN
DBMS_OUTPUT.PUT_LINE('This request has no child requests
that are still running. You may need to wake this request up manually.');
END IF;
END IF;
-- Pending Requests
-------------------------------------------------------------------------------------------------------------
ELSIF reqinfo.phase_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' is in phase "Pending" with status "'||c_status||'".');
DBMS_OUTPUT.PUT_LINE(' (phase_code = P) (status_code = '||reqinfo.status_code||')');
-- could be a queue control request
SELECT queue_control_flag
INTO qcf
FROM fnd_concurrent_programs
WHERE concurrent_program_id = reqinfo.concurrent_program_id
AND application_id = reqinfo.program_application_id;
IF qcf = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is a queue control request');
DBMS_OUTPUT.PUT_LINE('It will be run by the ICM on its next sleep cycle');
GOTO diagnose;
END IF;
-- why is it pending?
-- could be scheduled
IF reqinfo.requested_start_date > sysdate or reqinfo.status_code = 'P' THEN
DBMS_OUTPUT.PUT_LINE('This is a scheduled request.');
DBMS_OUTPUT.PUT_LINE('It is currently scheduled to start running on '||
to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Pending/Scheduled');
GOTO diagnose;
END IF;
-- could be on hold
IF reqinfo.hold_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently on hold. It will not run until the hold is released.');
DBMS_OUTPUT.PUT_LINE('It was placed on hold by: '||last_user||' on
'||to_char(reqinfo.last_update_date, 'DD-MON-RR HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/On Hold');
GOTO diagnose;
END IF;
-- could be disabled
IF proginfo.enabled_flag = 'N' THEN
DBMS_OUTPUT.PUT_LINE('This request is currently disabled.');
DBMS_OUTPUT.PUT_LINE('The concurrent_program '|| proginfo.user_concurrent_program_name
||' needs to be enabled for this request to run.');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/Disabled');
GOTO diagnose;
END IF;
-- check queue_method_code
-- unconstrained requests
IF reqinfo.queue_method_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('This request is an unconstrained request. (queue_method_code = I)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Normal" status, ready
to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It has a status of "Standby" even though
it is unconstrained. It will not be run by any manager.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
END IF;
-- constrained requests
ELSIF reqinfo.queue_method_code = 'B' THEN
DBMS_OUTPUT.PUT_LINE('This request is a constrained request. (queue_method_code = B)');
IF reqinfo.status_code = 'I' THEN
DBMS_OUTPUT.PUT_LINE('The Conflict Resolution manager has released
this request, and it is in a "Pending/Normal" status.');
DBMS_OUTPUT.PUT_LINE('It is ready to be run by the next available manager.');
ELSIF reqinfo.status_code = 'Q' THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Pending/Standby" status.
The Conflict Resolution manager will need to release it before it can be run.');
ELSIF reqinfo.status_code IN ('A', 'Z') THEN
DBMS_OUTPUT.PUT_LINE('It is in a "Waiting" status. This usually
indicates a child request waiting for the parent to release it.');
SELECT nvl(parent_request_id, -1)
INTO parent_id
FROM fnd_conc_req_summary_v
WHERE request_id = p_req_id;
IF parent_id = -1 THEN
DBMS_OUTPUT.PUT_LINE('** Unable to find a parent request for this request');
ELSE
DBMS_OUTPUT.PUT_LINE('It''s parent request id is: ' || to_char(parent_id));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Hmmm. A status of ' || reqinfo.status_code
|| '. I was not really expecting to see this status.');
END IF;
-- incompatible programs
SELECT program_application_id, concurrent_program_id, cd_id
INTO conc_app_id, conc_id, conc_cd_id
FROM fnd_concurrent_requests
WHERE request_id = p_req_id;
icount := 0;
FOR progs in c_inc LOOP
traid := progs.to_run_application_id;
trcpid := progs.to_run_concurrent_program_id;
OPEN c_ireqs;
LOOP
FETCH c_ireqs INTO ireqid, pcode, scode;
EXIT WHEN c_ireqs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Request '|| p_req_id ||' is
waiting, or will have to wait, on an incompatible request: '|| ireqid );
DBMS_OUTPUT.PUT_LINE('which has phase = '|| pcode ||' and status = '|| scode);
icount := icount + 1;
END LOOP;
CLOSE c_ireqs;
END LOOP;
IF icount = 0 THEN
DBMS_OUTPUT.PUT_LINE('No running incompatible requests were found for request '||p_req_id);
END IF;
-- could be a runalone itself
IF proginfo.run_alone_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because it is a runalone request.');
END IF;
-- single threaded
IF reqinfo.single_thread_flag = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Sequential Requests is set.');
reqlimit := true;
END IF;
-- request limit
IF reqinfo.request_limit = 'Y' THEN
DBMS_OUTPUT.PUT_LINE('This request is constrained because the
profile option Concurrent: Active Request Limit is set.');
reqlimit := true;
END IF;
IF reqlimit = true THEN
DBMS_OUTPUT.PUT_LINE('This request may have to wait on these requests:');
FOR progs in c_userreqs(reqinfo.requested_by, reqinfo.requested_start_date) LOOP
DBMS_OUTPUT.PUT_LINE('Request id: ' || progs.request_id ||
' Requested start date: ' || progs.start_date);
DBMS_OUTPUT.PUT_LINE(' Phase: ' || get_phase(progs.phase_code)
|| ' Status: ' || get_status(progs.status_code));
END LOOP;
END IF;
-- error, invalid queue_method_code
ELSE
DBMS_OUTPUT.PUT_LINE('** This request has an invalid queue_method_code of '||reqinfo.queue_method_code);
DBMS_OUTPUT.PUT_LINE('** This request will not be run. You may need to apply patch 739644.');
GOTO diagnose;
END IF;
DBMS_OUTPUT.PUT_LINE(sep);
DBMS_OUTPUT.PUT_LINE('Checking managers available to run this request...');
-- check the managers
manager_check(p_req_id, reqinfo.cd_id, mgr_defined, mgr_active, mgr_workshift, mgr_running, run_alone);
-- could be a runalone ahead of it
IF run_alone = TRUE THEN
DBMS_OUTPUT.PUT_LINE('There is a runalone request running ahead of this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
select user_cd_name into conflict_domain from fnd_conflicts_domain
where cd_id = reqinfo.cd_id;
DBMS_OUTPUT.PUT_LINE('Conflict domain = '||conflict_domain);
-- see what is running
begin
select request_id, status_code, actual_start_date
into tmp_id, tmp_status, tmp_date
from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
where fcp.run_alone_flag = 'Y'
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.phase_code = 'R'
and fcr.cd_id = reqinfo.cd_id;
DBMS_OUTPUT.PUT_LINE('This request is waiting for request '||tmp_id||
', which is running with status '||get_status(tmp_status));
DBMS_OUTPUT.PUT_LINE('It has been running since: '||
nvl(to_char(tmp_date, 'DD-MON-RR HH24:MI:SS'), '-- NO START DATE --'));
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('** The runalone flag is set for conflict domain '||conflict_domain||
', but there is no runalone request running');
end;
ELSIF mgr_defined = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is no manager defined that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
DBMS_OUTPUT.PUT_LINE('Check the specialization rules for each
manager to make sure they are defined correctly.');
ELSIF mgr_active = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There are one or more managers defined
that can run this request, but none of them are currently active');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
DBMS_OUTPUT.PUT_LINE('These managers are defined to run this request:');
print_mgrs(p_req_id);
ELSIF mgr_workshift = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Right now, there is no manager running
in an active workshift that can run this request');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- display details about the workshifts
ELSIF mgr_running = FALSE THEN
DBMS_OUTPUT.PUT_LINE('There is one or more managers available
to run this request, but none of them are running');
DBMS_OUTPUT.PUT_LINE('This should show on the form as Inactive/No Manager');
-- print out which managers can run it and their status
print_mgrs(p_req_id);
ELSE
-- print out the managers available to run it
DBMS_OUTPUT.PUT_LINE('These managers are available to run this request:');
print_mgrs(p_req_id);
END IF;
-- invalid phase code
ELSE
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' has an invalid phase_code of "'||reqinfo.phase_code||'"');
END IF;
<<diagnose>>
BEGIN
FND_CONC.DIAGNOSE(p_req_id, fcd_phase, fcd_status, :help_text);
EXCEPTION
WHEN OTHERS THEN
:help_text := 'The FND_CONC package has not been installed on this system.';
END;
DBMS_OUTPUT.PUT_LINE(sep);
EXCEPTION
WHEN REQ_NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('Request '||p_req_id||' not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error number ' || sqlcode || ' has occurred.');
DBMS_OUTPUT.PUT_LINE('Cause: ' || sqlerrm);
END analyze_request;
BEGIN
analyze_request(req_id);
END;
/
To Check the session details
======================
sess.sql
==========
def aps_prog = 'sessinfo.sql'
def aps_title = 'Session information'
col "Session Info" form A100
set verify off
accept sid prompt 'Please enter the value for Sid if known : '
accept terminal prompt 'Please enter the value for terminal if known : '
accept machine prompt 'Please enter the machine name if known : '
accept process prompt 'Please enter the value for Client Process if known : '
accept spid prompt 'Please enter the value for Server Process if known : '
accept osuser prompt 'Please enter the value for OS User if known : '
accept username prompt 'Please enter the value for DB User if known : '
select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| ' DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| ' Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
' OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| ' (Since) '||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
' Client Program Name : '||s.program||chr(10) ||
' Action / Module : '||s.action||' / '||s.module||chr(10) || chr(10) ||
' Wait Status : '||s.event || ' ' || s.seconds_in_wait || ' ' || s.state "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and s.sid = nvl('&SID',s.sid)
and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
and nvl(s.process,-1) = nvl('&Process',nvl(s.process,-1))
and p.spid = nvl('&spid',p.spid)
and s.username = nvl('&username',s.username)
and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/
undefine sid
To check the current running all sessions with wait events
=======================================
waits.sql
==========
set lines 132
set pagesize 1000
column sid format 99999
column event format a25 word_wrapped
column module format a15 word_wrapped
column username format a9 word_wrapped
column seconds_in_wait format 999999 word_wrapped
select b.sid, b.event, substr(a.action,1,1)||'-'||a.module module , a.username ,
b.p1, b.p2,b.p3 ,b.seconds_in_wait from v$session_wait b, v$session a
where b.event not in ('SQL*Net message from client','rdbms ipc message',
'pmon timer','smon timer', 'pipe get')
and a.sid = b.sid
order by 8
/
whywait.sql
==========
col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status
from v$session_wait a, v$session s
where a.sid=s.sid
and a.sid=&SID
/
To fetch the running sql from sid
========================
getsql.sql
=========
break on hash_value
set pagesize 1000
set long 200000
select hash_value, sql_text
from v$sqltext
where hash_value in
(select sql_hash_value from
v$session where sid = &sid)
order by piece
/
To check the session row movement
========================
sessrow.sql
==========
column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&sid
and a.value != 0
and b.name like '%row%'
/
To fetch the explain plan from the running session
==================================
expl.sql
=======
--variable sqlid varchar2(13);
column sql_id new_value sqlid
select sql_id from v$session where sid=&sid
/
select plan_table_output from table(DBMS_XPLAN.display_cursor('&sqlid'))
/
You can also run explain plan for in sql propmt and provide sql and check explain plan using utlxpls.sql
=======================================================================
sql> explain plan for
select name from v$database;
explained
sql> @utlxpls.sql --to view the explain plan for particular sql stmt
utlxpls.sql
========
set markup html preformat on
Rem
Rem Use the display table function from the dbms_xplan package to display the last
Rem explain plan. Force serial option for backward compatibility
Rem
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
To check the TEMP usage
==================
sortsize.sql
========
col used format 999,999,999,999
col free format 999,999,999,999
col maxused format 999,999,999,999
col totalsize format 999,999,999,999
SELECT tablespace_name, (TOTAL_BLOCKS * 8192) "totalsize",
(USED_BLOCKS * 8192 ) "used" , (FREE_BLOCKS * 8192) "free" ,
(MAX_USED_BLOCKS * 8192 ) "maxused"
FROM v$sort_segment;
psort.sql --to check the temp usage sessions
=========
col module format a15
col username format a15
col tablespace format a15
col size format 999,999,999,999
set lines 120
SELECT s.sid,s.username,s.module,u.contents,segtype,TABLESPACE, sum((u.blocks * 8192)) "size"
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
and u.extents > 10
group by s.sid,s.username,s.module,u.contents,segtype,tablespace
order by 7,2,3;
To check the inactive form sessions
==========================
inactive_frm.sql
==============
set pages 200 lines 200
SELECT a.sid,a.serial#, substr(b.object_name,1,40) object_name,
DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) "Lock Time"
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;
inactive_frm_kill.sql
==============
set pages 999
set head off
select 'alter system kill session '''||a.sid||','||a.serial#||''''||' immediate;'
from v$session a, dba_objects b, v$locked_object c, v$session d
where a.sid = c.session_id
and b.object_id = c.object_id and a.status = 'INACTIVE'
And D.Sid = A.Sid
--and substr(b.object_name,1,40) like 'GEPS%'
And A.Action Like 'FRM%'
and DECODE( round((d.last_call_et/60)/60),0,round(d.last_call_et/60) || ' Min', round((d.last_call_et/60)/60) || ' HRS' ) not like '%Min%'
order by d.last_call_et/60 desc;
To check the stale objects
====================
stale.sql
=========
set pages 2000
set linesize 200
set heading off
set feedback off
select 'exec fnd_stats.gather_table_stats(ownname =>'''||owner||''',tabname =>'''||table_name||''',percent => 30, cascade => TRUE);'
from dba_tab_statistics where stale_stats='YES'
and owner not in ('SYS','SYSTEM','TRCANLZR','OLAPSYS','XDB')
and table_name not like 'BIN%' order by 1;
To check the Patch details including patch stage
=================================
patch_details.sql
============
set line 132
col patch_top for a30
col name for a10
select a.PATCH_TOP,
c.DRIVER_FILE_NAME,
b.name,
a.CREATION_DATE
from
AD_PATCH_RUNS a,
AD_APPL_TOPS b,
AD_PATCH_DRIVERS C
where
a.APPL_TOP_ID=b.APPL_TOP_ID
and c.PATCH_DRIVER_ID=a.PATCH_DRIVER_ID
and c.DRIVER_FILE_NAME like '%&Patch_Name%'
order by a.CREATION_DATE, c.DRIVER_FILE_NAME;
To Kill toad/sql developer sessions
=========================
select 'alter system disconnect session '''||sid||','||serial#||'''immediate;' from v$session where program like '%T.O.A.D%' or program in('plsqldev.exe','SQL Developer','TOAD.exe','PLSQLDev.exe');
To check ASM disk utilization
=======================
select name, state, sum(total_mb/1024), sum(free_mb/1024) from v$asm_diskgroup where name like upper('%&Instance_name%') group by name,state;
Users details from machine
=====================
user_AT.sql
=========
col machine for a25
select count(distinct d.user_name) ,b.machine from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1) group by b.machine;
Patch details including language
========================
col PATCH_NAME for a15
select a.PATCH_NAME, a.APPLIED_PATCH_ID, b.LANGUAGE from ad_applied_patches a, ad_patch_driver_langs b where a.APPLIED_PATCH_ID=b.PATCH_DRIVER_ID and a.PATCH_NAME like '%&PatchNumber%';
To check the ICM status
=================
select apps.fnd_oam_em.get_icm_status from dual;
To check the FND Profiles
======================
set lines 200
col PROFILE_OPTION_NAME for a25
col PROFILE_OPTION_VALUE for a65
select a.profile_option_name, b.profile_option_value, a.profile_option_id, a.application_id from fnd_profile_options a, fnd_profile_option_values b where a.application_id = b.application_id and a.profile_option_id = b.profile_option_id
and b.profile_option_value like '%&value%';
To fetch the trace file name from request
==============================
req_tracefile.sql
============
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 90
set pages 22
set head off
select 'Request id: '||request_id , 'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace, 'Trace Name: '||dest.value||
'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module from
fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest,
v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname
where req.request_id = &request and req.oracle_process_id=proc.spid(+) and
proc.addr = ses.paddr(+) and dest.name='user_dump_dest' and dbnm.name='db_name' and
req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;
To check the RMAN backup details
===========================
rman_bkp_dtl.sql
===============
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS where START_TIME > sysdate-3
order by session_key;
dbms_rman_job.sql
===============
select job_name,job_type,number_of_arguments,job_action,repeat_interval,enabled from dba_scheduler_jobs where JOB_NAME like '%RMAN%';
dbms_rman_pgm.sql
================
select OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,NUMBER_OF_ARGUMENTS,ENABLED from dba_scheduler_programs where PROGRAM_NAME like '%RMAN%';
To check the locks
===============
lock_waits.sql --to check dead locks with details
===============
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
cursor c2 (v_id1 v$lock.ID1%TYPE,v_id2 v$lock.ID2%TYPE) is select sid , type from v$lock
where id1 = v_id1
and id2 = v_id2
and request = 0
and lmode != 4;
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
/*
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
*/
for c2_rec in c2(c1_rec.id1,c1_rec.id2) loop
wsid:=c2_rec.sid;
wtype:=c2_rec.type;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end loop;
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
lckdtl.sql --to chk blocking locks
=========
set feedback on
set lines 132
column sess format a8 word_wrapped
column id1 format 99999999
column id2 format 99999999
column req format 999
column type format a4
column event format a15 word_wrapped
column seconds_in_wait format 999999
column status format a10
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(vl.request,0,0,5),' ')||vl.sid sess
, vl.id1
, vl.id2
, vl.lmode
, vl.request req, vl.type,
sw.event event, sw.seconds_in_wait, s.status
FROM V$LOCK vl, v$session_wait sw, v$session s
WHERE vl.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and vl.sid = sw.sid
and vl.sid = s.sid
ORDER BY id1,request
/
locks.sql-To check all the locks in the database
========
set pagesize 1000
set line 132
break on "SID"
col Object format a35 wrap
col Locker format a10
col Type format a10
col Program format a30 wrap
col Lockmode format a15 wrap
col Wait format 9999999
col Status format a8
SELECT
SESSION_ID "SID",
OWNER||'.'||OBJECT_NAME "Object",
ORACLE_USERNAME "Locker",
-- nvl(lockwait,mysessf(lockwait)) "Wait",
lockwait "Wait",
DECODE(LOCKED_MODE,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode",
OBJECT_TYPE "Type",
c.ROW_WAIT_ROW#,
c.Status "Status"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/
To check the particular user sessions details
==============================
user_sess.sql
==========
def aps_prog = 'sessinfo.sql'
def aps_title = 'Session information'
col "Session Info" form A80
col dbuser form A8
col mach form A8
col program format a20 trunc
col description format a20 trunc
col module format a10 wrap
col action format a10 wrap
set verify off
set lines 132
select s.sid , substr(s.username,1,8) dbuser, substr(s.machine,1,10) mach, s.process,p.spid,f.login_type
,s.action,s.module,to_char(start_time,'DD-MON-YY HH24:MI') stime,
fu.description
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='&user_name'
order by f.start_time
/
To kill particular user sessions
=====================
set pages 0
set lines 132
!rm /tmp/user_sessions.sql
prompt Here is a list of SID's that correspond to the user entered:
set feedback off
spool /tmp/user_sessions.sql
prompt set echo on
set head off
set pages 100
set linesize 255
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';'
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='&user_name'
order by f.start_time;
prompt set echo off
spool off
!chmod 777 /tmp/user_sessions.sql
set feedback on
prompt Verify file @/tmp/user_sessions.sql and run it to kill them .
set head on
set echo off
To kill particular sessions
=================
undefine sid
set serveroutput on
--set heading off
DECLARE
CURSOR get_ps IS
SELECT distinct sid,serial# from v$session
where sid in (&sid);
ps get_ps%ROWTYPE;
sql_cmd VARCHAR2(1000);
sql_cur INTEGER;
sql_row_cnt INTEGER;
BEGIN
sql_cur := dbms_sql.open_cursor;
OPEN get_ps;
LOOP
FETCH get_ps INTO ps;
EXIT WHEN get_ps%NOTFOUND;
dbms_output.put_line ('Killing session '||ps.sid||','||ps.serial#);
sql_cmd := 'ALTER SYSTEM KILL SESSION ' ||''''||ps.sid||','|| ps.serial#||'''';
-- dbms_output.put_line ( sql_cmd );
dbms_sql.parse ( sql_cur , sql_cmd , dbms_sql.v7 );
sql_row_cnt := dbms_sql.execute ( sql_cur );
dbms_output.put_line ( sql_cmd );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_sql.close_cursor ( sql_cur );
END;
/
To check the long ops
==================
set verify off
col opname format a40 trunc
col pctdone format 999
col mintogo format 9,999.90
accept trgtsid number default 0 prompt 'Limit to which SID : '
select s.sid,o.opname,s.sql_hash_value,o.sofar,o.totalwork,o.elapsed_seconds,
round(o.time_remaining/60,2) mintogo,
round(o.sofar/o.totalwork * 100,0) pctdone, o.message
from v$session_longops o, v$session s
where o.sid = s.sid
and sofar < totalwork
and (o.sid = &trgtsid or &trgtsid = 0)
/
or
select sid, opname, target, sofar, totalwork, units,
time_remaining, elapsed_seconds, message, username,
to_date(last_update_time,'dd-MON-yy hh:mi:ss')
from v$session_longops
where sid=&sid
/
Library Cache pin locks
================
libcachepin.sql
============
select /*+ ordered */ w1.sid waiting_session,h1.sid holding_session,w.kgllktype lock_or_pin,w.kgllkhdl
address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive','Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr;
prompt To find out the object being referred, Please run the following select script;
prompt -----------------------------------------------------------------------------;
prompt select distinct to_name from v$object_dependency where to_address = '<address>';
prompt -----------------------------------------------------------------------------;
To check the DB Links
----------------------
set lines 132
set pages 100
col owner format a10
col db_link format a15
col username format a10
col host format a50
select * from dba_db_links
/
No comments:
Post a Comment