Difference between 11i and R12 :

                       

you can find below difference in 11i and r12 architecture:

1) in 11i , we have 3 top level dir (appl/ comn /ora) where as in R12, one extra top (INST) is present which contains all configuration file  in it.
2) jserv in 11i replaced by OC4J.
3) forms and report version in 11i is 6i where as 10gAs in R12.
4) in 11i ------- 3 oracle _home (  9i rdbms , 6i O_H  and iAS O_H)
in R12 -------------- 10.1.2 (forms OH),  10.1.3 (OC4J  OH) and  10g  rdbms OH

5) mod_plsql   is not present in r12

how to know the no of concurrent request run on a particular day:------------  


select count (*) from fnd_concurrent_requests where trunc(REQUEST_DATE)='29-JUNE-16';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
in test or uat , can change all  application user password using below query:------------

new password will be ------------- oracle1234

select user_name
  into l_usr_name
  from fnd_user
  where
  employee_id = c1.person_id
  ;
 
  lb_status := FND_USER_PKG.CHANGEPASSWORD ( username => l_usr_name, newpassword => 'oracle1234' );
 
  IF lb_status THEN
    DBMS_OUTPUT.PUT_LINE('Reset Password for user = '|| l_usr_name);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error while processing the request for user = '|| l_usr_name);
  END IF;
 
  end loop;
END;
/
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

how to get generated redo size per session:-------

SELECT
s.sid,
s.username,
s.program,
ROUND(t.VALUE/(1024*1024)) AS "Redo Size MB",
sa.sql_text
FROM V$SESSION s, V$SESSTAT t, V$STATNAME sn, V$SQLAREA sa
WHERE s.sid = t.sid
AND t.statistic# = sn.statistic#
AND sn.name = 'redo size'
AND sa.sql_id = s.sql_id
AND ROUND(t.VALUE/(1024*1024)) != 0
ORDER BY t.VALUE DESC;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If want to calculate per hour redo generation :------------  


SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC

) WHERE ROWNUM < 8;

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

WORKFLOW RELATED :

to set the overrides from backend:

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER :

--Set the override address to  'xyz@abc.com' , using the following script:
--Login to the application server..
cd $FND_TOP/sql

@afsvcpup.sql

--Check the workflow services using the below query.
set pagesize 400
set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

How to get active session ------------

select sid,serial#,module,machine,last_login_time from v$session where status='ACTIVE'

TO FIND OUT LONGOPS SESSION :===

SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
 TRUNC(elapsed_seconds, 5) "ET (s)", TO_CHAR(start_time, 'HH24:MI:SS') start_time,
 ROUND((sofar/totalwork)*100, 2) "COMPLETE (%)"
 FROM v$session_longops a, v$session b
 WHERE a.sid = b.sid AND b.username not IN ('SYS', 'SYSTEM') AND totalwork > 0
 ORDER BY elapsed_seconds;

TO GET INVALID OBJECTS:

select owner,object_name,object_type from dba_objects where status='INVALID'

TO FIND OUT SESSION FROM CONCURRENT REQ ID :

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id in ('27222682','27223376');

TO FIND TABLESPACE AGAINST TABLE :

select table_name,tablespace_name from all_tables where table_name = 'EMP';

to get blocking session in oracle

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;
 
 SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 alter system kill session '5031,2459';

++++++++++++++++++++ to find session details from concurrent req +++++++++++++++

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =26916095;