How to check the processes limit in oracle

Query :
======================

set lines 300 pages 3000
select * from gv$resource_limit where RESOURCE_NAME ='processes' order by 1;

How to know patch information in oracle

Query:
================

set lines 300 pages 300
col COMMENTS for a30
col VERSION for a20
col NAMESPACE for a20
col ACTION_TIME for a30
col ACTION for a20

select * from DBA_REGISTRY_HISTORY;

col ACTION_TIME for a30
col DESCRIPTION for a50
col BUNDLE_SERIES for a15
col BUNDLE_DATA for a5
col LOGFILE for a20
set lines 300
select PATCH_ID,VERSION,ACTION,STATUS,ACTION_TIME,DESCRIPTION,BUNDLE_SERIES,BUNDLE_ID from dba_registry_sqlpatch;

How to get the extent information in oracle

Query:
===================

set pages 3000 lines 300
col owner for a15
col segment_name for a30
col TABLESPACE_NAME for a30
select OWNER,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BYTES/1024 from dba_extents where OWNER='&OWNER' and SEGMENT_NAME='&SEGMENT_NAME' order by EXTENT_ID;

How to check RMAN backup status in oracle

Query:
==============

col INPUT_TYPE for a15
col status for a35
col START_TIME for a20
col END_TIME for a20
set lines 300 pages 3000
SELECT 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, ROUND(elapsed_seconds/60,2) elapsed_minutes
FROM ( SELECT a.start_time, a.end_time, MAX(a.end_time) OVER (PARTITION BY input_type) maxetime,
        DECODE(a.input_type, 'DB FULL', 'MERGE', 'DB INCR', 'INCREMENTAL', 'ARCHIVELOG', 'ARCHIVELOG', '') input_type, a.status, a.elapsed_seconds
        FROM V$RMAN_BACKUP_JOB_DETAILS  a
        WHERE a.input_type IN ( 'DB FULL', 'DB INCR','ARCHIVELOG' )) order by start_time;

How to check rman sessions in oracle database

Query:
=================

select b.sid, b.serial#, a.spid, b.client_info
from
 v$process a, v$session b
where
 a.addr=b.paddr and client_info
like
 'rman%';


 select b.sid, b.serial#, a.spid, b.client_info,b.program
from
 v$process a, v$session b
where
 a.addr=b.paddr and b.program
like
 'rman%';

How to check SGA components size in oracle

Query:
================

set lines 300 pages 300
select COMPONENT, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", max_size/1024/1024 "MAX_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE" from v$memory_dynamic_components;

How to check the shared pool usage in oracle

Query:
================

select sum(bytes/1024/1024) "Total_MB" from v$sgastat where pool = 'shared pool';
select sum(decode(name,'free memory',bytes))/1024/1024 "Free_MB" from v$sgastat where pool = 'shared pool';

How to find invalid object in oracle

Query:
===================

set lines 300
col owner for a20
col OBJECT_NAME for a30
select owner,object_name,object_id,object_type,status,last_ddl_time,created from dba_objects where status='INVALID' order by owner;

How to check blocking session in database

Query:
=====================

set lines 300 pages 3000
col MACHINE for a25
col USERNAME for a12
col EVENT for a30
col OSUSER for a10
col INST_ID for 9999999

select INST_ID,sid,osuser,username,MACHINE,sql_id,event, last_call_et RUN_TIME,seconds_in_wait Wait_time,blocking_session B_S,blocking_instance B_I
from gv$session
where
blocking_session is not NULL and event not like '%PX Deq%' and event not like '%read by other%' order by blocking_session;

To find Final Blocking session use below query:

======================================

select distinct FINAL_BLOCKING_SESSION from v$session where  FINAL_BLOCKING_SESSION_STATUS='VALID';

How to check component status in oracle

Query:
=================

set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS,version from dba_registry order by 4;

How to check standby lag

Query:
=====================

set time on
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi:ss') "Values"
from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);

How to check restore points in oracle

Query :
=============
set lines 300
col NAME for a50
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
 FROM V$RESTORE_POINT;