Use below query to check all the space related details of tablespaces.   set feedback off set pagesize 70; set linesize 2000 set head on COLUMN Tablespace format a25 heading ‘Tablespace Name’ COLUMN autoextensible format a11 heading ‘AutoExtend’ COLUMN files_in_tablespace format 999 heading ‘Files’ COLUMN total_tablespace_space format 99999999 heading ‘TotalSpace’ COLUMN total_used_space format 99999999 heading […]

Use the below query to find the applied archive log gap between primary and standby oracle database. select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);  

Use below script to get the details about undo tablespace usage.   select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB – USAGEMB) FREEMB from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = ‘UNDO’ group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB from […]

Run the below scripts to get the temp tablespace usage. select a.tablespace_name tablespace, d.TEMP_TOTAL_MB, sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB, d.TEMP_TOTAL_MB – sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB from v$sort_segment a, ( select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB from v$tablespace b, v$tempfile c where b.ts#= […]

Use the below query to monitor the currently running rman backup status. SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE” FROM V$SESSION_LONGOPS WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Below query will results the current wait event details in oracle database. select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser, substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine, a.event,a.p1,b.sql_hash_value from v$session_wait a,V$session b where b.sid=a.sid and a.event not in(‘SQL*Net message from client’,’SQL*Net message to client’, ‘smon timer’,’pmon timer’) and username is not null order by 6 /  

Below query will show the current sessions undo usage.   select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks from v$session a, v$transaction b where a.saddr=b.ses_addr ;

Below query will help you in providing sessions causing lock in the database.   col session_id head ‘Sid’ form 9999 col object_name head “Table|Locked” form a30 col oracle_username head “Oracle|Username” form a10 truncate col os_user_name head “OS|Username” form a10 truncate col process head “Client|Process|ID” form 99999999 col mode_held form a15 select lo.session_id,lo.oracle_username,lo.os_user_name, lo.process,do.object_name, decode(lo.locked_mode,0, ‘None’,1, […]

Below query will display the the list of sessions using lot of temps including their usage. SELECT   b.TABLESPACE        , b.segfile#        , b.segblk#        , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb        , a.SID        , a.serial#        , a.username        , a.osuser        […]

We can use below sql query to find the current sessions or queries generating lot of logs   set lines 2000 set pages 1000 col sid for 99999 col name for a09 col username for a14 col PROGRAM for a21 col MODULE for a25 select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) “program”, sn.type, sn.module,sn.sql_id from v$sesstat […]