note: Replace v$ with gv$ if you are running on RAC
check this site. This have a lot of useful queries
http://www.oracle-base.com/dba/Scripts.php
Find blocking sessionsselect 'alter system disconnect session '''sid','serial#'''immediate;' from V$SESSION where sid in (select sid from V$LOCK where block=1);
Find current Running SQLs
select sesion.sid,
sesion.username,
sesion.machine,
sesion.program,
sesion.serial#,
0ptimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text from v$sqlarea sqlarea, v$session sesion where sesion.sql_hash_value = sqlarea.hash_value and sesion.sql_address = sqlarea.address and sesion.username is not null
Flashback table to a previous point in time
-usefull if we accidently deleted some rows
insert into SYSADM.PS_TABLE(SELECT *FROM SYSADM.PS_TABLE AS OF TIMESTAMP TO_TIMESTAMP('18-JUL-11 06.10.38.129109'))
Finding DB Character set
select value$ from sys.props$ where name='NLS_CHARACTERSET';
STATs update
on a the entire schema: EXEC DBMS_STATS.gather_schema_stats (ownname => 'SYSADM', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);
on a single table: EXEC DBMS_STATS.gather_table_stats ('SYSADM','PS_TABLE',cascade=>true)
Finding the remaining time for a long running query
select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message,elapsed_seconds*(totalwork-sofar)/sofar as time_rem
from v$session_longops
where sid =
and serial# =
order by start_time desc)
where rownum <=1;
Finding RMAN backup status
selectSESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrsfrom V$RMAN_BACKUP_JOB_DETAILSorder by session_key;
find out the impdp status of a table-how many rows got imported
select sum(bytes)/1048576
from dba_segments
where owner = 'SCOTT'
and segment_type = 'TEMPORARY'
and tablespace_name = TABSPACENAME
SGA usage
select round(sum(bytes)/1024/1024,2) total_sga,
round(sum(decode(name,'free memory',bytes,0))/1024/1024,2) free,
round((sum(decode(name,'free memory',bytes,0))/1024/1024)/(sum(bytes)/1024/1024)*100,2) free_perfromv$sgastat;
select * from ( select POOL, NAME, BYTES, BYTES/1048576 as MBytes from v$sgastat where pool='shared pool' order by BYTES desc ) where rownum <= 25;
Finding details on a peoplesoft process instance from DB
select * from PSPRCSQUE where PRCSINSTANCE=297037;
select * from PS_PRCSRQSTDIST where PRCSINSTANCE=297037;
select RUNSTATUS from PSPRCSRQST where PRCSINSTANCE=297037;
Finding total size of a Oracle DB
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB" from ( select sum(bytes)/1024/1024 data_size from dba_data_files ) a,( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) b,( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) c,( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) d;
Long Running Operations
SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
Finding the free space in a tablespace
select
a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES
bytes_free,b.largest,round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from
(select TABLESPACE_NAME,sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME) a, (select
TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest from dba_free_space group by TABLESPACE_NAME)b where
a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
the o/p will show
the following detailsTablespace Name - Name of
the tablespace
- Bytes Used - Size of the file in bytes
- Bytes Free - Size of free space in
bytes
- Largest - Largest free space in
bytes
- Percent Used - Percentage of tablespace
that is being used - Careful if it is more than 85%
