AppsDBA Issuess

1. How do you compile a form command line?

appltop/apps/ora/8.0.6/bin/f60gen module=/appltop/apps/au/11.5.0/forms/US/FNDRSRUN.fmb userid=APPS/apps output_file=/appltop/apps/fnd/11.5.0/forms/US/FNDRSRUN.fmx module_type=form batch=yes compile_all=special

2. how do you generate a report command line?

/appltop/apps/ora/8.0.6/bin/rwcon60 userid=APPS/apps source=/tappltop/apps/pa/11.5.0/reports/US/PAXPCEGS.rdf dest=/tappltop/apps/admin/INTGBL/out/tmp001.rdf stype=rdffile dtype=rdffile logfile=/tappltop/apps/admin/INTGBL/out/adrep001.txt overwrite=yes batch=yes compile_all=yes

3. How do you load data to db using FNDLOAD?

/appltop/apps/fnd/11.5.0/bin/FNDLOAD &ui_apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct @PJI:patch/115/import/US/pji115fn.ldt

Patching Issues/Sollutions/WorkArounds

During Patching, If you get different AD Worker Errors:

1. AD Worker error:
The following ORACLE error:
occurred while executing the SQL statement:
GRANT select on GV$LOGFILE to em_oam_monitor_role
Error occurred in file
/appltop/apps/ad/11.5.0/patch/115/sql/ademusr.sql

Work Around:

->connect DB / as sysdba
->grant select on GV_$LOGFILE to system with grant option.
->connect system/systempwd.
->grant select on GV$LOGFILE to em_oam_monitor_role.
-> Restart the failed worker using adctrl.

2.AD Worker error:
The following ORACLE error:ORA-12801: error signaled in parallel query server P000ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys foundoccurred while executing the SQL statement:CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXXAD Worker error:Unable to compare or correct tables or indexes or keysbecause of the error above

Work Around

->Execute the following SQL to prevent errors during Patch Application through adpatch:
->SELECT TRANSACTION_ID, count(*)FROM ICX.ICX_TRANSACTIONSGROUP BY TRANSACTION_IDHAVING count(*)>1
->If the Above query returns any Row then Please execute the following SQL :

->$ICX_TOP/sql (named ICXDLTMP.sql).

1. How do you enable/disable a trace to a Oracle Application Forms Session?

solution:

1. Connection to Oracle Applications
2. Navigate to the particular form, which you want trace to be enabled
3. Goto Menu Help->Diagnostics->Trace->Regular Trace and select it
4. It will ask you for Apps Password. Provide it
5. Then it will show the file path where trace is going to be generated
6. Ask developer to perform their transactions, once they are done disable the trace
7. Goto to that location to get the trace file
8. Get the trace out put file using tkprof with different options

To disable Trace Session

Goto Menu Help-> Diagnostics->Trace->No Trace

2. How do you enable/disable a trace to a Oracle Application Forms Session? (Other Way)

Solution:

1. Get the serial #, sid of particular form session by navigating Help->about Oracle applicatins
2. Connect to database using sqlplus with relavant user
3. execute dbms_system.set_sql_trace_in_session(2122,332,TRUE);
4. Select spid from v$process where addr=(select paddr from v$session where sid=2122);
5. You will get spid like 4515 for above statement
6. Goto udump location and type ls -ltr *4515* you will get trace file

To disable Trace Session

1. execute dbms_system.set_sql_trace_in_session(2122,332,FALSE);

3. How do you enable/disable a trace to a Concurrent Program?

Solution:

1. Connect to Oracle Applications
2. Navigate to System Administrator->Concurrent->Program->Define
3. Query the concurrent program on which you want to enable trace.
4. Check the enable trace check box bottom of the screen, Save it.
5. Ask the developer to submit the request, Once the request got submitted and completed normal.
6. Get the spid as select oracle_process_id from apps.fnd_concurrent_requests where request_id=456624.
7. You will get a spid like 12340.
8. Goto Udump and ls -ltr *12344*.
9 . You will get trace file.

How to convert Forms server to Socket mode  from Servlet mode in Oracle Apps R12

Run the following command to enable Forms Socket Mode on Forms/web nodes (Place correct context file name and port value)

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -contextfile=$CONTEXT_FILE -mode=socket  -port=9030 -runautoconfig=No

Where port = forms port and contextfile=your environment contextfile

To Start/Stop/check Status of forms servers use following scripts

cd $ADMIN_SCRIPTS_HOME
./adformsrvctl.sh status/stop/start

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES

Unable to launch forms when logging to Application:

[ ]cd /d01/oracle/svrqatcomn/conf/svrqat_msvrqatebsal2/iAS/Apache/Apache/logs
cat error_log_pls.1099966600
After the Forms page timed out we received error on fnd_icx_launch.launch searching this on we reached file error_log_pls.1099966600
where in 2 port where not binding.
[oracle@msvrqatebsal2 logs]$ cat error_log_pls.1099966600
[Tue Aug 6 13:47:44 2011] [crit] (98)Address already in use: make_sock: could not bind to port 8202
[Tue Aug 6 16:26:12 2011] [crit] (98)Address already in use: make_sock: could not bind to port 8202
[Tue Aug 6 19:00:42 2011] [warn] pid file /d01/oracle/svrqatcomn/conf/svrqat_msvrqatebsal2/iAS/Apache/Apache/logs/httpd_pls.pid overwritten -- Unclean shutdown of previous Apache run
[Tue Aug 6 19:00:42 2011] [notice] FastCGI: process manager initialized (pid 5187)
[Tue Aug 6 19:00:43 2011] [notice] Oracle HTTP Server Powered by Apache/1.3.19 configured -- resuming normal operations

Solution:

1). Rebot the Particular Forms Server --> at UNIX lever
2). kill the processes like command (fi any seheduled requests are not in client like aventx)
             kill -9 -1

high consuming sql...

SQL> select s.sid,s.username,s.serial#,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=5156;(pid which is come from prstat -a command)

       SID USERNAME                          SERIAL# SPID
---------- ------------------------------ ---------- ------------
        40 APPS                                14047 5156

