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;