-- Instance startup info
set lines 190
col HOST_NAME for a20
select inst_id,host_name,instance_name,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "LAST_STARTUP_TIME"
from gv$instance order by 1;
-- Tablespace utilization
set lines 200
SELECT tbs.tablespace_name,
tot.bytes / 1024 / 1024 / 1024 total_gb,
tot.bytes / 1024 / 1024 -SUM(nvl(fre.bytes, 0)) / 1024 / 1024 used_mb,
SUM(nvl(fre.bytes, 0)) / 1024 / 1024 free_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
-- AND tbs.tablespace_name = 'USERS'
GROUP BY tbs.tablespace_name,
tot.bytes / 1024,
tot.bytes
ORDER BY 5, 1;
select tablespace_name,
round(used_space/(1024*1024),2),
round(tablespace_size/(1024*1024),2),
round(used_percent, 2)
from dba_tablespace_usage_metrics
where round(used_percent,2) > 90;
-- Datafile information check query:
set lines 190
col FILE_NAME for a50
select file_name,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_temp_files
where tablespace_name='TEMP'
group by file_name,maxbytes,autoextensible;
set lines 190
col FILE_NAME for a60
select file_id,file_name,status,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_data_files
where tablespace_name='SYSAUX'
group by file_id,file_name,status,maxbytes,autoextensible
order by 1;
-- Query to get all files datafile + tempfile + logfile:
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/
-- Redo log file info
set linesize 120
set pagesize 35
col group# format 999
col thread# format 999
col member format a50 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
-- Session info
-- Last Call Activity in minutes:
set lines 190
col USERNAME for a15
col MACHINE for a20
col PROGRAM for a20
select to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss') "Login_Time", a.sid, a.serial#, b.spid,a.username , a.machine, a.program
, floor(a.last_call_et / 60) "Minutes"
, a.status
--, EVENT
from v$session a, v$process b
where a.paddr = b.addr and a.username!='SYS' and a.last_call_et > 1800
order by a.last_call_et desc
/
set lines 200
set pages 1000
col MACHINE for a30
col username for a15
select inst_id,username,machine, count(1) from gv$session
where username='NXTL_APP_OVMEKS' and status='ACTIVE' group by inst_id,machine,username order by 1 ,2 ;
select inst_id, username,count(1) INACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='INACTIVE'
group by inst_id,username order by 1
/
select inst_id, username,count(1) ACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE'
group by inst_id,username order by 1
/
-- Query info
set lines 190
col SQL_TEXT for a40
col USERNAME for a10
col USERNAME for a10
col OSUSER for a10
col SPID for a10
col MACHINE for a15
col PROGRAM for a15
col LOGIN for a15
select distinct b.sid,b.inst_id,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,c.spid,
substr(b.machine,1,22) machine,
to_char(b.logon_time,'ddMon hh24:mi') login,
round(b.last_call_et/3600,2) "ACTIVE_SINCE_HRS",a.sql_text
from gv$sql a, gv$session b, gv$process c
where b.sql_address = a.address
and b.sql_hash_value = a.hash_value and b.paddr = c.addr and b.status='ACTIVE' and b.username not in ('SYS','SYSTEM','DBSNMP')
order by 2;
-- Run it from Os prompt:
while true
do
date; echo "select inst_id, status, count(*) from gv\$session where username is not null and username not in ('SYS','SYSTEM','DBSNMP') group by inst_id,
status order by 1,2;"| sqlplus -s / as sysdba
echo "select 'SESSIONS '||'LOCKING'||' COUNT '|| count(*) FROM gv\$lock c, dba_objects b, gv\$locked_object d, gv\$session s WHERE b.object_id = d.object_id
and d.session_id = c.sid and s.sid = c.sid and d.session_id=s.sid AND c.block = 1 and c.ctime > 60;"| sqlplus -s / as sysdba
echo "set pages 30;"| sqlplus -s / as sysdba
echo "select event, count(*) from gv\$session where status='ACTIVE' and username is not null group by event order by 2;"| sqlplus -s / as sysdba
sleep 30
done;
-- SPID info
Session + Process Information:
select a.logon_time, a.sid, a.username, a.machine, a.program,b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.sid=101
/
-- Monitoring Long Running:
set lines 190
col pct_comp for a10
col opname for a20
col message for a68
col START_TIME for a25
col MESSAGE for a40
SELECT USERNAME,
OPNAME, TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') "START_TIME",
round(elapsed_seconds/60,2) "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%' "PCT_COMP" ,
TOTALWORK, message
FROM GV$SESSION_LONGOPS
WHERE --USERNAME = 'SYS' and
sofarORDER BY ELAPSED_SECONDS DESC
/
-- DATAGUARD info
-- STANDALONE DATAGUARD CHECK
select max(sequence#) "Last Primary Seq Generated" from v$archived_log ;
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from gv$archived_log
where resetlogs_change#=(select resetlogs_change# from gv$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from gv$log_history
where first_time=(select max(first_time) from gv$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Thread Last Seq Received Last Seq Applied
------------ ----------------- ----------------
1 2955 2786
-- RAC DATAGUARD CHECK
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#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 39136 39136 0
2 45284 45284 0
3 46699 46699 0
select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log where applied='YES' group by thread# order by 1;
-- ASM info
set lines 300
col PATH for a30
SELECT instance_name,status,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS AM') db_date FROM v$instance, dual;
select PATH,HEADER_STATUS,OS_MB,TOTAL_MB,FREE_MB,NAME,STATE from v$asm_disk order by name ;
select NAME,STATE,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024,ROUND((1- (free_mb / total_mb))*100, 2) Pct_used from v$asm_diskgroup;
SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;
select * from v$asm_operation;
-- Log Switch History:
SET VERIFY OFF
SET LINESIZE 150
SET PAGESIZE 999
COL DAY FOR A10
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v$log_history a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY 1
/
-- Archive generation per day
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT To_Char(First_Time,'YYYY-MON-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#
FROM v$log_history
GROUP BY To_Char(First_Time,'YYYY-MON-DD')
ORDER BY 1 DESC
) A,
(
SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes
FROM v$log
) B;
set lines 190
col HOST_NAME for a20
select inst_id,host_name,instance_name,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') "LAST_STARTUP_TIME"
from gv$instance order by 1;
-- Tablespace utilization
set lines 200
SELECT tbs.tablespace_name,
tot.bytes / 1024 / 1024 / 1024 total_gb,
tot.bytes / 1024 / 1024 -SUM(nvl(fre.bytes, 0)) / 1024 / 1024 used_mb,
SUM(nvl(fre.bytes, 0)) / 1024 / 1024 free_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
-- AND tbs.tablespace_name = 'USERS'
GROUP BY tbs.tablespace_name,
tot.bytes / 1024,
tot.bytes
ORDER BY 5, 1;
select tablespace_name,
round(used_space/(1024*1024),2),
round(tablespace_size/(1024*1024),2),
round(used_percent, 2)
from dba_tablespace_usage_metrics
where round(used_percent,2) > 90;
-- Datafile information check query:
set lines 190
col FILE_NAME for a50
select file_name,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_temp_files
where tablespace_name='TEMP'
group by file_name,maxbytes,autoextensible;
set lines 190
col FILE_NAME for a60
select file_id,file_name,status,sum(bytes/1024/1024) "Size_MB",sum(maxbytes/1024/1024) "Maxbytes",autoextensible from dba_data_files
where tablespace_name='SYSAUX'
group by file_id,file_name,status,maxbytes,autoextensible
order by 1;
-- Query to get all files datafile + tempfile + logfile:
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/
-- Redo log file info
set linesize 120
set pagesize 35
col group# format 999
col thread# format 999
col member format a50 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
-- Session info
-- Last Call Activity in minutes:
set lines 190
col USERNAME for a15
col MACHINE for a20
col PROGRAM for a20
select to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss') "Login_Time", a.sid, a.serial#, b.spid,a.username , a.machine, a.program
, floor(a.last_call_et / 60) "Minutes"
, a.status
--, EVENT
from v$session a, v$process b
where a.paddr = b.addr and a.username!='SYS' and a.last_call_et > 1800
order by a.last_call_et desc
/
set lines 200
set pages 1000
col MACHINE for a30
col username for a15
select inst_id,username,machine, count(1) from gv$session
where username='NXTL_APP_OVMEKS' and status='ACTIVE' group by inst_id,machine,username order by 1 ,2 ;
select inst_id, username,count(1) INACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='INACTIVE'
group by inst_id,username order by 1
/
select inst_id, username,count(1) ACTIVE_COUNT from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE'
group by inst_id,username order by 1
/
-- Query info
set lines 190
col SQL_TEXT for a40
col USERNAME for a10
col USERNAME for a10
col OSUSER for a10
col SPID for a10
col MACHINE for a15
col PROGRAM for a15
col LOGIN for a15
select distinct b.sid,b.inst_id,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,c.spid,
substr(b.machine,1,22) machine,
to_char(b.logon_time,'ddMon hh24:mi') login,
round(b.last_call_et/3600,2) "ACTIVE_SINCE_HRS",a.sql_text
from gv$sql a, gv$session b, gv$process c
where b.sql_address = a.address
and b.sql_hash_value = a.hash_value and b.paddr = c.addr and b.status='ACTIVE' and b.username not in ('SYS','SYSTEM','DBSNMP')
order by 2;
-- Run it from Os prompt:
while true
do
date; echo "select inst_id, status, count(*) from gv\$session where username is not null and username not in ('SYS','SYSTEM','DBSNMP') group by inst_id,
status order by 1,2;"| sqlplus -s / as sysdba
echo "select 'SESSIONS '||'LOCKING'||' COUNT '|| count(*) FROM gv\$lock c, dba_objects b, gv\$locked_object d, gv\$session s WHERE b.object_id = d.object_id
and d.session_id = c.sid and s.sid = c.sid and d.session_id=s.sid AND c.block = 1 and c.ctime > 60;"| sqlplus -s / as sysdba
echo "set pages 30;"| sqlplus -s / as sysdba
echo "select event, count(*) from gv\$session where status='ACTIVE' and username is not null group by event order by 2;"| sqlplus -s / as sysdba
sleep 30
done;
-- SPID info
Session + Process Information:
select a.logon_time, a.sid, a.username, a.machine, a.program,b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.sid=101
/
-- Monitoring Long Running:
set lines 190
col pct_comp for a10
col opname for a20
col message for a68
col START_TIME for a25
col MESSAGE for a40
SELECT USERNAME,
OPNAME, TO_CHAR(START_TIME,'DD-MON-YYYY HH12:MI:SS AM') "START_TIME",
round(elapsed_seconds/60,2) "Elaps(min)", round(TIME_REMAINING/60,2) "TimeRem(min)", round(SOFAR/TOTALWORK*100,2)||'%' "PCT_COMP" ,
TOTALWORK, message
FROM GV$SESSION_LONGOPS
WHERE --USERNAME = 'SYS' and
sofar
/
-- DATAGUARD info
-- STANDALONE DATAGUARD CHECK
select max(sequence#) "Last Primary Seq Generated" from v$archived_log ;
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from gv$archived_log
where resetlogs_change#=(select resetlogs_change# from gv$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from gv$log_history
where first_time=(select max(first_time) from gv$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Thread Last Seq Received Last Seq Applied
------------ ----------------- ----------------
1 2955 2786
-- RAC DATAGUARD CHECK
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#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 39136 39136 0
2 45284 45284 0
3 46699 46699 0
select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log where applied='YES' group by thread# order by 1;
-- ASM info
set lines 300
col PATH for a30
SELECT instance_name,status,TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS AM') db_date FROM v$instance, dual;
select PATH,HEADER_STATUS,OS_MB,TOTAL_MB,FREE_MB,NAME,STATE from v$asm_disk order by name ;
select NAME,STATE,TOTAL_MB/1024,FREE_MB/1024,USABLE_FILE_MB/1024,ROUND((1- (free_mb / total_mb))*100, 2) Pct_used from v$asm_diskgroup;
SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;
select * from v$asm_operation;
-- Log Switch History:
SET VERIFY OFF
SET LINESIZE 150
SET PAGESIZE 999
COL DAY FOR A10
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'Total'
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v$log_history a
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY 1
/
-- Archive generation per day
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
SELECT To_Char(First_Time,'YYYY-MON-DD') DAY, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max#
FROM v$log_history
GROUP BY To_Char(First_Time,'YYYY-MON-DD')
ORDER BY 1 DESC
) A,
(
SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes
FROM v$log
) B;
