Export DB Links

USERID=system/xxxxxx
include=DB_link
DIRECTORY=DBLINKS_28Apr2018
dumpfile=DBLINKS_28Apr2018:DBLINKS_28Apr2018.dmp
LOGFILE=DBLINKS_28Apr2018:DBLINKS_28Apr2018.log
parallel=1
SCHEMAS='xxxxx','xxxxx','xxxxx'

Diskgroup Usage check

Query:

This query gives the information on space occupied by each directory in the diskgroup.

col gname form a10
set lines 190
set pages 10000
col dbname form a10
col file_type form a14

SELECT
    gname,
    dbname,
    file_type,
    round(SUM(space)/1024/1024) mb,
    round(SUM(space)/1024/1024/1024) gb,
    COUNT(*) "#FILES"
FROM
    (
        SELECT
            gname,
            regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
            file_type,
            space,
            aname,
            system_created,
            alias_directory
        FROM
            (
                SELECT
                    concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
                    system_created,
                    alias_directory,
                    file_type,
                    space,
                    level,
                    gname,
                    aname
                FROM
                    (
                        SELECT
                            b.name            gname,
                            a.parent_index    pindex,
                            a.name            aname,
                            a.reference_index rindex ,
                            a.system_created,
                            a.alias_directory,
                            c.type file_type,
                            c.space
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b,
                            v$asm_file c
                        WHERE
                            a.group_number = b.group_number
                        AND a.group_number = c.group_number(+)
                        AND a.file_number = c.file_number(+)
                        AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0
                AND rindex IN
                    (
                        SELECT
                            a.reference_index
                        FROM
                            v$asm_alias a,
                            v$asm_diskgroup b
                        WHERE
                            a.group_number = b.group_number
                        AND (
                                mod(a.parent_index, power(2, 24))) = 0
                             ) CONNECT BY prior rindex = pindex )
        WHERE
            NOT file_type IS NULL
            and system_created = 'Y' )
WHERE
    gname like '%&DG_NAME%'
GROUP BY
    gname,
    dbname,
    file_type
ORDER BY
   4
/

Input: DG_NAME

How to check diskgroup usage

Query:

Only for External Redundancy: 

set lines 200 pages 200
SELECT
   GROUP_NUMBER Group_Num
  ,name                                     group_name
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , free_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used FROM
    v$asm_diskgroup
where total_mb > 0
ORDER BY GROUP_NUMBER;

For External, Normal and High Redundancy:

select
GROUP_NUMBER Group_Num,
name,
round (total_mb/1024,2) TOTAL_GB,
 round (USABLE_FILE_MB/1024,2) FREE_GB,
round(USABLE_FILE_MB/total_mb*100,2) PER_FREE
from v$asm_diskgroup
ORDER BY GROUP_NUMBER;

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;