To Know Running Concurrent Requests

**************************************************ManjunathaReddy
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
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
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id         
                        and b.sid=g.sid
                                                and a.status_code='R'
                                                and a.phase_code ='R';
********************************************************ManjunathaReddy

To Change Schemas password & Change Site Name

sqlplus apps/apps
    set pages 0
    set lines 80
    spool schema_pwd.ksh
select 'FNDCPASS apps/<appspwd> 0 Y system/<systempwd> ORACLE ' ||
           replace(ORACLE_USERNAME,'$','\$') || ' ' || oracle_username
"CMD"
      from APPLSYS.FND_ORACLE_USERID
    where  READ_ONLY_FLAG = 'A'
      and  ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS)
/
    spool off
    exit
***********************************************************
Change Site Name:
a).To find the SiteName:
select V.PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES V
WHERE V.PROFILE_OPTION_ID = (SELECT O.PROFILE_OPTION_ID FROM
FND_PROFILE_OPTIONS_VL O WHERE O.PROFILE_OPTION_ID=V.PROFILE_OPTION_ID
AND V.APPLICATION_ID=O.APPLICATION_ID AND
O.PROFILE_OPTION_NAME='SITENAME' )

b).To Update the SiteName:

UPDATE FND_PROFILE_OPTION_VALUES V
set V.PROFILE_OPTION_VALUE='SUPT - Support Instance - Cloned from
&backup_date Production Backup'
WHERE V.PROFILE_OPTION_ID = (SELECT O.PROFILE_OPTION_ID FROM
FND_PROFILE_OPTIONS_VL O WHERE O.PROFILE_OPTION_ID=V.PROFILE_OPTION_ID
AND V.APPLICATION_ID=O.APPLICATION_ID AND
O.PROFILE_OPTION_NAME='SITENAME' )
/
commit;
/
********************************************************************

which sql is running on background

SELECT a.username, b.sql_text FROM v$session a, v$sqlarea b, v$process c
WHERE c.spid = '&PID' AND a.paddr = c.addr AND a.sql_address = b.address;

To Find Top List Segments Usage...

Total list
========
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc;

Top-20 list
=========
select * from (
select SEGMENT_NAME "Table Name",sum(BLOCKS)  "Total blocks" ,
sum(bytes/1024/1024) "Size in MB" from dba_segments
where segment_name like 'MLOG$%'
group by segment_name
 order by 2 desc)
where rownum < 20

TOP SQLs & TableSpace Free

TOP sqls :

set linesize 1000

set pagesize 1000

col sql for a50

SELECT sql,buffer_gets, executions

FROM (SELECT hash_value,address,substr(sql_text,1,40) sql,

            buffer_gets, executions, buffer_gets/executions "Gets/Exec"

      FROM V$SQLAREA

      WHERE buffer_gets > 100000 AND executions > 10

      ORDER BY buffer_gets DESC)

      WHERE rownum <= 5

/

Tablespace Free and Fragmentation [ID 1019709.6]

select
  total.tablespace_name                       tsname,
  count(free.bytes)                           nfrags,
  nvl(max(free.bytes)/1024,0)                 mxfrag,
  total.bytes/1024                            totsiz,
  nvl(sum(free.bytes)/1024,0)                 avasiz,
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd
from
  dba_data_files  total,
  dba_free_space  free
where
  total.tablespace_name = free.tablespace_name(+)
  and total.file_id=free.file_id(+)
group by
  total.tablespace_name,
  total.bytes
/

set linesize 1000

set pagesize 1000