Check Database Status
col name for a8col 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 200set 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 500col 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 200set pages 999
col comp_name for a40
select COMP_NAME, VERSION, STATUS, MODIFIED from dba_registry;
Check Table/Index/Segment Size
set lines 200col 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 999column 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;
