Scribd
Upload
35 views13 pages

Quries

The document contains a comprehensive set of SQL queries for managing and monitoring Oracle databases, focusing on tablespace usage, temporary tablespace management, session information, and system performance metrics. It includes commands to check tablespaces, identify invalid objects, monitor CPU usage, and manage sessions, including killing inactive sessions. Additionally, it provides insights into database size and memory components, along with methods to identify locks in concurrent jobs.

Uploaded by

chinnasamy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views13 pages

Quries

The document contains a comprehensive set of SQL queries for managing and monitoring Oracle databases, focusing on tablespace usage, temporary tablespace management, session information, and system performance metrics. It includes commands to check tablespaces, identify invalid objects, monitor CPU usage, and manage sessions, including killing inactive sessions. Additionally, it provides insights into database size and memory components, along with methods to identify locks in concurrent jobs.

Uploaded by

chinnasamy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 13

MMK

consultant.mubashir@gmail.com

DBA QUERIES FOR ORACLE DATABASE

Check Tablespaces
set lines 133 pages 133
set feed off
column "tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
column "%Free" heading "% | Free" format 999.99
column "%Free Ext" heading "% | Free Ext" format 999.99
column "Graph" heading "Graph" format a11
column tablespace_name heading "Tablespace | Name" format a20

SELECT
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc
/

MMK
MMK
consultant.mubashir@gmail.com

Check SYSAUX Tablespace Also


set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc
/

SELECT a.tablespace_name,
ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
c.BYTES / 1024 / 1024 space_allocated,
ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
c.DATAFILES
FROM dba_tablespaces a,
( SELECT tablespace_name,
SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name
) b,
( SELECT COUNT (1) DATAFILES,
SUM (BYTES) BYTES,
tablespace_name
FROM dba_data_files
GROUP BY tablespace_name
)c
WHERE b.tablespace_name(+) = a.tablespace_name
AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;

MMK
MMK
consultant.mubashir@gmail.com

Temporary tablespace Usage


SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;

Temporary Tablespace Usage.

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COL TABLESPACE_SIZE FOR 999,999,999,999


COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999

SELECT *
FROM dba_temp_free_space
/

To see top 10 consuming process:


select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB
Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value
AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10

MMK
MMK
consultant.mubashir@gmail.com

Get 10 sessions with largest temp usage:


cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Identifying WHO is currently using TEMP Segments:

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used,


c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;

MMK
MMK
consultant.mubashir@gmail.com

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

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
/

What temporary tablespace is each user using?:


select username, temporary_tablespace, default_tablespace from dba_users ;

List all tablespaces and some settings:


select tablespace_name, status, contents, extent_management from dba_tablespaces ;

Show number of tables in the TEMP tablespace - SHOULD be 0:


select count(*) from dba_all_tables where tablespace_name = 'TEMP' ;

MMK
MMK
consultant.mubashir@gmail.com

Invalid objects
-
select count(*) from dba_objects where status='INVALID';

Check CPU Usage by USERNAME, SID, SERIAL#, cpu usage (seconds)


SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN username FORMAT A30


COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000

SELECT
s.username,
t.sid,
s.serial#,
SUM(VALUE/100) as "cpu usage (seconds)"
FROM
v$session s,
v$sesstat t,
v$statname n
WHERE
t.STATISTIC# = n.STATISTIC#
AND
NAME like '%CPU used by this session%'
AND
t.SID = s.SID
AND
s.status='ACTIVE'
AND
s.username is not null
GROUP BY username,t.sid,s.serial#
/

MMK
MMK
consultant.mubashir@gmail.com

DB SIZE
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Check memory components


select component, current_size, max_size, granule_size, last_oper_type from
v$memory_dynamic_components;

select component, current_size, max_size from v$memory_dynamic_components;

select * from V$MEMORY_RESIZE_OPS

MMK
MMK
consultant.mubashir@gmail.com

