Frequently Used Oracle DBA Commands

******************************

Command to find sql from sql id:

select sql_text from v$sqltext where sql_id='9rfvrjph5vun2' order by PIECE ;

*******************************

Command to find space usage in a tablespace:

undef ts_name
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files where tablespace_name = '&&TS_NAME' group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space where tablespace_name = '&TS_NAME' group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

***************************************

Command to find datafiles in a specific tablespace:

set lines 300
col file_name for a50
select file_id,file_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&TS_NAME';

****************************************

Command to find currently running long operations:

set lines 300
set pages 2000
col message for a50
select sid,serial#,message,sofar,totalwork,(sofar/totalwork)*100 "pct",ELAPSED_SECONDS/60/60,TIME_REMAINING/60/60 from v$session_longops where TIME_REMAINING<>0 and sofar<>totalwork ;

***************************************

Command to gather statistics: (schema level)

exec DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

******************************************

Command to check backup availability:

set feedback off
col Name format a14
col BACKUP_TYPE format a11
col OPERATION format a15
col START_TIME format a22
set line 150

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY  HH24:MI:SS';

select a.Name ,COMMAND_ID  ,OPERATION,STATUS,MBYTES_PROCESSED,START_TIME,END_TIME from  V$RMAN_STATUS , v$database a where OBJECT_TYPE='DB FULL' and OPERATION='BACKUP'  and trunc(START_TIME)>trunc(sysdate)-1

********************************************

Command to get os process related to sid:

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr and p.spid=15107
/

***************************************
Script to compress exp dmp file on the fly:

Use the following technique if you use an Oracle version prior to 8i:

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.

# create a named pipe
mknod exp.pipe p
# read the pipe - output to zip file in the background
gzip < exp.pipe > scott.exp.gz &
# feed the pipe
exp userid=scott/tiger file=exp.pipe ...

Import directly from a compressed export:

# create a  name pipe
mknod imp_pipe p
# read the zip file and output to pipe
gunzip < exp_file.dmp.gz > imp_pipe &
# feed the pipe
imp system/pwd@sid file=imp_pipe log=imp_pipe.log ...

In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it:

imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log ...

***********************************
Command to get redo log count hourly:

col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999

select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

*************************************

Command to find segments causing redo log  generaion:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
 dhso.object_name,
 sum(db_block_changes_delta) BLOCK_CHANGED
 FROM dba_hist_seg_stat dhss,
 dba_hist_seg_stat_obj dhso,
 dba_hist_snapshot dhs
 WHERE dhs.snap_id = dhss.snap_id
 AND dhs.instance_number = dhss.instance_number
 AND dhss.obj# = dhso.obj#
 AND dhss.dataobj# = dhso.dataobj#
 AND begin_interval_time BETWEEN to_date('12-02-12 08:00','YY-MM-DD HH24:MI')
 AND to_date('12-02-13 08:00','YY-MM-DD HH24:MI')
 GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
 dhso.object_name
 HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;

***********************************
To find process utilization hourly:

select to_char(BEGIN_TIME,'dd-mon-yyyy hh24:mi:ss') begin_time, to_char(END_TIME,'dd-mon-yyyy hh24:mi:ss') end_time, MAXVAL*5000/100 max_process_count, ROUND(MAXVAL,2) "limit_reached (%age)"
from dba_hist_sysmetric_summary
where trunc(end_time) = '15-MAR-2013' and metric_id = 2118
order by 1;

*************************************

Command to find used and free memory:

select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot
/

**************************************
Note - The actual PGA usage might hit and go above those limits. The current allocation can be queried.

Command to find current allocation of sga and pga:

select round(sum(bytes)/1024/1024/1024,3) SGA_G
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat
 where name = 'total PGA allocated';

 *************************************
Command to find current Open cursor count:
 select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
 and b.name = 'opened cursors current'
and p.name= 'open_cursors'
 group by p.value;

*****************************************
Command to find current utilization of processes and sessions:
set lines 300
select * from v$resource_limit;

*************************************
Command to find CPU usage for active sessions:
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#
/

************************************
Command to check services in RAC in tabular format:
cd <CRS_HOME>/bin

./crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-75s%-18s%-15s%-30s\n", n,t,g,s)}'

************************************
To check histogram on a table:
select distinct histogram from dba_tab_col_statistics where table_name = '<table_name>';
************************************
To check resources in a database:
set lines 1000
set pages 1000
select * from v$resource_limit;

