Find blocking sessions
select '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
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#;