The following query may give you the list of running SQL (SQL_ID) from Active
Sessions:
col program format a20
col module format a20
set pages 133
select inst_id, program, module, SQL_ID, machine from gv$session where type!='BACKGROUND'and
status='ACTIVE' and sql_id is not null and sid=1610;

col program format a20


col module format a20
set pages 133
select inst_id,sid,serial#, program, module, SQL_ID from gv$session where type!='BACKGROUND'and
status='ACTIVE' and sql_id='6kvzmdavp2n8s';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'


FROM gv$session
where type!='BACKGROUND'and status='ACTIVE' and sql_id='1pgq96na3v93d';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'


FROM gv$session
where type!='BACKGROUND'and status='INACTIVE'; and sql_id='8zvr4a5n3q39a';

1njxahwsws06f
1pgq96na3v93d

col program format a20


col module format a20
set pages 133
select program, module, SQL_ID, machine from v$session where type!='BACKGROUND'and
status='INACTIVE' and sql_id is not null;

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine,s.status, p.terminal, s.program FROM
v$session s, v$process p;
select owner,tablespace_name, table_name from dba_tables where
table_name='XXCA_PREQ_WEBADI_T2';
select status,index_name from dba_indexes where table_name = '%$';

select owner,tablespace_name, table_name from dba_tables where


tablespace_name='APPS_TS_TX_DATA';

MMK
MMK
consultant.mubashir@gmail.com

set linesize 90
set pagesize 60
COLUMN SU FORMAT A8 HEADING 'ORACLE|USER ID' JUSTIFY LEFT
COLUMN OSU FORMAT A8 HEADING 'SYSTEM|USER ID' JUSTIFY LEFT
COLUMN STAT FORMAT A8 HEADING 'SESSION|STATUS' JUSTIFY LEFT
COLUMN SSID FORMAT 999999 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
COLUMN SSER FORMAT 999999 HEADING 'ORACLE|SERIAL|NO' JUSTIFY RIGHT
COLUMN SPID FORMAT A9 HEADING 'ORACLE|SESSION|ID' JUSTIFY RIGHT
COLUMN TXT FORMAT A25 HEADING 'CURRENT STATEMENT' JUSTIFY CENTER WORD
COLUMN LOGTIME FORMAT A10 HEADING 'LOGIN|TIME' JUSTIFY RIGHT

SELECT
S.USERNAME SU,
S.OSUSER OSU,
to_char(S.LOGON_TIME,'MM-DD-YYYY HH24:MI:SS') LOGTIME,
S.STATUS STAT,
S.SID SSID,
S.SERIAL# SSER,
LPAD(P.SPID,9) SPID,
SUBSTR(SA.SQL_TEXT,1,540) TXT
FROM V$PROCESS P,
V$SESSION S,
V$SQLAREA SA
WHERE P.ADDR=S.PADDR
AND S.USERNAME IS NOT NULL
AND S.SQL_ADDRESS=SA.ADDRESS (+)
AND S.SQL_HASH_VALUE=SA.HASH_VALUE (+)
ORDER BY 1,3,6;

select blocking_session, sid, serial#, wait_class,seconds_in_wait from gv$session where


blocking_session is not NULL order by blocking_session;

select sid,blocking_session,username,sql_id,event,machine,osuser,program,wait_class from v$session


where blocking_session > 0;

select sid,serial#,blocking_session,username,event,program,wait_class from gv$session where


blocking_session > 0;

select sid,serial#,blocking_session,username,event,program,wait_class from v$session where


blocking_session > 0;

MMK
MMK
consultant.mubashir@gmail.com

Query 1: For checking the locks in concurrent jobs


SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM
gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY
id1,request;

Find out the PID using SID and SID using PID
select sid from v$session where paddr in ( select addr from v$process where spid=8663);
select spid from v$process where addr in (select paddr from v$session where sid = 919);

Find Inactive Sessions and kill them


