Frequently Used Queries for Oracle DBA

Check Database Status

col name for a8 
col open_mode for a13 
col status for a8 
col logins for a8 
col host_name for a20 
set lines 500 
alter session set NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss'; 
select name,host_name, open_mode, status, logins, startup_time from gv$instance, v$database;

Check Oracle 12c PDB Database Status

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

Check Database Size

select
    ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
     ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
    ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
    from dual;

Check Invalid Objects

set lines 200
set pages 999
col OBJECT_NAME for a40
select owner, object_name, object_type, status from dba_objects where status like 'INVALID';

Check RMAN Backup Status 

set lines 300 pages 500
col command_id format a25
col status format a35
col start_time format a25
col time_taken_display format a25
col INPUT_TYPE format a20
col output_device_type format a50
SELECT   b.command_id,b.status, to_char(b.start_time, 'MM-DD-YYYY HH24:MI') "Start_TIME", b.time_taken_display,b.input_type, b.output_device_type
FROM v$rman_backup_job_details b
WHERE (b.start_time > (SYSDATE - 20)) order by 3 desc;

Check Oracle Compononets Status 

set lines 200
set pages 999 
col comp_name for a40
select COMP_NAME, VERSION, STATUS, MODIFIED from dba_registry;

Check Table/Index/Segment Size

set lines 200
col segment_name for a20
select owner, segment_name, segment_type, bytes/1024/1024 "Size" from dba_segments where segment_name like 'TABLE_NAME';

Check Tablespace Usage

set pages 999 lines 999
column Tablespace_Name format A30
column "Free %" format 999.99
column "Largest Chunk" format 9999.999
column "Free Space" format 9,999,999.999 
column "Tot Space" format 9,999,999.999
column auto_mb_left hea 'Auto_MB_Left' format 9,999,999.999
compute sum of "Free Space" on report
compute sum of "Tot Space" on report
break on report
ttitle off
select Tablespace_Name,
        nvl(Sum_Alloc_Bytes,0)/(1024*1024) "Tot_Space_GB",
        nvl(Sum_Free_Bytes,0)/(1024*1024) "Free_Space_GB",
        nvl(Max_Bytes,0)/(1024*1024) "Largest Chunk",
        Count_Chunks,
        auto_mb_left,
        (100*((nvl(Sum_Free_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))/((nvl(Sum_Alloc_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))) AS "Free %"
from
        (select Tablespace_Name, SUM(Bytes) Sum_Alloc_Bytes
                from DBA_DATA_FILES
                group by Tablespace_Name),
        (select Tablespace_Name FS_TS_NAME,
                MAX(Bytes)  AS Max_Bytes,
                COUNT(Bytes)  AS Count_Chunks,
                SUM(Bytes) AS Sum_Free_Bytes
                from DBA_FREE_SPACE
                group by Tablespace_Name),
        ( SELECT d.tablespace_name auto_tbspname,
                floor(sum(decode(sign( ((d.maxbytes-d.bytes)/1024/1024) - (d.increment_by*to_number(p.value)/1024/1024) ),
                        -1,0,((d.maxbytes-d.bytes)/1024/1024)))) auto_MB_Left
          FROM dba_data_files d,  v$parameter p
          WHERE p.name='db_block_size'
            and d.autoextensible='YES'
          GROUP BY d.tablespace_name) b
where Tablespace_Name = FS_TS_NAME (+)
and Tablespace_name = auto_tbspname (+)
order by 7 desc;