Useful Oracle queries for Psoft DBA

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

1. Bytes Used - Size of the file in bytes
2. Bytes Free - Size of free space in bytes
3. Largest - Largest free space in bytes
4. Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%