select
s.status,
count(1),
s.username
from
gv$process p,
gv$session s
where
paddr(+)=addr
group by
s.status,
s.username
order by 1;

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||''''||' immediate;'


FROM gv$session
WHERE status ='INACTIVE';

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||''''||' immediate;'


FROM v$session
WHERE status ='INACTIVE'

Find SQL TEXT using the SID


select a.sid,a.program,b.sql_text
from gv$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sid=610
order by a.sid,hash_value,piece;

MMK
MMK
consultant.mubashir@gmail.com

SELECT * FROM dba_blockers;

SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION where sid=667;

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program FROM v$session s,
v$process p WHERE s.paddr = p.addr and s.program='w3wp.exe';
SELECT s.sid, s.serial#, s.username, s.osuser, s.program FROM v$session s, v$process p WHERE s.paddr =
p.addr and s.program='w3wp.exe';

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program FROM v$session s,
v$process p WHERE s.paddr = p.addr and p.pid=310;

select s.sid, s.serial#, s.username, s.osuser,s.program FROM v$session s, v$process p WHERE s.paddr =
p.addr and s.program='w3wp.exe';

set lines 133


col wait_class format a20
col program format a10
select s.sid, s.serial#,s.inst_id, s.username, s.osuser,s.program,s.wait_class,SECONDS_IN_WAIT FROM
gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid=&sid;

set lines 133


col wait_class format a20
col program format a10
select s.sid, s.serial#, s.username, s.osuser,s.program,s.wait_class FROM v$session s, v$process p
WHERE s.paddr = p.addr and s.sid=161;

select s.sid, s.serial#, s.username, s.osuser,s.program,s.wait_class FROM v$session s, v$process p


WHERE s.paddr = p.addr;

select sid,serial#,program,wait_class from v$session where program='w3wp.exe';

select sid,serial#,ELAPSED_SECONDS,START_TIME,last_update_time from v$session_longops where


elapsed_seconds > 1000 and sid=772;

SID SERIAL# ELAPSED_SECONDS


---------- ---------- ---------------------------
1054 5877 6429
1664 4337 1316
84 6101 2051
837 8013 4270

MMK
MMK
consultant.mubashir@gmail.com

Find the UPTIME of the database

select
'Hostname : ' || host_name
,'Instance Name : ' || instance_name
,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
trunc( 24*((sysdate-startup_time) -
trunc(sysdate-startup_time))) || ' hour(s) ' ||
mod(trunc(1440*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
mod(trunc(86400*((sysdate-startup_time) -
trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
sys.v_$instance;

select count(*) from v$session where username is not null;

select count(*),inst_id from gv$session where username is not null group by inst_id;

The script below will display a status message that shows the current amount of
time that has been used by long-running operations, include the associated SQL
from the v$sql view.
select
l.sid,
l.sofar,
l.totalwork,
l.start_time,
l.last_update_time,
s.sql_text
from v$session_longops l left outer join v$sql s on s.hash_value = l.sql_hash_value and s.address =
l.sql_address and s.child_number = 0;

select * from dba_waiters;

select owner,object_name,object_type from dba_objects where object_id in (select object_id from


v$locked_object where session_id=132 and locked_mode =3); (Heavy query)

MMK
MMK
consultant.mubashir@gmail.com

Friendly query for who is blocking who


Mostly for versions before v$session had blocking_session column
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;

find blocking sessions that were blocking for more than 15 minutes + objects
and sql
select s.SID,p.SPID,s.machine,s.username,CTIME/60 as minutes_locking, do.object_name as
locked_object, q.sql_text
from v$lock l
join v$session s on l.sid=s.sid
join v$process p on p.addr = s.paddr
join v$locked_object lo on l.SID = lo.SESSION_ID
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID
join v$sqlarea q on s.sql_hash_value = q.hash_value and s.sql_address = q.address
where block=1 and ctime/60>15

MMK

You might also like