Orace DBA scripts: wait event scripts
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 3600 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0
--session statistics for a particular session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
and s.username='&USERNAME'
order by s.sid,se.value desc
If non DBA user want to see its own statistics then it should grants as below:
grant select on sys.V_$SESSION to username;
grant select on sys.V_$STATNAME to username;
grant select on sys.V_$MYSTAT to username;
and replace view v$sesstat by v$mystat
another useful grants :
grant select on sys.V_$PROCESS to username;
grant select on sys.DBA_2PC_PENDING to username;
grant select on sys.v_$SQLTEXT to username;
grant select on sys.v_$SQL to username;
++++++++++++
Performance issue find highest resource consumption
Session with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as
"CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
Sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
Sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as
"DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "%
CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v
$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
++++++++++++
O CHECK FOR TABLE LOCKS
set pagesize 400
set linesize 600
col USERNAME for a15
col OS_USER_NAME for a15
col TERMINAL for a15
col OBJECT_NAME for a30
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c where a.sid = c.session_id and b.object_id = c.object_id;
Steps for releasing lock on a table:
Finding Locks
select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
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
Finding Blocking sessions :
select l1.sid, ' IS BLOCKING ', l2.sid
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 s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
Sessions with highest CPU consumption :
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
Sessions with the highest time for a certain wait :
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
Sessions with highest DB Time usage :
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
Step1:To verify the lock object Here is the import query:
---------------------------------------------------------------
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Step 2: Find the serial# for the sessions holding the lock:
SQL> select SERIAL# from v$session where SID=667;
SERIAL#
----------
21091
SQL> alter system kill session '667,21091';
System altered.
How to release a lock in Oracle
This mostly comes from two other sources Killing Oracle Session and What’s blocking my lock?
This involves the system tables v$lock and v$session, and using the ‘ALTER SYSTEM’ statement. This can all be done via sqlplus as the system user (you don’t need to be sysdba).
First, determine who’s holding the lock:
select
s1.username || '@' || s1.machine
|| ' ( SID,S#=' || s1.sid || ',' || s1.serial# || ' ) is blocking '
|| s2.username || '@' || s2.machine
|| ' ( SID,S#=' || s2.sid || ',' || s2.serial# || ' )'
AS blocking_status
from
v$lock l1,
v$session s1,
v$lock l2,
v$session s2
where
s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
The result set will look like this:
BLOCKING_STATUS
--------------------------------------------------------------------------------
BEN@INTWAREPOD2145 ( SID,S#=134,11102 ) is blocking BEN@INTWAREPOD2145 ( SID,S#=128,30076 )
Now that we know which session is doing the blocking, (134,11102) in this case, we can kill it.
SQL> alter system kill session '134,11102';
Note, the alter system statement is powerful stuff, so take care with it.
=++++++
Database Important Scripts
Hit Ratios
select Round(100*(cg.value+db.value-pr.value)/(cg.value+db.value),2) "Buffer Hit Ratio"
from v$sysstat db, v$sysstat cg, v$sysstat pr
where db.name = 'db block gets'
and cg.name = 'consistent gets'
and pr.name = 'physical reads'
/
Check the active process
ps -fu applmgr
Kill the Active process
kill -9 `ps -u applmgr -o "pid="`
Clear the whole content and remove all lines inside the file then :
Code:
cat /dev/null > your_file
Total TBS Size
SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc
/
Free Space Size
select tablespace_name, bytes/1024/1024 from dba_free_space
/
Last Analyzed
select max(last_analyzed) from dba_tables
/
TEMP TBS Size
select file_name, sum(bytes)/(1024*1024) from dba_temp_files
group by file_name
/
select tablespace_name, sum(bytes)/(1024*1024) TEMPSIZE from dba_temp_files
group by tablespace_name
/
Script: Listing Memory Used By All Sessions
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/
SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts
select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size'
/
select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/
This script lists all jobs that are currently running in the local database.
select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job
/
Lists all jobs that have been submitted to run in the local database job queue.
select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs
/
Compile
for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW') AND STATUS ='INVALID';
spool off;
for running the script
@c:\dba\compile.sql
Section – A
This scripts is used to take a index rebuild
The following tables we have to recreate it, (FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
1) This scripts is used to take a index rebuild on following tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
(spool the following output)
select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE
a.TABLE_NAME in('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'NO'
union
select 'ALTER INDEX ' a.OWNER '.' b.INDEX_NAME ' REBUILD PARTITION 'b.partition_name' NOLOGGING;'
from dba_indexes a,dba_ind_partitions b
where a.indeX_name = b.index_name
and a.TABLE_NAME in ('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'YES'
2) ALTER TABLE APPLSYS.FND_CONCURRENT_REQUESTS move;
3) ALTER TABLE APPLSYS.FND_FILE_TEMP move;
3) run the index rebuild outuput scripts.
4) run the gather table statistics for mentioned tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
TABLE
select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'TABLE'
and (extents +1) >= max_extents;
ALTER TABLE .table STORAGE ( MAXEXTENTS x);
where x is greater than max_extents and lesser than unlimited
(2147483645);
ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);
INDEX
select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;
ALTER INDEX .index STORAGE ( MAXEXTENTS integer);
ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);
Section B
The following script's output is used to run a index rebuild on weekly basis
select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE OWNER NOT IN ('SYS','SYSTEM')
AND a.INDEX_TYPE='NORMAL'
AND NOT EXISTS (SELECT INDEX_NAME FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND INDEX_NAME = a.INDEX_NAME)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME from dba_tables c
where TEMPORARY='Y'
and a.table_name = c.table_name)
ORDER BY OWNER,INDEX_NAME;
/
for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW') AND STATUS ='INVALID';
spool off;
for running the script
select 'alter ' 'PACKAGE ' OBJECT_NAME ' compile body' ';' from dba_objects where object_type in ('PACKAGE BODY') AND STATUS ='INVALID';
/
spool runts.sql
select 'alter database datafile '''file_name''''' autoextend on;' from dba_data_files;
/
@runts
The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
Putting it all together into a single query:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
/
SESSION_WAITS
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
/
To select the username and the process status
select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224
selecta.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224
To select the username,process,status,Terminal name using SID
select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
/
To see the currently updated archive log files
SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;
To find the BDUMP,UDUMP directory
select value from v$parameter where name = 'background_dump_dest'
select value from v$parameter where name = 'user_dump_dest'
select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')
/
Identify the user and session ID for a UNIX process
This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU. It can also be used to help find inefficient queries. This query is placed inside of a script that I pass the UNIX process ID to.
select s.sid, s.username, s.osuser, s.serial#
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &1;
/
Tracing an Oracle session by SID
This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.
prompt Showing running sql statements ...........................
select addr from v$process where spid='8419'
select * from v$session where PADDR='00000003B29F9388'
select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;
prompt Showing what sql statement is doing.....................
select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
/
Check all active processes, the latest SQL, and the SQL hit ratio
select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
Monitoring Oracle processes
select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;
/
Displays concurrent requests that have run times longer than one hour (3600 seconds)
SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc
/
This script will map concurrent manager process information about current concurrent managers.
SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id
Show currently running concurrent requests
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
/
To find the CPU consumption
select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid
/
Script to show problem tablespaces
SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc
Oracle space monitoring scripts table space wise
This scripts gives warning indicator for all tablespaces that have less then 90% free space in them (with an asterisk in the last column).
select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;
Oracle space monitoring scripts (grand total table space)
select
sum(tot.bytes/(1024 *1024))"Total size",
sum(tot.bytes/(1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024)) Used,
sum(sum(nvl(fre.bytes,0))/(1024*1024)) Free,
sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
/
What's holding up the system?
Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:
Select
p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text
From
v$session s,
v$sqltext t,
v$process p
Where
s.sql_address = t.address and
s.paddr = p.addr and
s.sql_hash_value = t.hash_value and
s.sid > 7 and
s.audsid != userenv ('SESSIONID')
Order By s.status,s.sid, s.osuser, s.process, t.piece ;
/
Script to display status of all the Concurrent Managers
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
Get current SQL from SGA
select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;
You can find the SID from V$session.
What SQL is running and who is running it?
select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;
---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid
TO FIND THE SORTING DETAILS
SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;
/
Long running SQL statements
SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
s.disk_reads, t.sql_text,s.module, s.ACTION
FROM v$sql /*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ((buffer_gets > 10000000) or
(disk_reads > 1000000) or
(executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/
Move a table from one tablespace to another
There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.
The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.
Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.
connect as sys
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT SYSTEM
Elapsed: 00:00:00.50
You want to move DEPT table from system to say test tablespace.
SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;
Table altered.
SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT TEST
/
To compile the procedure
Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile
To compile the form
F60gen userid=apps/metroapps@dev module=
.fmb
output_file=/forms/US/form name.fmx
module_type=form batch=no compile_all=special
/
Run this sql statement to get package version :
select text from user_source where name='&package_name'
and text like '%$Header%';
prompt asks you the package name, in return it gives you two lines
corresponding to specifications and body creation files
You can also get pls version on database by running:
select name , text
from dba_source
where text like '%.pls%'
and line <>views
Sometimes version information is available in view definition.
Try the following sql statement :
col TEXT for a40 head "TEXT"
select VIEW_NAME, TEXT
from USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';
workflow
Run wfver.sql (see §5.e) to get version of workflow packages and views.
Finding active and inactive sessions :
set linesize 132
col started format a15
col osuser format a10
col username format a13
col sid format 99999
select d.physical_reads, p.spid,a.sid, a.serial#, a.username, a.osuser,
TO_CHAR(a.logon_time, 'MM/DD HH24:MI') started, a.sql_hash_value,status
from sys.v_$session a, sys.v_$process p, sys.v_$sess_io d
where a.sid = d.sid and a.paddr = p.addr and a.type <> 'BACKGROUND'
and a.status = 'INACTIVE'
order by a.username,a.logon_time
Select 'alter system kill session '''sid','serial#''';' from
V$session where status='INACTIVE';
select p.spid,s.status,s.username,s.machine,s.sid,s.serial#,s.program,
s.osuser,s.sql_address from v$process p,v$session s
where s.paddr = p.addr and s.sid in(&sid)
Finding Locks
select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
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
Finding Blocking sessions :
select l1.sid, ' IS BLOCKING ', l2.sid
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 s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
db file scattered read
Systemwide Waits:
If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).
It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:
SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;
One can also look at:
Statements with high DISK_READS in Sessions with high table scans blocks gotten in
db file sequential read
Systemwide Waits:IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO" and "File IO" sections of the ESTAT or STATSPACK reports to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at .
It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:
SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2;
One can also look at:
Statements with high DISK_READS in Sessions with high "physical reads" in
Undo Tablespace Check
set linesize 150
col username format a18
col sid format 99999
col object_name format a18
select s.username,s.sid,rn.name,rs.rssize/1024/1024 "UsedSize",rs.status,t.used_ublk,t.used_urec,do.object_name
from V$TRANSACTION t,V$SESSION s,V$ROLLNAME rn,V$ROLLSTAT rs,V$LOCKED_OBJECT lo,DBA_OBJECTS do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id;
Temp Tablespace Check
prompt
prompt +----------------------------------------------------+
prompt TEMP TABLESPACE USAGE BY SESSION
prompt +----------------------------------------------------+
--Temp TS usage by each session:
select b.tablespace
,a.sid,
sum(round(((b.blocks*p.value)/1024/1024),2)) size_mb
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr and
a.paddr=c.addr
group by b.tablespace,a.sid
order by sum(round(((b.blocks*p.value)/1024/1024),2)) desc
Extract the DDL Scripts for the existing database links:
SELECT
'create 'DECODE(U.NAME,'PUBLIC','public ')'database link 'CHR(10)
DECODE(U.NAME,'PUBLIC',Null, U.NAME'.') L.NAMEchr(10)
'connect to ' L.USERID ' identified by '''
L.PASSWORD''' using ''' L.host ''''
chr(10)';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
Please change the oracle directories appropriately:
select 'create or replace directory 'OWNER'.'DIRECTORY_NAME ' as ' ''''DIRECTORY_PATH''''';' from DBA_DIRECTORIES;
How to check database bit like 32bit or 64bit
SELECT distinct('This is a ' (length(addr)*4) '-bit database') "WordSize" FROM v$process;
select PLATFORM_ID, PLATFORM_NAME from v$database;
Check the PROFILE OPTIONS VALUE
SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
‘10001′, ‘SITE’,
‘10002′, ‘APP’,
‘10003′, ‘RESP’,
‘10005′, ‘SERVER’,
‘10006′, ‘ORG’,
‘10004′, ‘USER’, ‘???’) "LEV",
decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
‘???’) "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE ‘%&&profile%’
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
‘10001′, ”,
‘10002′, app.application_short_name,
‘10003′, rsp.responsibility_key,
‘10005′, svr.node_name,
‘10006′, org.name,
‘10004′, usr.user_name,
ORDER BY "NAME", pov.level_id, "VALUE";
***************
SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE 'ICX_FORMS_LAUNCHER'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
*********
SELECT po.profile_option_name "NAME",
po.USER_PROFILE_OPTION_NAME,
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM FND_PROFILE_OPTIONS_VL po,
FND_PROFILE_OPTION_VALUES pov,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE po.profile_option_name LIKE '%&&profile%'
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
AND decode(to_char(pov.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') LIKE '%&&username%'
ORDER BY "NAME", pov.level_id, "VALUE";
Find Concurrent manager for a particular concurrent request
select
c.user_name,
a.Request_id,
to_char(a.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start",
decode(a.status_code,
'A','Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Error',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'P', 'Scheduled',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting') status_code,
b.user_concurrent_queue_name' - 'b.target_node "queue_name",
a.user_concurrent_program_name
from fnd_concurrent_worker_requests a,
fnd_concurrent_queues_vl b,
fnd_user c
where a.concurrent_queue_id=b.concurrent_queue_id
and a.phase_code='R'
and a.requested_by=c.user_id
and c.user_name='RMANI'
Order by 5;
