select name, total_mb/1024 total_gb, free_mb/1024 free_gb, (total_mb - free_mb)/1024 used_gb,
select path,header_status,os_mb,total_mb from v\$asm_disk order by header_status desc
Undo stealing session *
********************************
select BEGIN_TIME,MAXQUERYLEN,MAXQUERYID,MAXCONCURRENCY,EXPSTEALCNT,EXPBLKRELCNT,EXPBLKREUCNT,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS,TUNED_UNDORETENTION from v$undostat;
********************************
Redo Generation per user *********************************col machine for a15
col username for a10
col redo_MB for 999G990 heading "Redo |Size MB"
column sid_serial for a13;
select b.inst_id,
lpad((b.SID || ',' || lpad(b.serial#,5)),11) sid_serial,
b.username,
machine,
b.osuser,
b.status,
a.redo_mb
from (select n.inst_id, sid,
round(value/1024/1024) redo_mb
from gv$statname n, gv$sesstat s
where n.inst_id=s.inst_id
and n.name = 'redo size'
and s.statistic# = n.statistic#
order by value desc
) a,
gv$session b
where b.inst_id=a.inst_id
and a.sid = b.sid
and rownum <= 30
;
********************************
Backup progress Monitor : *********************************SELECT
i.instance_name instance_name
, sid sid
, serial# serial_num
, b.opname opname
, TO_CHAR(b.start_time, 'mm/dd/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork
, 0
, 0.001
, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
, DECODE( b.time_remaining
, 0
, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'mm/dd/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'mm/dd/yy HH24:MI:SS')
) done_at
FROM
gv$session a
JOIN gv$session_longops b USING (sid,serial#)
JOIN gv$instance i ON ( i.inst_id = a.inst_id
AND i.inst_id = b.inst_id)
WHERE
a.program LIKE 'rman%'
AND b.opname LIKE 'RMAN%'
AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY
i.instance_name
, b.start_time
/
********************************
DBA JOBS MONITORING *********************************select substr(job,1,4) "job",
substr(schema_user,1,10) "user",
substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
substr(broken,1,2) "b",
substr(failures,1,6) "failed",
substr(what,1,32) "command"
from dba_jobs;
********************************
Index info by table *
********************************
select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and table_name like '&table'
order by 2, 1
/
select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and table_name like '&table'
order by 2, 1
/
Show indexed columns
select column_name
from dba_ind_columns
where index_name = '&index'
order by column_position
set echo off
set feedback off
set verify off
set wrap on
set pagesize 60
set linesize 132
set newpage 0
set serverout on
set trimspool on
ttitle off
btitle off
clear columns
clear breaks
clear compute
-- spool show_logins.txt
col stat# format 9999
select statistic# stat#,
substr(name,1,20) name,
value,
class
from v$sysstat
where name like '%logon%';
select status, count(*)
from v$session
where type <> 'BACKGROUND'
group by status;
select username,count(*) from v$session where type <> 'BACKGROUND' group by username;
break on inst_id skip 1
col inst_id format 99 heading IN
col program format a7 trunc
col sid format 999
col serial# format 9999999 heading serl#
col sid_serl# format a16 trunc
col child format a6 trunc
col logon_time format a9 trunc
col cpu_time format 9999999
col event format a37 trunc
col typ format a3 trunc
col username format a8 trunc
col osuser format a7 trunc
col status format a1 trunc
col sql_text format a43 trunc
col p1 format a12 trunc
col p2 format 99999999
select p.spid child,
to_char(a.logon_time,'MMDD/hh24mi') logon_time,
lpad(a.sid || ',' || a.serial#,9,' ') sid_serl#,
decode(a.username, NULL, substr(a.program,instr(a.program,'(',1,1)), a.username) username,
a.osuser,
a.machine,
a.sql_id sql_id,
a.sql_hash_value hash_value,
decode(a.status, 'ACTIVE', '*', 'INACTIVE', ' ', 'KILLED', 'K', 'SNIPED', 'S', a.sql_id,substr(a.status,1,1)) || b.sql_text sql_text,
c.event,
c.wait_time,
decode(c.event,'enqueue',chr(bitand(c.p1,-16777216)/16777215)||chr(bitand(c.p1,16711680)/65536),c.p1) p1,
decode(c.event,'enqueue',mod(c.P1,16),c.p2) p2
from gv$session a, gv$sqltext b, gv$process p, gv$session_wait c
where a.paddr = p.addr
and a.inst_id = p.inst_id
and b.piece(+) = 0
and a.sql_address = b.address(+)
and a.inst_id = b.inst_id(+)
and a.inst_id = c.inst_id
and a.sid = c.sid
and a.status = 'ACTIVE'
--and a.status = 'INACTIVE'
--and c.event like '%enq: TX - row lock contention%'
and a.type <> 'BACKGROUND'
--and a.username = 'FLEXAPP'
--and a.osuser = 'GLMSAD'
--and a.program like 'rman%'
order by a.inst_id, a.type, a.status, a.sid;
-- spool off
set feedback 6
set verify on
set wrap on
set heading on
set pagesize 66
set linesize 132
ttitle off
btitle off
-- clear columns
-- clear breaks
clear compute
set echo on
Archive generation Size
set linesize 200
break on Date_Archived
prompt
prompt
prompt **********************************************************************************
prompt ** Logs Trends Analysis for Last Week (Day Wise Aggredates) **********************
prompt **********************************************************************************
col Date_Archived for a15
col Total_Bytes_MB format 99999999.99
break on Date_Archived
select trunc(COMPLETION_TIME) Date_Archived,thread#,
sum(BLOCKS*512/1024/1024) Total_Bytes_MB
from V$ARCHIVED_LOG
where trunc(completion_time) between sysdate-7 and sysdate
group by thread#, trunc(COMPLETION_TIME)
order by 1 desc,2
/
select to_char(FIRST_TIME,'dd-MON-rr hh24') , count(1), sum(BLOCKS*BLOCK_SIZE)/(1024*1024*1024) " in GB" from
v$archived_log group by to_char(FIRST_TIME,'dd-MON-rr hh24') order by 1
/
CPU USAGE:
SELECT se.username, ss.sid,se.program, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;
SCHEMA STAT
set pagesize 0
select table_name, to_char(last_analyzed, 'DD-MON-YY HH:MM:SS') last_analyzed, num_rows , sample_size
from dba_tab_statistics
where
owner = '&owner'
/