select df.tablespace_name, df.file_name, round(df.bytes/1024/1024) TotalSizeMB, nvl(round(usedBytes/1024/1024), 0) UsedMB, nvl(round(freeBytes/1024/1024), 0) AvailableMB,
    nvl(round(freeBytes/df.bytes * 100), 0) freePerc, df.autoextensible
from dba_data_files df
    left join (
        select file_id, sum(bytes) usedBytes
        from dba_extents
        group by file_id
    ) ext on df.file_id = ext.file_id
    left join (
        select file_id, sum(bytes) freeBytes
        from dba_free_space
        group by file_id
    ) free on df.file_id = free.file_id
order by df.tablespace_name, df.file_name


select a.TABLESPACE_NAME,
      (select file_name from dba_temp_files where FILE_ID=a.file_id) filen,
       (a.bytes_used)/1024/1024 "Space Used MB", 
       (a.bytes_free)/1024/1024 "Space Free MB"
FROM   V$temp_space_header a


 
Below is the monitoring scripts which gives us the details about
Total tablespace size, Used space, Free space, Largest Space in
tablespace,Percentage utilizes and Alarm mark (*) if it exceeds 90 %.
 
select  tbs.tablespace_name,
        tot.bytes/1024/1024 total,
        tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used,
        sum(nvl(fre.bytes,0))/1024/1024 free,
        max(fre.bytes/1024/1024) Largest_Space,
        (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
        decode(
            greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
            90, '', '*'
        ) pct_warn
from    dba_free_space fre,
        (select tablespace_name, sum(bytes) bytes
        from    dba_data_files
        group by tablespace_name) tot,
        dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and     fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes
order by 6 desc;
____________________________________________________

To check the Uptime of the Database
 
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
 
____________________________________________________
 
To check UNDO Space Status
 
elect status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;
 
____________________________________________________
  
To find the used and free space in Temporary Tablespace
 
SELECT tablespace_name, SUM(bytes_used)/1024/1024 "Space Used MB", SUM(bytes_free)/1024/1024 "Space Free MB"
FROM   V$temp_space_header
GROUP  BY tablespace_name;
 
____________________________________________________


Flash Recovery


SELECT NAME, SPACE_LIMIT/1024/1024,SPACE_USED/1024/1024 FROM V$RECOVERY_FILE_DEST;

after manually deleting the archive log files

CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

then check the space utilization in the FRA space.


___________________________________________________
 
 
 
To check the status of synchronization between Production and Standby Database
 
conn <username>/<password>@<TNS-Name of Production Database>
select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG
from
(select name||' (PROD) -> ' name from v$database) a,
(select max(sequence#) lh from v$log_history) b,
(select max(sequence#) al from v$archived_log) c;

conn sys/<password>@<TNS-Name of DR Database> as sysdba
select a.name,a.open_mode,log_mode,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "DATE"  from v$database a,dual;
select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG
from
(select name||' (DR) -> ' name from v$database) a,
(select max(sequence#) lh from v$log_history) b,
(select max(sequence#) al from v$archived_log) c;
 
Note :
Sequence# from V$log_history gives us the archived log that has been applied to the database
Sequence# from V$archived_log gives us the archived log that has been registered and copied to the DR Database
 
____________________________________________________
 
To get information about the Database
 
Control Files
 
SELECT Substr(c.name,1,60) "Controlfile",
       NVL(c.status,'UNKNOWN') "Status"
FROM   v$controlfile c
ORDER BY 1;
 
Datafiles
 
SELECT Substr(d.name,1,60) "Datafile",
       NVL(d.status,'UNKNOWN') "Status",
       d.enabled "Enabled",
       LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM   v$datafile d
ORDER BY 1;
 
Tempfiles
 
select a.TABLESPACE_NAME,
      (select file_name from dba_temp_files where FILE_ID=a.file_id) filen,
       (a.bytes_used)/1024/1024 "Space Used MB",
       (a.bytes_free)/1024/1024 "Space Free MB"
FROM   V$temp_space_header a
 
Log Files
 
select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs
from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1

 
____________________________________________________

Client IP address
select sys_context('USERENV','IP_ADDRESS') from dual;

Server IP address
select utl_inaddr.get_host_address from dual;

____________________________________________________

 
To Check locks on the database.
 
select session_id "sid",SERIAL#  "Serial",
substr(object_name,1,20) "Object",
  substr(os_user_name,1,10) "Terminal",
  substr(oracle_username,1,10) "Locker",
  nvl(lockwait,'active') "Wait",
  decode(locked_mode,
    2, 'row share',
    3, 'row exclusive',
    4, 'share',
    5, 'share row exclusive',
    6, 'exclusive',  'unknown') "Lockmode",
  OBJECT_TYPE "Type"
FROM
  SYS.V_$LOCKED_OBJECT A,
  SYS.DBA_OBJECTS B,
  SYS.V_$SESSION c
WHERE
  A.OBJECT_ID = B.OBJECT_ID AND
  C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/
 
 
 
____________________________________________________
 
To check Memory utilization by connected sessions.
 
SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
       a.module,
       a.program,
       Trunc(b.value/1024) AS memory_kb
FROM   v$session a,
       v$sesstat b,
       v$statname c
WHERE  a.sid = b.sid
AND    b.statistic# = c.statistic#
AND    c.name = 'PGA Memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC;
 

____________________________________________________
 
To check the status of the Redo log files

col member for a30
set lines 120
col status for a10
select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs
from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;
 

____________________________________________________
 
To SID of Particular resouce consuming PID
 
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
 where b.paddr = a.addr and type='USER' and a.spid=<PID No> order by spid;
____________________________________________________
 
 
To Check Activity of a SID
 
--Begin--
 
set serveroutput on size 50000
set echo off feed off veri off
accept SID prompt 'Enter Oracle SID: '
DECLARE
  v_sid number;
  s sys.v_$session%ROWTYPE;
  p sys.v_$process%ROWTYPE;
BEGIN
  begin
    select sid into v_sid
    from   sys.v_$session s
    where  sid    = &&SID;
  exception
    when no_data_found then
      dbms_output.put_line('Unable to find SID &&SID!!!');
      return;
    when others then
      dbms_output.put_line(sqlerrm||'naaaaaaaaaaaaaaaaaaaaa');
      return;
  end;
  select * into s from sys.v_$session where sid  = v_sid;
  select * into p from sys.v_$process where addr = s.paddr;
  dbms_output.put_line('=====================================================================');
  dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);
  dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);
  dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);
  dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);
  dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);
  dbms_output.put_line('Ora User    : '|| s.username);
  dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
  dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
  dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
  dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min');
  dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
  dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));
  dbms_output.put_line('Current SQL statement:');
  for c1 in ( select * from sys.v_$sqltext
              where HASH_VALUE = s.sql_hash_value order by piece) loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
  dbms_output.put_line('Previous SQL statement:');
  for c1 in ( select * from sys.v_$sqltext
              where HASH_VALUE = s.prev_hash_value order by piece) loop
    dbms_output.put_line(chr(9)||c1.sql_text);
  end loop;
  dbms_output.put_line('Session Waits:');
  for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
    dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
  end loop;
  dbms_output.put_line('Connect Info:');
  for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
  end loop;
  dbms_output.put_line('Locks:');
  for c1 in ( select
          decode(l.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||l.type) type,
       decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                       4, 'S',    5, 'RSX',  6, 'X',
                       to_char(l.lmode) ) lmode,
       decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
                         4, 'S', 5, 'RSX', 6, 'X',
                         to_char(l.request) ) lrequest,
       decode(l.type, 'MR', o.object_name,
                      'TD', o.object_name,
                      'TM', o.object_name,
                      'RW', 'FILE#='||substr(l.id1,1,3)||
                            ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
                      'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
                      'WL', 'REDO LOG FILE#='||l.id1,
                      'RT', 'THREAD='||l.id1,
                      'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
                      'ID1='||l.id1||' ID2='||l.id2) objname
       from  sys.v_$lock l, dba_objects o
       where sid   = s.sid
         and l.id1 = o.object_id(+) ) loop
    dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
  end loop;
  dbms_output.put_line('=====================================================================');
END;
/
undef SID
=====END===