DBA useful scripts

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);