1 - - - - - - - - - - - - Find SPID from SID - - - - - - - - - - - - - - - - - - -
select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid = <SID>
order by s.sid;
2. - - - --- - - - Find blocking session in the database ...................
SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;
:::::::::::::::::::::::::::::::::::::::::Blocking : session with machine sid and other details:::::::::::::::::::::::::::::
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 ;
3.- - - - - - - - - - - - - - Running sql details with os user --------
set lines 1000
col object_name format a20
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
4. - - - - Find Sql text from sid and serial --- - - -
select a.sid, a.serial#, b.sql_fulltext
from v$session a, v$sql b
where decode(a.sql_id,null,a.prev_sql_id, a.sql_id)=b.sql_id
and decode(a.sql_id,null,a.prev_child_number, a.sql_child_number)=b.child_number
and a.sid=&sid
and a.serial#=&serial;
5. ====================== table_lock ===================
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
6. --- Displays the undo space currently in use by users. -------
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A30
COLUMN undoseg FORMAT A20
COLUMN undo FORMAT A20
SET LINESIZE 200
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial,
NVL(s.username, '(oracle)') AS username,
s.program,
s.osuser,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024/1024||'M' AS undo
FROM v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
7. ============== Long running session details =======================
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0 ;
8............. To determine whether any active transactions exists in UNDO segemnt.........
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time -
begin_time)*86400))) AS UPS
FROM v$undostat),
(SELECT value AS DBS
FROM v$parameter
WHERE name = 'db_block_size');
9. .................. Datafile Shrink ....................
set linesize 150
COLUMN file_name format A60
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
UNION ALL
select file_name, bytes/1024/1024 allocated_mb,user_bytes/1024/1024 used_mb,
((bytes/1024/1024) - (user_bytes/1024/1024)) free_space_mb
from dba_temp_files;
) ;
10. ............. Size of a Table ............................................
select segment_name,segment_type,bytes/1024/1024 MB from dba_segments
where segment_type='TABLE' and segment_name='<yourtablename>';
11....................... Checking ALL tablespace space usage in the database.........
col name for a25
select a.tbl "Name",a.tsz "Total Size GB",b.fsz "Free Space GB",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024/1024 TSZ from dba_data_files
where tablespace_name like '%%' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024/1024 FSZ from dba_free_space
where tablespace_name like '%%' group by tablespace_name) b
Where a.tbl=b.tblsp;
12. .... To show the list of datafiles under that particular tablespace..................
col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='<tablespace_Name>';
13. ... Top CPU Consuming SQL During A Certain Time Period ......
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
set pages 50000 lines 32767
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc);
