1) ap (active_plan):
select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',&child))
/
2) topsql:
col type for a10
col usernm for a10
select
(select username from dba_users where user_id = a.user_id) usernm,
sql_id,
plan_hash,
type,
cpu,
wait,
io,
total
from (
select
ash.inst_id, ash.user_id, ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
sum(decode(ash.session_state,'ON CPU',1,0)) cpu,
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) wait ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) io ,
sum(decode(ash.session_state,'ON CPU',1,1)) total
from gv$active_session_history ash,
audit_actions aud
where SQL_ID is not NULL
and ash.sql_opcode=aud.action
and ash.sample_time > sysdate - &minutes /( 60*24)
group by inst_id, user_id, sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) a where rownum <= 20
/
3)Longops:
set lines 200
set pagesize 999
cle bre
col sid form 999999
col start_time head "Start|Time" form a12 trunc
col opname head "Operation" form a12 trunc
col target head "Object" form a30
col module head "Module" form a25
col totalwork head "Total|Work" form 9999999999 trunc
col Sofar head "Sofar" form 9999999999 trunc
col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc
col tre head "Time|Remain|(Sec)" form 999999999 trunc
select l.sid,l.serial#,module,s.sql_id,to_char(start_time,'dd-mon:hh24:mi') start_time,
opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
time_remaining tre
from gv$session_longops l, gv$session s
where
s.sid=l.sid
and s.serial#=l.serial#
and totalwork <> SOFAR
order by l.sid,tre, start_time,sid
/
==========================================================4) Response:==============================================================
set pages 200 lines 192
col dat1 NEW_VALUE v_start_time
col dat2 NEW_VALUE v_start_time2
col dat3 NEW_VALUE v_start_time3
select to_char(sysdate,'YYYY-MM-DD_HH24MISS') dat1 from dual;
select ||'_'||to_char(sysdate,'DD-MM-YYYY_HH24_MI_SS') dat1 from v$database;
select name dat2 from v$database;
variable KEY2 varchar2(200);
exec :KEY2 := 'C:\Users\akumar\my_script\'||'&v_start_time2\&v_start_time3&v_start_time..log';
select name||'_DB_RESPONSE_' dat3 from v$database;
spool C:\Users\akumar\my_script\&v_start_time2\&v_start_time3&v_start_time..log
select :KEY2 AS "SPOOL File Location" from dual;
set feedback off;
set pagesize 0;
Select (SELECT NAME FROM V$DATABASE) ||'_Oracle_Database_DB_RESPONSE_Report: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') "TIMESTAMP" from dual;
prompt**=====================================================================================================**
prompt** **Database Current Status**
prompt**=====================================================================================================**
set pagesize 50;
set line 300;
col HOST_NAME FORMAT a12;
col "HOST_ADDRESS" FORMAT a15;
col RESETLOGS_TIME FORMAT a12;
col "DB RAC?" FORMAT A8;
col days format 9999;
select name INSTANCE,HOST_NAME, UTL_INADDR.GET_HOST_ADDRESS "HOST_ADDRESS",
LOGINS, archiver,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", RESETLOGS_TIME "RESET_TIME", FLOOR(sysdate-startup_time) days,
(select DECODE(vp1.value,'TRUE','Yes ('|| decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')
from v$instance,
(select value from v$parameter
where name like 'cluster_database'
) vp1,
(select value from v$parameter
where name like 'cluster_database_instances'
) vp2) "DB RAC?"
from v$database,gv$instance;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Details" format a60;
SELECT
1 sort1,' DBNAME:'||name ||chr(9)||'DBID:'||dbid ||chr(9)|| 'Created:'||to_char(created, 'dd/mm/yyyy hh24:mi:ss') ||chr(10)||
' Log mode:'||log_mode || decode (log_mode,'ARCHIVELOG','',' !!!') ||chr(9)||'Forcelogging:'||force_logging||chr(9)||'Open mode:'||open_mode||chr(10)||
' Remote archiving:'||remote_archive||chr(9)||'Database role:'||database_role "Database Details"
FROM v$database
UNION
SELECT 2 sort1,'Datafiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Datafile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$datafile
UNION
SELECT 3 sort1,'Tempfiles: '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(9)||'Tempfile size(Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT 4 sort1,'Segment size (Gb): '||trim(TO_CHAR(SUM(bytes)/1073741824, '9,990'))
FROM dba_segments
UNION
SELECT 5 sort1,'Tables/Indexes: '|| trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'|| trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT 6 sort1,'Total DB Users: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM sys.user$ WHERE
type# = 1
UNION
SELECT 7 sort1,'Online Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER'
UNION
SELECT 8 sort1,'Active Sessions: '||trim( TO_CHAR(COUNT(*), '9,990'))
FROM gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT 9 sort1,'Session highwater: '|| trim(TO_CHAR(sessions_highwater, '9,990'))
FROM v$license
UNION
SELECT 10 sort1,'SGA (Mb): '||trim(TO_CHAR(SUM(value)/1048576, '99,990.99'))
FROM v$sga;
prompt ----Global last-minute database response time:
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INST_ID,
ROUND (VALUE * 10, 2) "Response Time (msecs)"
FROM GV$SYSMETRIC
WHERE 1 = 1
AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;
prompt----the minimum, average and maximum response time for the last minute
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INST_ID,
ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
FROM GV$SYSMETRIC_SUMMARY
WHERE 1 = 1
AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;
prompt----DBA_HIST_SYSMETRIC_SUMMARY.
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INSTANCE_NUMBER INST_ID,
ROUND (VALUE * 10, 2) "Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_HISTORY
WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;
prompt *** TOP SYSTEM Timed Events (Waits):
prompt*-----------------------------------------------------------------------**
COLUMN event FORMAT A40 HEADING "Wait Event" TRUNC
COLUMN time_waited FORMAT 9999999999999 HEADING "Time|Waited"
COLUMN wait_pct FORMAT 99.90 HEADING "Wait|(%)"
SELECT w.event, w.time_waited, round(w.time_waited/tw.twt*100,2) wait_pct
FROM gv$system_event w, (select inst_id, sum(time_waited) twt from gv$system_event
where time_waited>0
AND event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
group by inst_id
) tw
WHERE w.inst_id = tw.inst_id and w.time_waited>0
and round(w.time_waited/tw.twt*100,2) > 1
and w.event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep',
'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get')
ORDER by 1;
prompt
prompt *** Most buffer gets (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a10;
col object_name format a30;
col statistic_name format a15;
col value format 99999999999;
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%logi%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation (TOP 5):
prompt*-----------------------------------------------------------------------**
col object_type format a15 heading "Object Type"
col object_name format a27 heading "Object Name"
col statistic_name format a22 heading "Statistic Name"
col value format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
FROM v$segment_statistics
WHERE statistic_name LIKE '%phys%' AND VALUE > 50
ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most I/O operation for particualr Query:
prompt*-----------------------------------------------------------------------**
col sql_text format a60;
col reads_per_exe format 99999999 heading 'reads|per_exe';
col "exe" format 99999;
col "sorts" format 99999;
col buffer_gets heading 'buffer|gets';
col disk_reads heading 'disk|reads';
SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe,
a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address"
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;
prompt
prompt Monitoring Full Table Scan of Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col "Full Table Scan" format a30;
SELECT name "Full Table Scan", value FROM v$sysstat
WHERE name LIKE '%table scans %'
ORDER BY name;
prompt
Prompt* Review the query causing high amount of buffer_gets and create additional index to avoid full table scan.
prompt
prompt Monitor TOP CPU Usage and Logical I/O Process:
prompt*-----------------------------------------------------------------------**
col resource_name heading "Resource|Name";
col current_utilization heading "current|utiliz";
col max_utilization heading "Max|utiliz";
col initial_allocation heading "Initial|Alloc";
col limit_value heading "Limit|Value";
select resource_name, current_utilization, max_utilization, initial_allocation, limit_value
from v$resource_limit where resource_name in ('processes','sessions', 'transactions', 'max_rollback_segments');
col name format a30;
select * from (select a.sid, c.username, c.osuser, c.machine, logon_time, b.name, a.value
from v$sesstat a, v$statname b, v$session c
where a.STATISTIC# = b.STATISTIC#
and a.sid = c.sid
and b.name like '%CPU used by this session%'
order by a.value desc)
where rownum < 5;
select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr
and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_mem from v$statname c, v$sesstat a
where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu
from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name) order by 3 desc),
(select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p
where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid
and c.name = 'CPU used by this session') where rownum < 2;
prompt
prompt Monitoring Current Running Long Job in Database:
prompt*-----------------------------------------------------------------------**
set line 200;
col opname format a30;
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;
prompt
prompt Monitoring Object locking:
prompt*-----------------------------------------------------------------------**
set line 200;
col username format a15;
col lock_type format a10;
col osuser format a15;
col owner format a10;
col object_name format a20;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait"
FROM v$session s, dba_locks l, dba_objects o, v$session_wait w
WHERE s.sid = l.session_id
AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id
AND l.session_id = w.sid
ORDER BY s.sid;
prompt ---Blocking
SELECT a.username, a.program, a.sid, a.serial#
FROM v$session a, dba_blockers b
WHERE a.sid = b.holding_session;
SELECT sid,username, blocking_session,event
blocking_session_status
FROM V$SESSION WHERE blocking_session_status='VALID';
---wait events:
set pages 900 lines 200;
col sid for 99999;
col serial# for 99999;
col process for a10 ;
col LAST_CALL_ET/60 for 9999.90;
col object_name for a20;
col USERNAME for a10;
col event for a27;
col ACTION for a20;
col osuser for a10;
col machine for a15
col module for a32
col wait for 999.99
select sid, s.status, s.sql_id, USERNAME, osuser, process, machine, round(LAST_CALL_ET/60,2) wait, obj.object_name, event, module from
v$session s, dba_objects obj
where
event not in ('SQL*Net message from client','SQL*Net message to client','rdbms ipc message','SQL*Net more data from client','smon timer','Streams AQ: qmn slave idle wait','SQL*Net more data to client')
and obj.object_id = s.ROW_WAIT_OBJ#
order by wait,event;
select
round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "IOPS"
from
v$sysstat s
,v$instance i
where
s.name in ('physical reads','physical writes')
/
break on report
compute sum of Value on report
select METRIC_NAME,avg(AVERAGE) as "Value"
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
group by metric_name;
spool off;
==============================================================================================================================
5) sqlh-SQL history:
set linesize 200
set pagesize 2000
select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24MI'), plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio,
(iowait_delta/decode(nvl(iowait_delta,0),0,1,executions_delta)) avg_iowait
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
6) stdby_sync_status:
SELECT NAME, OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
;
select 'Last Sequence applied Time : ' Logs, to_char(next_time, 'DD-MON-YY HH:MI:SS AM') Time from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union select 'Last Sequence received Time: ' Logs, to_char(next_time,'DD-MON-YY HH:MI:SS AM') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
select distinct process,status,sequence# from v$managed_standby;
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values"
from v$recovery_progress
where start_time=(select max(start_time) from v$recovery_progress);
7) sqlver:
col module for a10 trunc
set heading on ;
set feedback on ;
set serveroutput on ;
set lines 300 pages 300;
select sql_id,module,plan_hash_value,child_number,executions,buffer_gets/executions "Bg/gets",elapsed_time/executions/1000000 "Ela/Exec" ,to_char(last_active_time,'DD-MON hh24:mi') "DT" from v$sql where sql_id='&sql_id' order by last_active_time
/
8) session_by_program session by program:
set markup html on
set pagesize 30
spool inactive_sessions.html
select status, count(1) from v$session group by status;
select username, status, count(1) from v$session group by username, status;
select username, program, count(1) from v$session where status='INACTIVE' group by username, program;
select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program,
s.sid,
s.serial#,
p.spid
FROM v$session s,v$process p
WHERE p.addr=s.paddr
and s.status='INACTIVE'
order by p.background desc;
spool off
exit;
9) tbs2 (rman job monitoring):
set lines 200
set pagesize 999
cle bre
col sid form 999999
col start_time head "Start|Time" form a12 trunc
col opname head "Operation" form a12 trunc
col target head "Object" form a30
col module head "Module" form a25
col totalwork head "Total|Work" form 9999999999 trunc
col Sofar head "Sofar" form 9999999999 trunc
col elamin head "Elapsed|Time|(Mins)" form 99999999 trunc
col tre head "Time|Remain|(Sec)" form 999999999 trunc
select l.sid,l.serial#,module,s.sql_id,to_char(start_time,'dd-mon:hh24:mi') start_time,
opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,
time_remaining tre
from gv$session_longops l, gv$session s
where
s.sid=l.sid
and s.serial#=l.serial#
and totalwork <> SOFAR
order by l.sid,tre, start_time,sid
/
10) Tablespace Monitoring:
SET PAGES 200 LINES 132
column "Tablespace" format a60
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace),2) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name ORDER BY 5;
--TEMP
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024
mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
=====================================================================================================================
11) top 10 wait event:
select * from (
SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited)/1000000 "Total Wait Time"
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time < (select max(sample_time) from v$active_session_history)
and h.sample_time > (select max(sample_time)-1/24 from v$active_session_history)
AND h.event_id = e.event_id
AND e.wait_class <>'Idle'
GROUP BY h.event
ORDER BY 2 DESC)
where rownum <10;
12) Table size:
set pages 300 lines 300 ;
col segment_name format a25
select segment_name,owner,segment_type , sum(bytes)/1024/1024/1024
From dba_segments
Where segment_name = '&name'
and owner='&TableOwnerName'
group by segment_name , owner,segment_type
order by 1;
13) blocking_session
select name, open_mode,database_role from v$database;
SELECT sid,username, blocking_session,event
blocking_session_status
FROM V$SESSION WHERE blocking_session_status='VALID';
14) kill inactive session MIND
COL PROGRAM FORM A40
COL MACHINE FORM A20
col osuser form a20
select SID,
B.serial#,
B.PROGRAM,
SCHEMANAME "DB USER",
B.OSUSER "OS USER",
B.MACHINE,
B.TERMINAL,
B.STATUS,
PREV_EXEC_START "START DATE",
ROUND(SECONDS_IN_WAIT/86400,2) "DAYS - WAIT",
PGA_USED_MEM "USED_MEM",
PGA_ALLOC_MEM "ALLOC_MEM",
PGA_FREEABLE_MEM "FREEABLE_MEM"
from v$session b, v$process a
where b.paddr = a.addr
AND SERVICE_NAME = 'ALPHADB'
AND B.PROGRAM NOT IN ('jdequeue.exe','jdenet_k.exe','oexplore.exe')
AND B.TERMINAL NOT IN ('WARDSI05','WARDSI06','WARDSI07','WARDEVDSI05')
AND SUBSTR(B.PROGRAM,1,10) <> 'ORACLE.EXE'
AND SUBSTR(B.PROGRAM,1,10) <> 'RMAN.EXE'
AND SECONDS_IN_WAIT > 21600
and status='INACTIVE'
and type !='BACKGROUND'
order by SECONDS_IN_WAIT desc;
15) Flash reco usage:
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
select * from V$FLASH_RECOVERY_AREA_USAGE;
16) Dba_tab_privs
select privilege,table_name,grantor,grantee from dba_tab_privs where grantee='MWSIUSR';
17) Session:
set serveroutput on
set echo off feed off veri off
exec dbms_output.enable(200000)
accept SID prompt 'Enter Oracle SID: '
var SID number;
exec :SID:=&sid;
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$session s
where sid = :SID;
exception
when no_data_found then
dbms_output.put_line('Unable to find SID &&SID!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;
select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('sql_id : '|| s.sql_id);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.object_name,
'TD', o.object_name,
'TM', o.object_name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, dba_objects o
where sid = s.sid
and l.id1 = o.object_id ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/
SELECT s.sid,pm.pid,pm.serial#,pm.category,
ROUND(pm.allocated/1024/1024,2) as ALLOCATED_MB,
ROUND(pm.used/1024/1024,2) as USED_MB,
ROUND((pm.allocated/1024/1024) -(pm.used/1024/1024),2) as FREE_MB,
ROUND(pm.max_allocated/1024/1024,2) as MAX_ALLOCATED_MB
FROM
v$session s
, v$process p
, v$process_memory pm
WHERE
s.paddr = p.addr
AND p.pid = pm.pid
AND s.sid IN (:SID)
ORDER BY
sid
, category
/
undef SID
set feedback on
set feed on
