Some Useful Oracle Commands

Currently running Jobs in Oracle
SELECT sid, r.job, log_user, r.this_date, r.this_sec
FROM dba_jobs_running r, dba_jobs j
WHERE r.job = j.job
/

Lists all of the supported INIT.ORA parameters in Oracle

SELECT a.ksppinm name, b.ksppstvl VALUE, b.ksppstdf isdefault,
       DECODE(a.ksppity, 1, ‘boolean’, 2, ‘string’, 3, ‘number’, 4, ‘file’,
           a.ksppity) TYPE,
       a.ksppdesc description
FROM   sys.x$ksppi a, sys.x$ksppcv b
WHERE  a.indx = b.indx
  AND  a.ksppinm NOT LIKE ‘_%’ escape ”
order  by name
/

Find details of dba_data_files in Oracle

SET pages 99
col file_name format a45
col tablespace_name format a13
col tablespace_name heading ts_name
col blocks format 999,999
SELECT file_name,tablespace_name,bytes,blocks
FROM dba_data_files
ORDER BY tablespace_name,file_name
/

SQL query  to find Oracle Active Processes

SET pagesize 55;
SET linesize 170;
col SQL format a80;
col SERVER heading ‘SVR’ format a3;
col EVENT heading ‘WAITING’ format a30 fold_after;
col OSUSER heading ‘OSUSER’ format a8;
col USERNAME heading ‘USERNAME’ format a8;
col PID heading ‘OSPID’ format 99999;
col DISK_READS heading ‘DISK I/O’ format 99999999;
col BUFFER_GETS heading ‘BUFFER|GETS’ format 99999999;

SELECT  SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
     V$SESSION.OSUSER OSUSER,
–        DECODE(V$SESSION.SERVER,’DEDICATED’,'D’,'SHARED’,'S’,'O’) SERVER,
     V$SQLAREA.DISK_READS DISK_READS,
     V$SQLAREA.BUFFER_GETS BUFFER_GETS,
        SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
     V$SESSION.PROCESS PID,
     V$SESSION_WAIT.EVENT EVENT,
        V$SQLAREA.SQL_TEXT SQL
FROM    V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE   V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
     V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
        V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
     V$SESSION.STATUS = ‘ACTIVE’ AND
     V$SESSION_WAIT.EVENT != ‘client message’
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;