SQL> select SQL_HASH_VALUE,PREV_HASH_VALUE from v$session where sid=40;

SQL_HASH_VALUE PREV_HASH_VALUE
-------------- ---------------
    1529310018      1529310018

SQL> select sql_text from v$sql where HASH_VALUE='1529310018';

SQL_TEXT
--------------------------------------------------------------------------------
insert into ra_customer_trx ( customer_trx_id, last_update_date, last_updated_by
, creation_date, created_by, last_update_login, trx_number, cust_trx_type_id, tr
x_date, set_of_books_id, ct_reference, interface_header_context, interface_heade
r_attribute1, interface_header_attribute2, interface_header_attribute3, interfac
e_header_attribute4, interface_header_attribute5, interface_header_attribute6, i
nterface_header_attribute7, interface_header_attribute8, interface_header_attrib
ute9, interface_header_attribute10, interface_header_attribute11, interface_head
er_attribute12, interface_header_attribute13, interface_header_attribute14, inte
rface_header_attribute15, bill_to_contact_id, batch_id, batch_source_id, sold_to
_customer_id, bill_to_customer_id, bill_to_address_id, ship_to_customer_id, ship
_to_contact_id, ship_to_address_id, ship_to_site_use_id, term_id, previous_custo

SQL_TEXT
--------------------------------------------------------------------------------
mer_trx_id, primary_salesrep_id, purchase_order, purchase_order_revision, purcha
se_order_date, comments, internal_notes
================================================================

+++++++++++++++++++++++++++++++++++++++++++++locked objects

select 

  oracle_username

  os_user_name,

  locked_mode,

  object_name,

  object_type

from 

  v$locked_object a,dba_objects b

where 

  a.object_id = b.object_id

 +++++++++++++++++++++++++++++++++++++++++++++++++++++ select SID, SPID VALUES

select s.sid,s.username,s.serial#,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=4197;
       SID    SERIAL# SPID
---------- ---------- ------------
      1359        249 23851

select SQL_HASH_VALUE,PREV_HASH_VALUE from v$session where sid=1359
             
SQL> select sql_text from v$sql where HASH_VALUE='682811033';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT SUM (A.QUANTITY) FROM OKE_K_DELIVERABLES_VL A   WHERE A.DIRECTION =  'IN'
  AND A.PROJECT_ID =  :b1  AND A.ITEM_ID =  :b2
 select sql_text from v$sql where HASH_VALUE='682811033';
SQL_TEXT
--------------------------------------------------------------------------------
SELECT SUM (A.QUANTITY) FROM OKE_K_DELIVERABLES_VL A   WHERE A.DIRECTION =  'IN'
  AND A.PROJECT_ID =  :b1  AND A.ITEM_ID =  :b2


..............................................................................Manju

Create defaults file for adpatch defaultsfile:

Create defaults file for  adpatch defaultsfile:

adpatch defaultsfile=$APPL_TOP/admin/SUPT/defaults.txt

Using Defaults file with adpatch (Non Interactive)

How to create defaults file ?

adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt ( You can keep this txt file in any location of your choice)

Now abort autopatch section at point where it asks for patch directory by ctrl +c or ctrl+d

Now check if this file exists

Note : You have to do above steps only once in an environment to create defaults file.

How to apply patches in future ?

adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt

Now any future patches you apply non interactive using defaults file like

adpatch defaultsfile=$APPL_TOP/admin/$SID/defaults.txt logfile=654321.log patchtop=/patches/654321 driver=u654321.drv interactive=no

You will see that it will apply patch by itself using defaults file , adpatch will pick APP_TOP, ORACLE_HOME information, system password and apps password from defaults file.

This will save lot of time & you can reduce your patch timing usign defaults file

+++++++++Manjunatha+++++++++

To Find 32/64 bit processer

#getconf  LONG_BIT

#uname -a (***i686 <processor_name> i386) is a 32 bit .

scripts

ManjunathaReddy
+++++++++++++++++++++++++++++++++ Node Details +++++++++++++++++++++++++++++++

SELECT node_name,
support_cp,
support_forms,
support_web,
support_admin,
status
FROM apps.fnd_nodes
ORDER BY 2,3,4;

+++++++++++++++++++++++++++++++++++ Find URL +++++++++++++++++++++++++++++++

select name from v$database;

select home_url from apps.icx_parameters;

grep login $CONTEXT_FILE

+++++++++++++++++++++++++++++++++++ tkprof usage  +++++++++++++++++++++++++++++++

tkprof coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc coestg1_ora_14984_XXCSE_EMAIL__CR3167157.trc_tkprof sys=no explain=apps/b1ue2uth sort='(fchela,exeela,prsela)'

+++++++++++++++++++++++++++++++++++ FNDCPASS  usage +++++++++++++++++++++++++++++++

FNDCPASS apps/g00g1e 0 Y system/dv1s4seme USER sysadmin dv1admeme

+++++++++++++++++++++++++++++++++++ zip usage +++++++++++++++++++++++++++++++

zip -rl foldername.zip folder

+++++++++++++++++++++++++++++++++++ Scp usage +++++++++++++++++++++++++++++++

scp trace_aug1.zip /tmp@dev-db3, scrp trace_aug1 bgembali@dev-db3:/tmp

+++++++++++++++++++++++++++++++++++  If you give concurrent request name it will show concurrent program details

col Program format a40;
col ARGUMENT_TEXT format a30;
col Interval format a10;
col requestor format a20;
set linesize 140;
set pagesize 999;
select distinct a.request_id,a.user_concurrent_program_name "Program",a.ARGUMENT_TEXT,to_char(a.REQUESTED_START_DATE,'DD/MM/YYYY HH24:MI:SS') "Start Date" ,a.RESUBMIT_INTERVAL||' '||a.RESUBMIT_INTERVAL_UNIT_CODE "Interval",requestor FROM apps.FND_CONC_REQUESTS_FORM_V a where
a.concurrent_program_id in (select CONCURRENT_PROGRAM_ID from apps.fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME like '&1') and a.phase_code='P';

+++++++++++++++++++++++++++++++++ Get oracle process id for a request for trace +++++++++++++++++++++++++++++++

select request_id,to_char(ACTUAL_START_DATE,'DD-MM-YYYY HH24:MI:SS'),to_char(ACTUAL_COMPLETION_DATE,'DD-MM-YYYY HH24:MI:SS'),phase_code,status_code,os_process_id,oracle_process_id from apps.fnd_concurrent_requests where request_id=&req_id;

+++++++++++++++++++++++++++++++++ Kill Sessions syntax +++++++++++++++++++++++++++++++

select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='XXNTF' AND status='ACTIVE';

+++++++++++++++++++++++++++++++++ Find Which manager took care of request +++++++++++++++++

$FND_TOP/sql/@afcmcreq.sql

+++++++++++++++++++++++++++++++++ Know front end nodes from database node +++++++++++++++++

select distinct machine from gv$session where osuser='oa&sid';

+++++++++++++++++++++++++++++++++ Script Locations  ++++++++++++++++++++++++++++++++++++++

/usr/tools/oracle/scripts - Script location
/usr/tools/oracle/Standard/onCall/core - Another script location for core db
/usr/tools/oracle/Standard/onCall/11i - Script location for Apps db


setdb <sid> -- For CTS and COE instances

    eg: setdb CTSPRD
   
    cd $APPL_TOP/admin/$TWO_TASK/log
   
+++++++++++++++++++++++++++++++++ Gather Table level stats ++++++++++++++++++++++++++++++++

exec dbms_stats.gather_table_stats(OWNNAME=>'MRP',TABNAME=>'MRP_FORECAST_INTERFACE','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;

++++++++++++++++++++++++++++++++++ Screen Usage ++++++++++++++++++++++++++++++++++++++++++++

screen -S ganesh1
screen -x screen1
screen -list

++++++++++++++++++++++++++++++++++ Gather Schema level stats ++++++++++++++++++++++++++++++++
set time on;
set timing on;
select name from v$database;
exec dbms_stats.gather_schema_stats(OWNNAME=>'AR','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'JTF','ESTIMATE_PERCENT' =>20, DEGREE=>20,CASCADE=>TRUE) ;
exec dbms_stats.gather_schema_stats(OWNNAME=>'OKC','ESTIMATE_PERCENT' =>23, DEGREE=>20,CASCADE=>TRUE) ;
exit;

++++++++++++++++++++++++++++++++++ Recompile invalid objects +++++++++++++++++++++++++++++++++

exec sys.utl_recomp.recomp_parallel(32);

++++++++++++++++++++++++++++++++++ Temp File Info ++++++++++++++++++++++++++++++++++++++++++++

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" from dual;

+++++++++++++++++++++++++++++++++++ Grants to apps schema ++++++++++++++++++++++++++++++++++++

col grantee format a20
col owner format a15
col TABLE_NAME format a15
col GRANTOR format a15
col PRIVILEGE format a10
set pagesize 100

select grantee,owner, table_name, grantor,PRIVILEGE from dba_tab_privs where TABLE_NAME='RVTAB';

/usr/tools/oracle/.refresh/password_TS1KPR.sql --- Get the pwd of the schema

grant select on AQADM.RVTAB to apps_read_only;

+++++++++++++++++++++++++++++++++++++++ start oracle script usage +++++++++++++++++++++++++++++++++++++++++++++++++++++++++==

/usr/tools/oracle/Standard/script/start_oracle -s YCODV1
/usr/tools/oracle/Standard/script/stop_oracle -s TS3ECP -t 300 -m immediate

++++++++++++++++++++++++++++++++++++++++++++++ Concurrent mangers Log locatoin +++++++++++++++++++++++++++++++++++++++++++++


cd $FND_TOP/log -- concurrent program log

++++++++++++++++++++++++++++++++++++++++++++++++++++++

1. @lock_holder
2. Note process id which causes lock say 1214
3. @sid
4. Give Enter value for sid: 1214
5. NOte os process id say 2803
6. select request_id,phase_code,status_code from apps.fnd_concurrent_requests where oracle_process_id=2803;

++++++++++++++++++++++++++++++++++++++++++++++++++++++

TS1OAS - tst-db5/tst-db6

R3 Internal BPEL - oa1-itst-16/oa1-itst-17 (oaoasts1)
    sudo su - oaoasts1
    status
    stop
    status
    start
    status

R3 Internal J2EE - oa1-itst-03/oa1-itst-04 (oaj2ets1)
R3 Internal OHS - oa1-itst-05/oa1-itst-06 (oaj2ets1/oaoasts1)

++++++++++++++++++++++To create new user for dart request+++++++++++

/usr/tools/oracle/Standard/script/Create_User.ksh

++++++++++++++++++++++ Password Reset ++++++++++++++++++++++++++++++

SQL> select name from v$database;

NAME
---------
TS1BVP

SQL> select USERNAME,PASSWORD,PROFILE from dba_users where username like 'UNI%';

USERNAME PASSWORD PROFILE
------------------------------ ------------------------------
UNIVDB F84281E3BA66D865 SOX_PROFILE

SQL> select * from dba_profiles where PROFILE='SOX_PROFILE' and RESOURCE_NAME like 'PASSWORD_VERIFY_FUNCTION';

PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD
PASSWORD_DONTCHANGE_FUNCTION


SQL> alter profile default limit password_verify_function null;

Profile altered.

SQL> alter user UNIVDB identified by "univdb123";

User altered.

SQL> alter profile SOX_PROFILE limit password_verify_function PASSWORD_DONTCHANGE_FUNCTION;

Profile altered.

++++++++++++++++++++++++++ create soft link +++++++++++++++++++++++++++++++++

ln -s /usr/tools/oracle/Standard/script/free_vol.sh .

+++++++++++++++++++++++++++++ When Jiniatior crashes

C:\Program Files\Oracle\JInitiator 1.1.8.7\bin\symcjit_old.dll

++++++++++++++++++++++++++++++ To know whether trace enabled or not

select CONCURRENT_PROGRAM_ID,USER_CONCURRENT_PROGRAM_NAME from apps.fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%Cisco%RMA%and%2Tier%Report%';

select concurrent_program_id,enable_trace from apps.fnd_concurrent_programs where concurrent_program_id in (45394,45395);

+++++++++++++++++++++++++++++++ Create Index Syntax
CREATE UNIQUE INDEX "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" ON "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG" ("PROC_ID", "QUOTE_LINE_ID") PCTFREE 10 INITRANS 10 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "XXCSSD1" PARALLEL 32;

drop index "XXCSS_O"."XXCSS_QOT_LINES_ALL_STG_PK" force;

++++++++++++++++++++++++++++++++++++++++= oracle session trace (ora debug)++++++++++++++++++++

oradebug setospid 420
oradebug unlimit
oradebug EVENT 10046 trace name context forever, level 12
oradebug tracefile_name
oradebug event 10046 trace name context off

++++++++++++++++++++++++++++++++++++++++++++++++++++

Make an entry in jserv.properties under Classpath section.
Add a new liine as "wrapper.classpath=<path>".
Bounce the Apache.

++++++++++++++++++++++++++++++++++++++++++++++++++++Concurrent requests show Inactive NoManager

Get the request id from the user,
Go to SYSADMIN --> System Administrator --> Others --> View Requests screen, search for the request. Click on Tools --> Manager log to see under which manager this program is being executed. Check the status of the manager in Concurrent --> Manager --> Administer and take necessary action.

If the Manager screen does not show any entry, this means the program is not attached to any manager. The application team needs to include this program under one of the managers

+++++++++++++++++++++++++++++++++++++++++++++++++++ Analyzing a table partition ++++++++++++++++++++++++++++++++++++++++++++

exec dbms_stats.gather_table_stats(ownname=>'ctsblr_o',tabname=>'ss_incide', partname=>'ss_incide1', 'estimate_percent' =>20, degree=>20,granularity=>'partition',cascade=>true)

++++++++++++++++++++++ Temp Usage ++++++++++++++++++++++++++++++.Give DB block size as input. show parameter db_block_size

SELECT inst_id "instid",
SUBSTR(tablespace_name,1,15) "ts",
used_blocks*&bs/1048/1048/1048 "used mb",
free_blocks*&bs/1048/1048/1048 "free mb",
total_blocks*&bs/1048/1048/1048 "total mb"
FROM gv$sort_segment;

++++++++++++++++++++++++++++++++++ Tablespace Info


select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';


+++++++++++++++++++++++++++++++++++++== LDAP

select fnd_preference.eget('#INTERNAL', 'LDAP_SYNCH','EPWD','LDAP_PWD') from dual;

+++++++++++++++++++++++++++++++++++++ package locks

select inst_id,sid,serial#,status from gv$session where sid in ( select sid from gv$access where object like '%CTS_CSS_QOT_LINES_ALL%');
select inst_id,sid,serial#,status from v$session where sid in ( select sid from v$access where object='CTS_CSS_QOT_LINES_ALL');
select inst_id,session_id from gv$locked_object where object_id=3253564


+++++++++++++++++++++++++++++++++++++ R12 URL

http://wwwin-dv2coe.cisco.com:8044/OA_HTML/dare2use.jsp

++++++++++++++++++++++++++++++++++++++++ Bulk concurrent program termination

update apps.fnd_concurrent_requests set phase_code='C',status_code='T' where concurrent_program_id= and phase_code='P' and status_code='Q'

select concurrent_program_id from apps.fnd_concurrent_programs_tl where user_concurrent_program_name like

Phase_Code :

'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',

Status_Code :

'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',

++++++++++++++++++++++++++++ find command usage

find . -size +1000000000c -exec ls -l {} \;
find ./ -size +1000000000c -exec ls -l {} \; hp unix
find .  -name *.trc -mtime +2 -exec ls -ltr {} \;
find ./ -size +500000000c -exec ls -l {} \;

+++++++++++++++++++++++++++ Parallel sessions that are  running

col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pagesize 100

SELECT DECODE(px.qcinst_id, NULL, username, ' - '||LOWER(SUBSTR(s.program, LENGTH(s.program)-4,4) ) ) "Username",
DECODE(px.qcinst_id, NULL, 'QC', '(Slave)') "QC/Slave",
TO_CHAR(px.server_set) "Slave Set",
TO_CHAR(s.sid) "SID",
DECODE(px.qcinst_id, NULL, TO_CHAR(s.sid), px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
FROM v$px_session px,
v$session s
WHERE px.sid=s.sid (+)
AND px.serial#=s.serial#
ORDER BY 5, 1 DESC
/

++++++++++++++++++++++++++++++++ Disable trigger for db link

ALTER TRIGGER CISCO_DBLINKADM.DB_LINK_CHECK enable;


+++++++++++++++++++++++++++++++ checking restricted db info

SET LINESIZE 165
COLUMN "Host Name" FORMAT a20
COLUMN "Start Time" FORMAT a25
SELECT instance_number,instance_name,instance_role,host_name "Host Name",TO_CHAR(startup_time, 'DD-MON-YYYY HH24:MM:SS') "Start Time",status,logins,database_status,thread#,archiver from gv$instance;


++++++++++++++++++++++++++++++ srs services start/stop

sudo /oracle/product/crs_home/bin/crsctl stop crs
sudo /oracle/product/crs_home/bin/crsctl start crs

++++++++++++++++++++++++++++++ To check How many Redo log swithces happened on hour

select (to_char(first_time,'mm/dd')), (to_char(first_time,'HH24')), count(*)
from v$log_history
group by (to_char(first_time,'HH24'))
,(to_char(first_time,'mm/dd'))
order by 1,2 asc
/

+++++++++++++++++++++++++++++ To check you own session id
select distinct sid from v$mystat

++++++++++++++++++++++++++++++ To get SID for particular sql running

SELECT s.sid FROM   v$session s, v$sqlarea a,v$process p WHERE  s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR and  a.sql_text like '%EXTR_BE_GEO_QUAL_HIERARCHY%';

+++++++++++++++++++++++++++++++++++ DB link info
col OWNER for a15
col DB_LINK for a45
col USERNAME for a15
col HOST for a25
set linesize 120
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK like '%&DB_LINK_NAME%';

++++++++++++++++++++++++++++++++++++ To check concurrent Queue status

select CONCURRENT_QUEUE_NAME,max_processes,running_processes,decode(control_code,
'A','Activating',
'B','Activated',
'D','Deactivating',
'E','Deactivated',
'N','Target node/queue unavailable',
'O','Suspending concurrent manager',
'P','Suspended',
'Q','Resuming concurrent manager',
'R','Restarting',
'T','Terminating',
'U','Updating environment information',
'V','Verifying',
'X','Terminated')
from apps.fnd_concurrent_queues
where
MAX_PROCESSES <> RUNNING_PROCESSES or
(control_code is not null and control_code not in ('B','E'));


++++++++++++++++++++++++++++++++++++++++ How to enable write mode to form to add responsibtility

QUERY_ONLY="YES"

Goto Application

+++++++++++++++++++++++++++++++++++++++ Compile package body  in bulk

select 'alter ' || owner || '.' || object_name || ' compile body;' from dba_objects where object_type='PACKAGE BODY' and owner='XXCD';


+++++++++++++++++++++++++++++++++++++++++++ Get the high rbs consumption info:-

select KTUXEUSN,KTUXESIZ,KTUXESTA,KTUXECFL from x$ktuxe where KTUXESTA='ACTIVE' and KTUXESIZ>10000 order by KTUXESIZ;

Get Session info (Enter the ktuxeusn obtained from the previous query):-

SELECT s.inst_id,s.sid, s.serial#, s.username, s.program,t.xidusn,t.used_ublk, t.used_urec FROM gv$session s, gv$transaction t WHERE s.taddr = t.addr and xidusn in(&rbsn);


+++++++++++++++++++++++++++++++++++++++++++ FND_STATS usage

exec FND_STATS.GATHER_TABLE_STATS (ownname => '&owner', tabname => '&table_name', percent => 20 ,degree => 30 , granularity => 'ALL', cascade => TRUE);

------------AFOCPS8R.sql

++++++++++++++++++++++++++++++++++++++++++++ Parallism query example
Select /*+ parallel (a,32) */ * from dba_segments a;

+++++++++++++++++++++++++++++++++++++++++++++ Tablespace usage in percentage

select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like 'OKCD1'

++++++++++++++++++++++++++++++++++++++++++++ To check form or program from sid
select FND_USER_NAME,sid,module,process,action,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') from apps.XXV$SESSION where sid=&amp;no;

How to Analyze Performance Problems :

How to generate a trace file:
Depending on the nature of the performance problem, there are different methods to capture the raw trace file.
How to generate a trace file (forms) :
If the performance problem occurs while using Oracle forms, a trace file can be obtained using the following steps :
a. Navigate to the point in the application right before you are experiencing the problem.
b. Turn trace on by:  Help > Diagnostics > Trace > Trace with Binds and Waits
c. Duplicate the error and then stop immediately afterwards.
d. Retrieve trace file from the user_dump_dest, which can be located as follows: select value from V$PARAMETER where name like 'user%';

How to generate a trace file (HTML) :
If the performance problem occurs while using web pages, a trace file can be obtained using the following steps :
1. In the System Administrator responsibility set the system profile FND: Diagnostics to YES at user level. (Note: Enabling this profile will consume greater system resources and may impend database and applications performance. Thus tracing an event during hours of low system usage may be a consideration).

2. Login to self service using the user id for which the above profile is enabled and select the responsibility applicable to the issue being traced.

3. In a self service application page click on the global button Diagnostic.

5. Choose the option Set trace level and set it to Trace with Binds and Waits.

6. Navigate Back to Home and go to the self service page for which the issue is reproducible.

7. Reproduce the issue while ensuring that the performance problem is captured in the trace.

8. Click on the Diagnostic link and select the Set trace level option. Numbers will appear on the left side of the screen. Note down these numbers as these are the identifiers of the trace files on the server. Disable the tracing.

9. Using these identifiers the DBA/System Administrator can get all the trace files from the server directory location where the database trace are saved.

How to generate a trace file (Concurrent Request) before 11.5.10 :

If the performance problem occurs while running a concurrent program (including reports), a trace file can be obtained using one of the following methods :
Prior to 11.5.10, the following steps can be used to generate a trace file :
1. Navigate Responsibility: System Administrator > Profile > System >Query User: User submitting the Report
Profile: Initialization SQL Statement - Custom

2. Click on User column - Edit Field and enter

begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end;

3. Set Profile Option: SLA: Enable SQL Trace - Yes

4. Reproduce the issue by submitting the concurrent program

5. Use the following SQL to locate the trace file:

select value from v$parameter where name = 'user_dump_dest';


How to generate a trace file (Concurrent Request) on 11.5.10 and above :
From 11.5.10 onwards, the above method can still be used to create a trace file, however, there is an easier method to generate the trace file for concurrent programs. The following steps can be used :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Concurrent Program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.

How to create a tkprof file :
TKPROF reformats the raw data so that it is easier to review and also shows the time taken by each SQL statement and can be used to determine the expensive SQL statements. Once the trace file has been gathered, a tkprof file can be created from the raw trace using the steps below :

a. Retrieve the trace file.
b. Issue a command like the following to create a TKPROF version of the trace file. This command sorts the results with the longest running queries first:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)'

c. Additionally, the following command can be used which will create a tkprof that limits the results to the top ten queries:
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password>
sort='(prsela,exeela,fchela)' print=10

Gathering Statistics Concurrent Requests

The following recommended concurrent requests are available in Oracle Applications for gathering statistics:

Analyze All Index Columns
Backup Table Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics

Please refer to the Oracle Application System Administration Guide for concurrent request parameters. Here are common parameters and their meanings:

• Schemaname Enter the three character schema to analyze. An example for entry would be MRP or INV or ONT or ALL. You may enter ALL to analyze every defined App schema.
• Estimate_percentPercentage of rows to estimate. If left empty it will default to 10%. The valid range is 0-99. A higher percentage will be more accurate, but take longer to run. If the object(s) that you are gathering statistics for do not change often or the object(s) has data entered that is very similiar you may choose a lower number. However, if the data changes frequently a larger number entered for this parameter would be recommended to provide a more accurate representation of your data.
• DegreeEnter the Degree of parallelism. If not entered, it will default to min(cpu_count, parallel_max_servers). Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make Full Table Scans look less attractive.
• Internal FlagIf the value is 'NOBACKUP' then it won't take a backup of the current statistics and should run quicker. If the value is
  'BACKUP' then it does an export_table_stats prior to gathering the statistics.
• Restart Request IdEnter the request id that should be used for recovering gather_schema_stats if this request should fail. You may leave
  this parameter null.

Summary of FND_STATS Subprograms

CREATE_STAT_TABLE
This creates the table where the statistics can be backed up. This table is created in the AOL (APPLSYS)schema and is called FND_STATTAB.

BACKUP_TABLE_STATS
Backups the statistics of the given table into FND_STATTAB. Statistics can be backed up with different statid . The default statid is 'BACKUP'. It is possible to keep different versions of the backup as different statid's. This also backs up the related index stats by default.

Concurrent Request equivalent is "Backup Table Statistics".

BACKUP_SCHEMA_STATS
Backs up the statistics of all the objects of the given schema into FND_STATTAB. Statistics can be backed up with different statid. The default statid is NULL. It is possible to keep different versions of the backup as different statids.

RESTORE_SCHEMA_STATS
Restore the previously backed up schema statistics.

RESTORE_TABLE_STATS
Restores the previously backed up table statistics from a given statid.
The default statid is 'BACKUP'.

Concurrent request equivalent is "Restore Table Statistics".

RESTORE_TABLE_STATS
This procedure retrieves statistics for a particular table from the FND_STATTAB for the given statid(optional)and stores them in the dictionary. Cascade results in all index and column stats associated with the specified table being imported as well.

GATHER_SCHEMA_STATS
This procedure gathers statistics for all objects in a schema. If schema name is specified as 'ALL' then all the Apps specific schema (having an entry in FND_PRODUCT_INSTALLATIONS table) statistics are gathered. Before gathering the
statistics, this also takes a backup of the existing statistics so that in case the database slows down after gathering statistics, things can be restored to its previous status. The statid used for this backup is 'NULL'. Also after gathering the schema level statistics this procedure creates the histogram for the specified columns in the FND_HISTOGRAM_COLS tables. And lastly it populates a default statistics for all the INTERFACE tables as specified in the FND_EXLCUDE_TABLE_STATS table.

If the procedure fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id can be captured when the procedure is started from concurrent request manager.

Concurrent request equivalent is "Gather Schema Statistics".

GATHER_SCHEMA_STATISTICS
This is a simpler call for using GATHER_SCHEMA_STATS from the SQL prompt. This should be used for gathering schema statistics for a single schema only. Schema name should rarely be 'ALL'. Downside to using 'ALL' is that you will
not know if there was indeed any error. If it fails at any time during operation, it can be restarted by supplying the request id of the run that failed. The request id. To identify the request id for the last run select the maximum value for request_id from table FND_STATS_HIST.

This internally calls GATHER_SCHEMA_STATS. The only difference here is that this procedure doesn't have a output parameter and hence is easier to call from the SQL prompt.

GATHER_INDEX_STATS
This procedure gathers index statistics. It is equivalent to running ANALYZE INDEX[ownname.]indname [PARTITION partname] COMPUTE STATISTICS | ESTIMATE STATISTICS SAMPLE estimate_percent PERCENT.

It does not execute in parallel.

If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB . If the value of backup_flag is anything other than 'BACKUP' export_table_stats
is not performed.


GATHER_TABLE_STATS
This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible. This operation does not parallelize if the user does not have select privilege on the table being analyzed. If the value of backup_flag is 'BACKUP' then it does an export_table_stats before gathering the stats. The exported data is stored in FND_STATTAB. If the value of backup_flag is anything other than 'BACKUP' export_table_stats is not performed.


ANALYZE_ALL_COLUMNS
This procedure analyzes all the indexed columns for all the tables in a given schema.

Concurrent request equivalent is "Analyze All Index Columns".

LOAD_XCLUD_STATS
This procedure loads the default stats as specified in the SEED data table FND_EXCLUDE_TABLE_STATS. There are two versions of this procedure. One loads for all the tables for a particular schema and the other one loads for a given table in a given schema.

LOAD_XCLUD_TAB
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for interface tables.

LOAD_HISTOGRAM_COLS
This procedure is for Oracle internal purpose only and should not be used by the customers. This is used for SEED data manipulation for histogram columns.

CHECK_HISTOGRAM_COLS
This gives a report on which indexed columns for a list of given tables is a good candidate for histograms. For a given list of comma separated tables, this procedure checks the data in all the leading columns of all the non-unique indexes of those tables and figures out if histograms needs to be created for those columns. A count(*) of at least 3000 rows in the table(s) is
recommended. The procedure needs to be run from the SQL prompt after setting the serveroutput on.

VERIFY_STATS
This gives a report on the stats of the given list of objects.
For a given list of comma separated tables, or for the given schema name, this procedure reports the stats in the data-dictionary tables for the tables, indexes and the histograms. Run FND_STATS.VERIFY_STATS at any time to check
when statistics were last gathered. 

Apps DBA useful scripts :

Apps DBA Scripts

To know instance full detalis

 SQL> col instance_name format a13

       col host_name format a15

       col version format a9

       col release format a9

      select i.instance_name, i.host_name, f.release_name release, i.version

         from v$instance i, apps.fnd_product_groups f

          where i.instance_name = f.applications_system_name;

 

To know entries in the database :

 

SQL> select node_name "Node Name", node_mode "Mode", support_cp "C",support_web "W", support_admin "A", support_forms "F" from FND_NODES;

 

1. Provide Concurrent Program Name, It will list out all concurrent requests sets names that has concurrent program in it?

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

3. Find out Inactive Form sessions for X hours?

set lines 200 pages 200
col event for a30

select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs, 
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
       a.process, b.spid
  from v$session a, v$process b
 where a.action like 'FRM%'
   and a.paddr = b.addr
   and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;

4. Find out Application Names (Products) in Oracle Apps?

set lines 200

set pagesize 300

col APPLICATION_NAME for a70

select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from apps.fnd_application b, apps.fnd_application_tl a  where a.APPLICATION_ID=b.APPLICATION_ID; 

5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?

set lines 200 pages 300

col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50

break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;

6. Find out session details  of a concurrent Request?

set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/

7. Find out All Concurrent Queue/Manager sizes/Processes?

set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;

8. To know the SID ,Sireal# to find OS level :

SQL> SELECT ses.sid, 

            ses.serial# 

       FROM v$session ses, 

            v$process pro 

           WHERE ses.paddr = pro.addr 

                AND pro.spid IN (SELECT oracle_process_id 

                                           FROM fnd_concurrent_requests 

                                        WHERE request_id = &request_id);

To Know the SPID :

select

sid,

spid

from v$process p, v$session s

where paddr = addr

and s.sid in(&SID)

order by s.sid;

9. To know CONCURRENTMANAGER completed

SELECT fcr.REQUEST_ID,fc.USER_CONCURRENT_PROGRAM_NAME,TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI:SS') actual_start_date,TO_CHAR(fcr.actual_completion_date,'DD-MON HH24:MI:SS') actual_completion_date,fcr.STATUS_CODE

FROM FND_CONCURRENT_REQUESTS fcr,

                FND_CONCURRENT_PROGRAMS_TL fc

WHERE fcr.phase_code='C'

                AND fc.concurrent_program_id=fcr.concurrent_program_id

                AND fc.USER_CONCURRENT_PROGRAM_NAME='&CONC_req_name' order by fcr.actual_start_date ;

10. To find the start & end date of Conc – Req info :

SELECT qt.user_concurrent_queue_name

, fcr.Request_Id Request_id

, fu.User_name

, p.spid

, s.sid ||','|| s.serial# SIDSERIAL

, substr( Fcpv.Concurrent_Program_Name ||'-'|| Fcpv.User_Concurrent_Program_Name, 1,46) Program

, to_char( fcr.actual_start_date, 'mm/dd hh24:mi') actual_start_date

, phase_code, status_code

, to_char( trunc(sysdate) + ( sysdate - fcr.actual_start_date )

, 'hh24:mi:ss' ) duration

FROM apps.Fnd_Concurrent_Queues Fcq

, apps.fnd_concurrent_queues_tl qt

, apps.Fnd_Concurrent_Requests Fcr

, apps.Fnd_Concurrent_Programs Fcp

, apps.Fnd_User Fu

, apps.Fnd_Concurrent_Processes Fpro

, v$session s

, v$process p

, apps.Fnd_Concurrent_Programs_Vl Fcpv

WHERE phase_code = 'C'

AND status_Code = 'X'

AND s.paddr = p.addr

AND fcr.requested_by = user_id

AND fcq.application_id = qt.application_id

AND fcq.concurrent_queue_id = qt.concurrent_queue_id

AND userenv('lang') = qt.language

AND fcr.os_process_id = s.process

AND fcr.Controlling_Manager = Concurrent_Process_Id

AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id

AND fcq.application_id = fpro.queue_application_id )

AND (fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id )

AND (fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id )

ORDER BY fcr.actual_start_date;

11. Req running Duration...

    *********************

   

    set linesize 152

              col USER_CONCURRENT_PROGRAM_NAME for a30

              col status format a5

              col phase  format a5

              col COMPL_DATE FOR A10

              col START_DATE FOR A10

              col user_name format a20

              col hrs_running format 999.999

               select

                 '*'||a.status_code ||'*' st,

                 '#'||a.phase_code||'#' ph,

                 a.USER_CONCURRENT_PROGRAM_NAME,

                 b.user_name,

                 to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,

                 to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE,

                 (sysdate-actual_start_date)*24 Hrs_Running

               from

                 apps.fnd_conc_req_summary_v a,

                 apps.fnd_user  b

               where

                 --phase_code='C'and

                 a.REQUESTED_BY=b.user_id and

                 a.REQUEST_ID =&REQID

             order by b.user_name asc,a.ACTUAL_START_DATE asc ;

12. Long Running Concurrent Manager Requests

select  'ID='|| to_char(r.request_id) job_id, r.status_code,decode( r.description,null, '* ' ||

p.user_concurrent_program_name,r.description ) description,

       f.user_name requested_by,

       to_char( trunc(sysdate) + ( sysdate - r.actual_start_date ), 'hh24:mi:ss' ) duration

       ,to_char( r.actual_start_date,'mm/dd hh24:mi:ss' ) actual_start_date,

        r.argument_text

  from apps.fnd_concurrent_programs_tl p,

       apps.fnd_oracle_userid u,

       apps.fnd_user f,

       apps.fnd_concurrent_requests r

 where r.oracle_id = u.oracle_id

   and r.requested_by = f.user_id

   and r.program_application_id = p.application_id

   and r.concurrent_program_id = p.concurrent_program_id

   and p.language = 'US'

   and r.phase_code = 'R' and r.status_code='R'

      and decode( r.description,null, p.user_concurrent_program_name,r.description ) not in ('Adapter Startup','Gather Schema

Statistics','Planning Manager','DRWIP Discrete Job Pick List Report')

      and (sysdate - r.actual_start_date) * 1440 > &how_long

   order by r.request_id

/

13. To find Conc - Reqs particular date Information on particular date :

set verify off

set feedback off

set pagesize 59

set linesize 180

set recsep off

compute sum of cnt on actual_start_date;

compute sum of elapsed on actual_start_date;

compute sum of average on actual_start_date;

compute sum of waited on actual_start_date;

compute sum of avewait on actual_start_date;

column elapsed format 99999.99 heading 'TOTAL|HOURS';

column average format 9999.99 heading 'AVG.|HOURS';

column waited format 99999999.99 heading 'WAITED|HOURS';

column avewait format 9999.99 heading 'AVG.|WAIT';

column cnt format 999,999 heading 'COUNT';

set feedback off

select    q.concurrent_queue_name,

                count(*) cnt,

                sum(r.actual_completion_date - r.actual_start_date) * 24 elapsed,

                avg(r.actual_completion_date - r.actual_start_date) * 24 average,

                sum(r.actual_start_date - r.requested_start_date) * 24 waited,

                avg(r.actual_start_date - r.requested_start_date) * 24 avewait

from      apps.fnd_concurrent_programs p,

                apps.fnd_concurrent_requests r,

                apps.fnd_concurrent_queues q,

                apps.fnd_concurrent_processes pr        

where   r.program_application_id = p.application_id and

                r.concurrent_program_id = p.concurrent_program_id and

                r.status_code in ('C','G') and

                r.controlling_manager=pr.concurrent_process_id and

                q.concurrent_queue_id=pr.concurrent_queue_id and

                r.concurrent_program_id=p.concurrent_program_id and

        actual_start_date between '04-NOV-2012' and '07-NOV-2012'

group by q.concurrent_queue_name;

set feedback on

***********************************

SET PAGES 3000 LINESIZE 400 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF tab off

col Concurrent_Queue_Name for a9

col User_name for a18

col USER_CONCURRENT_PROGRAM_NAME for a55

col Concurrent_Program_Name for a10

Select Concurrent_Queue_Name Manager,

Request_Id Request, User_name,

fcpt.USER_CONCURRENT_PROGRAM_NAME,

Concurrent_Program_Name Program,

To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI:SS') Started,

To_Char(Actual_Completion_Date, 'DD-MON-YY HH24:MI:SS') Completed,

round(to_number(to_char(Actual_Completion_Date,'SSSSS')-to_number(to_char(Actual_Start_Date,'SSSSS')))) time_sec

from Fnd_Concurrent_Queues Fcq, Fnd_Concurrent_Requests Fcr,

Fnd_Concurrent_Programs Fcp, Fnd_User Fu, Fnd_Concurrent_Processes

Fpro,FND_CONCURRENT_PROGRAMS_TL fcpt

where

Phase_Code in ('C','R','P') And

Fcr.Controlling_Manager = Concurrent_Process_Id       And

Fcr.REQUEST_DATE between to_date('28-JAN-2012 00:00:00', 'DD-MON-YY HH24:MI:SS') and to_date('28-JAN-2012

23:59:00','DD-MON-YY HH24:MI:SS') and

(Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    And

Fcq.Application_Id      = Fpro.Queue_Application_Id ) And

(Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And

Fcr.Program_Application_Id = Fcp.Application_Id )     And

Fcr.Requested_By = User_Id                            And

fcp.APPLICATION_ID = fcpt.APPLICATION_ID and

fcp.CONCURRENT_PROGRAM_ID = fcpt.CONCURRENT_PROGRAM_ID and

fcpt.LANGUAGE = userenv('LANG')

order  by time_sec desc;

+++++++++++++++++

Conc-Req – Long running:

SELECT   fcr.phase_code,

         DECODE (fcr.phase_code,'C', 'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code) phase,

         fcr.status_code,

         DECODE (fcr.status_code,'A', 'Waiting',

                                'B', 'Resuming',

                                'C', 'Normal',

                                 'D', 'Cancelled',

                                'E', 'Error',

                                'F', 'Scheduled',

                                'G', 'Warning',

                                'H', 'On Hold',

                                 'I', 'Normal',

                                'M', 'No Manager',

                                'Q', 'Standby',

                                'R', 'Normal',

                                 'S', 'Suspended',

                                'T', 'Terminating',

                                'U', 'Disabled',

                                'W', 'Paused',

                                'X', 'Terminated',

                                 'Z', 'Waiting',

                                fcr.status_code) status,

         request_date,

          frt.responsibility_name,fu.user_name,

          fcpt.user_concurrent_program_name,

          fcr.request_id,fcr.request_date,fcr.requested_start_date,

          fcr.ACTUAL_START_DATE,fcr.ACTUAL_COMPLETION_DATE

   FROM fnd_concurrent_requests fcr,

         fnd_user fu,

         fnd_responsibility_tl frt,

          fnd_application_tl fat,

         fnd_concurrent_programs_tl fcpt

   WHERE (fu.user_id = fcr.requested_by)

     AND (fat.application_id = fcr.program_application_id)

     AND (fcr.concurrent_program_id = fcpt.concurrent_program_id)

     AND (fcr.responsibility_id = frt.responsibility_id)

     AND fat.LANGUAGE = 'US'

     AND frt.LANGUAGE = 'US'

      AND fcpt.LANGUAGE = 'US'

      and fcr.phase_code='C'

      and status_code='C'

      and fcr.request_date > sysdate-3

    ORDER BY fcr.request_date DESC;

++++++++++++++++++++

14. To find the conc-req id detaisl with trace details ...

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 80 pages 22 head off

select

    'Request id: '||request_id ,

    'Trace   id: '||oracle_Process_id,

    'Trace Flag: '||req.enable_trace,

    'Trace  Dir:  Go to directory '||dest.value,

    'Search String: ls -al *'||oracle_process_id||'*',

    '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 apps.fnd_concurrent_requests req, v$session ses, v$process proc,

     v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,

     apps.fnd_executables execname

where  req.request_id = &request_id

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;

EX - Output:

Request id: 22849336

Trace   id: 1063                           Trace Flag: N

Trace  Dir:  Go to directory /d01/oracle/uatdb/11.2.0.3/admin/UAT_uatsdl1/diag/rdbms/uqat/UAT/trace

Search String: ls -al *1063*

Prog. Name: PRC: Update Project Performance Data

File  Name: PJI_PJP_SUM_MAIN.SUMMARIZE

Status    : Running-Normal

SID Serial: 8787,55063

Module    : PJI_PJP_SUMMARIZE_INCR

TKPROF command :

tkprof aps_ora_22822.trc aps_ora_22822.txt sys=no explain=apps/apps sort='(prsela,exeela,fchela)'print=20