In RAC environment, below statement gives PROCESSES and SESSIONS information all all instances.
set lines 1000
set pages 1000
select * from gv$resource_limit where RESOURCE_NAME in ('processes','sessions');
************************************
Find blocking sessions with v$session:
set lines 300
set pages 300
SELECT inst_id,
   s.blocking_session,
   s.sid,
   s.serial#, username,status,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;

Find blocking sessions using v$lock:
SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
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;

Command to identify locked tables:
set lines 180
col object_name for a40
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
    a.object_id = c.object_id;


select a.sid, a.serial#,sql_id ,(case LOCKED_MODE   when 0 then 'none'
  when 1 then  'null'
   when  2 then 'row-S'
  when 3 then 'row-x'
  when 4 then 'share'
  when 5 then 'S/Row-X'
  when  6 then 'exclusive'
   end ) LOCKED_MODE
    from v$session a, v$locked_object b, dba_objects c
  where b.object_id = c.object_id
  and a.sid = b.session_id
   and OBJECT_NAME='XYS';


Command to identify blocked objects:
SELECT sid, id1 FROM v$lock WHERE TYPE='TM';

****************************
To check wait events on a particular SID:
select SID,EVENT,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where sid=<sid>;

To check wait events on all sessions:
set lines 300
select a.SID,b.serial#,b.username,b.status,b.sql_id, a.EVENT,a.WAIT_TIME,a.SECONDS_IN_WAIT,a.STATE from v$session_wait a,v$session b where a.sid=b.sid ;

****************************
To find complete query using sql id:
select sql_text from v$sqltext where sql_id='2vgdsq0w31qgy' order by PIECE ;
****************************
To check CPU usage by each ACTIVE session:
set pages 1000
set lines 300
select
   ss.username,
   se.SID,ss.serial#,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc ;


Select username,v.sid||','||v.serial# sid_session,round((s.value/y.value)*100,2) cpu_used_percentage
from v$session v, v$sesstat s,v$sysstat y
where v.username is not null
and v.sid = s.sid
and s.statistic#=y.statistic#
and y. name = 'CPU used by this session'
Order by username,3 desc ;

*********************************
To get the information of min and max time of ACTIVE SESSION HISTORY:
select min(sample_time), max(sample_time) from sys.WRH$_ACTIVE_SESSION_HISTORY ;
select min(sample_time), max(sample_time) from v$ACTIVE_SESSION_HISTORY ;

*********************************
To get the DDL of a procedure:
SELECT dbms_metadata.get_ddl('PROCOBJ','YOURJOBNAME', 'YOURJOBOWNER') from dual;

*********************************
Commands to reset a sequence value:
ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM foo;
ALTER SEQUENCE serial INCREMENT BY 1;


To get the IO of each session:
set pagesize 9999
set linesize 140
column username  format a10
column sid       format 9999
column serial#   format 99999
column actv      format a2
column sess_mi   format 9,999.9
column last      format 9,999.99
column TotGets   format 999,999.999
column phyRds    format 999,999
column "KBSnt"   format 9,999,999
column "Trips/s" format 999
column "KbSnt/s" format 99,999
column Trips     format 99,999,999
column "B/trip"  format 9,999,999
column hit_rat   format 9,999

SELECT a.username, a.sid, a.serial#,
       decode(a.status, 'ACTIVE', 'Y','INACTIVE', 'N') actv,
       (sysdate-logon_time)*24*60 sess_mi,
       a.last_call_et/60 "last",
       c.value/1024 "KBSnt",
       e.value "Trips",
       e.value / ((sysdate - logon_time)*24*60*60) "Trips/s",
       ((8*c.value/1024)/((sysdate - logon_time)*24*60*60)) "KbSnt/s",
       c.value/e.value "B/trip",
       b.block_gets + b.consistent_gets TotGets,
       b.physical_reads phyRds,
       1-(physical_reads/(b.block_gets + b.consistent_gets)) hit_rat
FROM v$session a, v$sess_io b, v$sesstat c, v$statname d, v$sesstat e, v$statname f
WHERE a.sid = b.sid
AND a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = 'bytes sent via SQL*Net to client'
AND a.sid = e.sid
AND e.statistic# = f.statistic#
AND f.name = 'SQL*Net roundtrips to/from client'
AND a.username is not null
AND (a.last_call_et < 3600 or a.status = 'ACTIVE')
AND sysdate - logon_time > 0
AND a.username != 'SYS'
AND (b.block_gets + b.consistent_gets) > 0
ORDER BY username
/