============================ ASM ============================
SELECT name, free_mb/1024, total_mb/1024, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;
==================== Tablespaces =========================
select tsu.tablespace_name, ceil(tsu.maxmb) "MAXIMUM size MB", ceil(tsu.used_mb) "allocated MB",ceil(tsu.used_mb-nvl(tsf.free_mb,0)) "used MB"
,ceil(ceil(tsu.used_mb-nvl(tsf.free_mb,0))*100/tsu.maxmb) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb, sum(maxbytes)/1024/1024 maxmb
from dba_data_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+) order by 5 desc;
select a.tablespace_name TABLESPACE_NAME, c.status, a.df_count, decode(a.auto_extend,0,'NO','YES') as autoextend, a.value TOTAL_MB,
NVL(b.value,0) USED_MB, a.value-NVL(b.value,0) FREE_MB,
round((NVL(b.value,0))*100/a.value,2) PCRT_USED FROM
(select tablespace_name, sum(decode(autoextensible,'YES',CASE when MAXBYTES > BYTES THEN MAXBYTES ELSE BYTES END, bytes))/1024/1024 value,
count(file_name) df_count, sum(decode(autoextensible, 'YES', 1, 0)) auto_extend from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 value from dba_segments group by tablespace_name) b,
(select tablespace_name, status, CONTENTS from dba_tablespaces) c
where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name and c.contents !='UNDO' and c.status != 'READ ONLY' order by PCRT_USED desc;
======================================== Kill sessions
select machine,'alter system kill session '''||sid||','||serial#||'@'||inst_id||''' immediate;' from gv$session where machine like '%SADIQ%';
======================================== ASH
select * from SYS.DBA_HIST_ACTIVE_SESS_HISTORY where sample_time between to_date('09-may-2017 18:30:00','dd-mon-yyyy hh24:mi:ss') and to_date('09-may-2017 21:00:00','dd-mon-yyyy hh24:mi:ss') and event is not null;
======================================== Locking and Blocking
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
======================================== PID to SID
select a.sid,a.serial#,b.spid,a.OSUSER,a.MACHINE,a.sql_id from v$session a,v$process b where a.paddr=b.addr and b.spid=18219; ---7668 is a PID
======================================== Redo Log Details
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
============================================= DR
SQL> 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;
SQL>
================================================ HWM
select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*8192)/1024/1024 )||'m;' as cmd, bytes/1024/1024 "MB_WILL_BE_SAVED_BY_COMMAND" from dba_data_files a, ( select file_id, max(block_id+blocks-1) as hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*8192/1024/1024)- ceil((nvl(hwm,1)* 8192)/1024/1024 ) > 0 and a.tablespace_name NOT IN ('SYSAUX','SYSTEM','UNDOTB1','UNDOTB2','UNDOTB3','UNDOTB4');
================================================== RMAN
select to_char(START_TIME,'DD-MM-YY:HH-MI-SS'),to_char(END_TIME,'DD-MM-YY:HH-MI-SS'),STATUS from v$rman_backup_job_details where start_time>sysdate-5;
select to_char(END_TIME,'DD-MM-YY:HH-MI-SS'),STATUS from v$rman_backup_job_details;
Backup Size:-
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize
from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = 'A'
group by trunc(bp.completion_time), backup_type) order by 1, 2;
================================================ Per Day Archives
SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_MB
--,thread
FROM
(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,Count(1) Count#,Min(RECID) Min#,Max(RECID) Max#
--,thread# thread
FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-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
where rownum <10
order by 1 desc;per hour :
=============================================== Check Hourly Archive log generation
select
to_char(first_time,'YY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
COUNT(*) TOT
from v$log_history where trunc(first_time)='08-MAY-2018'
group by to_char(first_time,'YY-MM-DD')
order by day ;
============================================ Check current session generating Redo
select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid
where name like '%redo entries%' and TYPE not like '%BACKGROUND%'
order by value desc
============================================== Failed Jobs
select * from SYS.DBA_SCHEDULER_JOB_RUN_DETAILS where status='FAILED' order by 2 desc;
============================================ Table Fragmenttion
select table_name,
round(blocks*8/1024/1024,2) "Size Consumed GB",
round(avg_row_len*num_rows/1024/1024/1024,2) "Actual Size GB",
round((blocks*8/1024/1024) - (avg_row_len*num_rows/1024/1024/1024),2) "Space wasted GB",
round((100 - ((avg_row_len*num_rows/1024/1024/1024)*100)/(blocks*8/1024/1024)),2) "% fragmented"
from dba_tables
where table_name='TBLCDRPREPAID'
and round(blocks*8,2) > 0
and round(avg_row_len*num_rows/1024,2) > 0
order by 5 desc;
/
================================================== Object in a perticular DB block
-- List an Object and Object Type via a Given File and Block ID.
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN segment_name FORMAT A24
COLUMN segment_type FORMAT A24
SELECT segment_name, segment_type, block_id, blocks
FROM dba_extents
WHERE
file_id = &file_no
AND
( &block_value BETWEEN block_id AND ( block_id + blocks ) )
/
SELECT name, free_mb/1024, total_mb/1024, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;
==================== Tablespaces =========================
select tsu.tablespace_name, ceil(tsu.maxmb) "MAXIMUM size MB", ceil(tsu.used_mb) "allocated MB",ceil(tsu.used_mb-nvl(tsf.free_mb,0)) "used MB"
,ceil(ceil(tsu.used_mb-nvl(tsf.free_mb,0))*100/tsu.maxmb) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb, sum(maxbytes)/1024/1024 maxmb
from dba_data_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+) order by 5 desc;
select a.tablespace_name TABLESPACE_NAME, c.status, a.df_count, decode(a.auto_extend,0,'NO','YES') as autoextend, a.value TOTAL_MB,
NVL(b.value,0) USED_MB, a.value-NVL(b.value,0) FREE_MB,
round((NVL(b.value,0))*100/a.value,2) PCRT_USED FROM
(select tablespace_name, sum(decode(autoextensible,'YES',CASE when MAXBYTES > BYTES THEN MAXBYTES ELSE BYTES END, bytes))/1024/1024 value,
count(file_name) df_count, sum(decode(autoextensible, 'YES', 1, 0)) auto_extend from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 value from dba_segments group by tablespace_name) b,
(select tablespace_name, status, CONTENTS from dba_tablespaces) c
where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name and c.contents !='UNDO' and c.status != 'READ ONLY' order by PCRT_USED desc;
======================================== Kill sessions
select machine,'alter system kill session '''||sid||','||serial#||'@'||inst_id||''' immediate;' from gv$session where machine like '%SADIQ%';
======================================== ASH
select * from SYS.DBA_HIST_ACTIVE_SESS_HISTORY where sample_time between to_date('09-may-2017 18:30:00','dd-mon-yyyy hh24:mi:ss') and to_date('09-may-2017 21:00:00','dd-mon-yyyy hh24:mi:ss') and event is not null;
======================================== Locking and Blocking
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
======================================== PID to SID
select a.sid,a.serial#,b.spid,a.OSUSER,a.MACHINE,a.sql_id from v$session a,v$process b where a.paddr=b.addr and b.spid=18219; ---7668 is a PID
======================================== Redo Log Details
SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#,
a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
============================================= DR
SQL> 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;
SQL>
================================================ HWM
select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*8192)/1024/1024 )||'m;' as cmd, bytes/1024/1024 "MB_WILL_BE_SAVED_BY_COMMAND" from dba_data_files a, ( select file_id, max(block_id+blocks-1) as hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*8192/1024/1024)- ceil((nvl(hwm,1)* 8192)/1024/1024 ) > 0 and a.tablespace_name NOT IN ('SYSAUX','SYSTEM','UNDOTB1','UNDOTB2','UNDOTB3','UNDOTB4');
================================================== RMAN
select to_char(START_TIME,'DD-MM-YY:HH-MI-SS'),to_char(END_TIME,'DD-MM-YY:HH-MI-SS'),STATUS from v$rman_backup_job_details where start_time>sysdate-5;
select to_char(END_TIME,'DD-MM-YY:HH-MI-SS'),STATUS from v$rman_backup_job_details;
Backup Size:-
select ctime "Date", decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type, bsize "Size MB"
from (select trunc(bp.completion_time) ctime, backup_type, round(sum(bp.bytes/1024/1024),2) bsize
from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = 'A'
group by trunc(bp.completion_time), backup_type) order by 1, 2;
================================================ Per Day Archives
SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_MB
--,thread
FROM
(SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,Count(1) Count#,Min(RECID) Min#,Max(RECID) Max#
--,thread# thread
FROM v$log_history GROUP BY To_Char(First_Time,'YYYY-MM-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
where rownum <10
order by 1 desc;per hour :
=============================================== Check Hourly Archive log generation
select
to_char(first_time,'YY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
COUNT(*) TOT
from v$log_history where trunc(first_time)='08-MAY-2018'
group by to_char(first_time,'YY-MM-DD')
order by day ;
============================================ Check current session generating Redo
select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid
where name like '%redo entries%' and TYPE not like '%BACKGROUND%'
order by value desc
============================================== Failed Jobs
select * from SYS.DBA_SCHEDULER_JOB_RUN_DETAILS where status='FAILED' order by 2 desc;
============================================ Table Fragmenttion
select table_name,
round(blocks*8/1024/1024,2) "Size Consumed GB",
round(avg_row_len*num_rows/1024/1024/1024,2) "Actual Size GB",
round((blocks*8/1024/1024) - (avg_row_len*num_rows/1024/1024/1024),2) "Space wasted GB",
round((100 - ((avg_row_len*num_rows/1024/1024/1024)*100)/(blocks*8/1024/1024)),2) "% fragmented"
from dba_tables
where table_name='TBLCDRPREPAID'
and round(blocks*8,2) > 0
and round(avg_row_len*num_rows/1024,2) > 0
order by 5 desc;
/
================================================== Object in a perticular DB block
-- List an Object and Object Type via a Given File and Block ID.
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN segment_name FORMAT A24
COLUMN segment_type FORMAT A24
SELECT segment_name, segment_type, block_id, blocks
FROM dba_extents
WHERE
file_id = &file_no
AND
( &block_value BETWEEN block_id AND ( block_id + blocks ) )
/
