ORACLE DBA BLOG

 1) Basic information of database (primary or standby) SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; 2) Check for messages/errors SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; 3) To display current status information for specific physical standby database background processes. SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; 4) Show received archived logs on physical standby -Run this query on physical standby SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log; 5) To check the log status  select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES') union select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v...

set echo off set linesize 95 set head on set feedback on col sid head "Sid" form 9999 trunc col serial # form 99999 trunc head "Ser#" col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head "Client|Machine" col program form a15 trunc head "Client|Program" col login form a11 col "last call"    form 9999999 trunc head "Last Call|In Secs" col status form a6 trunc select sid , serial #,substr(username,1,10) username,substr(osuser,1,10) osuser, substr ( program || module , 1 , 15 ) program , substr ( machine , 1 , 22 ) machine , to_char ( logon_time , 'ddMon hh24:mi' ) login , last_call _ et "last call" , status from v $ session where status = 'ACTIVE' order by 1 /

col sql_text form a80 set lines 120 select sql_text from gv $ sqltext where hash_value = ( select sql_hash_value from gv $ session where sid =& 1 ) order by piece /  

Top 10 cached sql statements  SELECT sql_id , child_number , sql_text , elapsed_time    FROM ( SELECT sql_id , child_number , sql_text , elapsed_time , cpu_time ,                disk_reads ,                RANK ( ) OVER ( ORDER BY elapsed_time DESC ) AS elapsed_rank            FROM v $ sql ) WHERE elapsed_rank <= 5  

 V$SQL_PLAN_MONITOR displays real time plan level monitoring statistics for the currently running sql queries.. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored.  -- - sql_is from v $ sql_monitor   SELECT sql_id FROM    v $ sql_monitor ;   SQL_ID -- -- -- - 00tr6c6tngp4x SELECT sid , sql_id , status , plan_line_id , plan_operation || ' ' || plan_options operation , output_rows FROM v $ sql_plan_monitor WHERE status not like '%DONE%' ORDER BY 1 , 4 ;            SID SQL_ID         STATUS       LINE OPERATION                       ROWS -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- -- -- - -- -- -- -- -- -- -- -- -- -- -- -- - -...