Home / Database Scripts / Oracle Database Monitoring Queries

Oracle Database Monitoring Queries

QUERY TO CHECK SIZE OF ALL TABLESPACES

SELECT /* + RULE */ df.tablespace_name
“Tablespace”,
df.bytes / ( 1024 * 1024 )
“Size (MB)”,
SUM(fs.bytes) / ( 1024 * 1024 )
“Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1)
“% Free”,
Round(( df.bytes  SUM(fs.bytes) ) * 100 / df.bytes)
“% Used”
FROM   dba_free_space fs,
(SELECT tablespace_name,
SUM(bytes) bytes
FROM   dba_data_files
GROUP  BY tablespace_name) df
WHERE  fs.tablespace_name (+) = df.tablespace_name
GROUP  BY df.tablespace_name,
df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / ( 1024 * 1024 ),
SUM(df.bytes_free) / ( 1024 * 1024 ),
Nvl(Round(( SUM(fs.bytes)  df.bytes_used ) * 100 /
fs.bytes), 1),
Round(( SUM(fs.bytes)  df.bytes_free ) * 100 / fs.bytes)
FROM   dba_temp_files fs,
(SELECT tablespace_name,
bytes_free,
bytes_used
FROM   v$temp_space_header
GROUP  BY tablespace_name,
bytes_free,
bytes_used) df
WHERE  fs.tablespace_name (+) = df.tablespace_name
GROUP  BY df.tablespace_name,
fs.bytes,
df.bytes_free,
df.bytes_used
ORDER  BY 4 DESC;

 

QUERY TO CHECK BLOCKING SESSIONS PER USER

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;

 

QUERY TO CHECK LIST OF LOCKS IN THE DATABASE

SELECT a.inst_id,
a.sid,
a.TYPE,
a.id1,
a.id2,
Decode(a.lmode, 0, ‘NONE’,
1, ‘NULL’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’,
‘?’),
Decode(a.request, 0, ‘NONE’,
1, ‘NULL’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’,
‘?’),
a.BLOCK
FROM   gv$lock a
WHERE  a.id1 IN (SELECT id1
FROM   gv$lock
WHERE  request <> 0)
ORDER  BY a.id1,
a.request,
a.sid;

 

QUERY TO CHECK TOP 10 DATABASE LOAD QUERIES

SELECT *
FROM   (SELECT sql_text,
cpu_time / 1000000     cpu_time,
elapsed_time / 1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
FROM   v$sqlarea
ORDER  BY cpu_time DESC,
disk_reads DESC)
WHERE  ROWNUM < 11;

 

QUERY TO CHECK OPEN CURSOR BY USER

SELECT SUM(a.value) total_cur,
Avg(a.value) avg_cur,
Max(a.value) max_cur,
s.username,
s.machine
FROM   v$sesstat a,
v$statname b,
v$session s
WHERE  a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = ‘opened cursors current’
GROUP  BY s.username,
s.machine
ORDER  BY 1 DESC;

 

QUERY TO CHECK USERS, SESSIONS, CPU AND I/O CONSUMPTION

SELECT p.spid                                                       SPID,
To_char(s.logon_time, ‘DDMonYY HH24:MI’)                     date_login,
s.username,
Decode(Nvl(p.background, 0), 1, bg.description,
s.program)                      program,
ss.value / 100                                               CPU,
physical_reads                                               disk_io,
( Trunc(SYSDATE, ‘J’)  Trunc(logon_time, ‘J’) )             days,
Round(( ss.value / 100 ) / ( Decode(( Trunc(SYSDATE, ‘J’) 
Trunc(logon_time, ‘J’) ),
0, 1,
(
Trunc(SYSDATE, ‘J’)  Trunc(
logon_time, ‘J’) )) ), 2) cpu_per_day
FROM   v$process p,
v$session s,
v$sesstat ss,
v$sess_io si,
v$bgprocess bg
WHERE  s.paddr = p.addr
AND ss.sid = s.sid
AND ss.statistic# = 12
AND si.sid = s.sid
AND bg.paddr(+) = p.addr
AND Round(( ss.value / 100 ), 0) > 10
ORDER  BY 8;

 

 

QUERY TO FIND ACTIVE SESSIONS IN DATABASE

SELECT ‘ACTIVE SESSION’,
sid,
serial#,
machine,
osuser,
username,
status
FROM   v$session
WHERE  username != ‘NULL’
AND status = ‘ACTIVE’;

 

QUERY TO CHECK FOR TABLE LOCKS

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;

 

QUERY TO FIND Undo Tablespace USAGE

SELECT s.username,
s.sid,
rn.name,
rs.rssize / 1024 / 1024 “UsedSize IN MBs”,
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;

 

QUERY TO FIND 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;

 

QUERY TO FIND DATABASE SIZE

SELECT SUM(tot.bytes / ( 1024 * 1024 * 1024 ))
“Total size iN GBs”,
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;

 

QUERY TO FIND Database, Redologs, Temp AND Control File SIZE

SELECT ‘Full Data Size: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM   dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM   dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM   v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM   v$controlfile;

 

 

 

 

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also

Application Report Queries

QUERY TO CHECK LIST OF RUNNING REQUEST

Leave a Reply