-- =========================================================
-- Database Information Report
-- Author  : IFSTechServices.com
-- Purpose : Generate HTML report with DB information
-- =========================================================

SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET PAGESIZE 50000
--SET LINESIZE 200

SET MARKUP HTML ON SPOOL ON TABLE ON PREFORMAT OFF ENTMAP OFF


SPOOL Database_Information_Report.html
--PROMPT <div class="section-header"><H2><B>DATABASE INFORMATION<B></H2></div>

--PROMPT <h1 style="background:purple; color:white; padding:8px; width:max-content;">     DATABASE REPORT</h1>
set head off
SELECT
    '<h1 style=color:purple; padding:8px; width:max-content;"><b>
        Database Information Report Generated On :  ' ||
        TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||
    '</b></h1>' AS html_output
FROM dual;
set head on


--Database and Instance Last start time:
SET MARKUP HTML ON TABLE "border='1' cellpadding='5' cellspacing='0'"

PROMPT <div class="section-header"><h2 style=color:purple; padding:8px; width:max-content;><b>Database Startup Information</h2></b></div>

SELECT
  TO_CHAR(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "Startup Time",
  ROUND((SYSDATE - startup_time),2)             AS "Days Up",
  ROUND((SYSDATE - startup_time)*24,2)          AS "Hours Up"
FROM v$instance;




PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Initial Database information</B></h2>
--Track Database Version:
SELECT banner_full  "Database Edition, Release and Version" from v$version;
 

-- Track Database Name and ID information:
SELECT dbid, name "Database Name", Platform_name as "Platform" , Log_mode as "Archive" , 
       CASE cdb 
            WHEN 'YES' THEN 'CONTAINER DATABASE'
            WHEN 'NO'  THEN 'NON-CDB'
       END AS "Container"
 FROM v$database; 

 
-- Track Database Global Name information:

SELECT name   AS "Instance/PDB Name",
       open_mode AS "Open Mode"
FROM v$pdbs;

 

--Track Database Host Details:

SELECT substr(UTL_INADDR.GET_HOST_ADDRESS,1,15) "DB Server IP",upper( substr(UTL_INADDR.GET_HOST_NAME,1,10)) "DB  Host" FROM DUAL;

-- Track Database Present Status:
SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;

--DB Character Set Information:
Select * from nls_database_parameters;

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Storage and Tablespaces</b></h2>
--Track Database default information:
Select username, profile, default_tablespace, temporary_tablespace from dba_users;

-- Track Total Size of Database:
select a.data_size+b.temp_size+c.redo_size "Total Databse Size (GB)"
from ( select sum(bytes/1024/1024/1024) data_size
         from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size
         from dba_temp_files ) b, ( select sum(bytes/1024/1024/1024) redo_size
         from sys.v_$log ) c;
		 
-- Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;
	  
--Track Database Structure:
select name "Control File" from   sys.v_$controlfile;

select group#,member from   sys.v_$logfile order by group#;
Select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte,
       decode(F.status,'AVAILABLE','OK',F.status) status, F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name;
 
--Tablespace/Datafile/Temp/UNDO Information:

--Track Tablespace Used/Free Space:
SELECT /* + RULE */  df.tablespace_name "Tablespace",  df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;




-- Track all Tablespaces with free space < 10%
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;


--Track Tablespace Fragmentation Details:
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
     from  ( select tablespace_name,0 tots,sum(bytes) sumb,
     max(bytes) largest,count(*) chunks
     from dba_free_space a
     group by tablespace_name
     union
     select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files
     group by tablespace_name) a  group by a.tablespace_name
order by pct_free;
 

 
-- Track Non-Sys owned tables in SYSTEM Tablespace:
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');

 
-- Track Default and Temporary Tablespace:
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';

--for Default tablespace for All Users
Select username, default_tablespace,temporary_tablespace from dba_users;  

-- Track DB datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) -  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
 /***
-- Track Datafile with Archive Details:
SELECT NAME, a.status, DECODE (b.status, 'Active', 'Backup', 'Normal') arc, enabled, bytes/1024/1024 as "Size MB", change#, TIME ARCHIVE
 FROM sys.v_$datafile a, sys.v_$backup b 
 WHERE a.file# = b.file#;
 ***/

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Datafiles with highest I/O activities</b></h2>
 
--Track Datafiles with highest I/O activity:
Select * from (select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum <6;
---------
--Track Datafile as per the Physical Read/Write Percentage:
PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Track Datafile as per the Physical Read/Write Percentage</b></h2>
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT   NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct, phywrts, phywrts * 100 / trw.phys_wrts write_pct FROM totreadwrite trw, v$datafile df, v$filestat fs WHERE df.file# = fs.file# ORDER BY phyrds DESC;

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Checking  Autoextend ON/OFF for Datafile</b></h2>
--Checking  Autoextend ON/OFF for Datafile:
select tablespace_name, substr(file_name,1,50) as "File Name", AUTOEXTENSIBLE as "ON" from dba_data_files;

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Temp Space</b></h2>
--More on Tablespace/Datafile size 
SELECT tablespace_name as "Tablespace Name", SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header
GROUP  BY tablespace_name;

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM  v$sort_segment A, (SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

SELECT
    b.tablespace,
    ROUND(b.blocks * p.value / 1024 / 1024, 2) AS temp_mb,
    a.sid || ',' || a.serial# AS sid_serial,
    a.username,
    a.program
FROM v$sort_usage b
JOIN v$session a
  ON a.saddr = b.session_addr
JOIN v$parameter p
  ON p.name = 'db_block_size'
ORDER BY temp_mb DESC;

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Archive Log Status</b></h2>
SELECT log_mode FROM v$database;
SELECT sequence#, applied, completion_time
FROM v$archived_log
WHERE rownum < 10
ORDER BY sequence# DESC;

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Memory Usage</b></h2>
SELECT name, round(value/1024/1024,2) AS "Size (MB)"
FROM v$sga;
SELECT name, value/1024/1024 AS "Size (MB)"
FROM v$pgastat
WHERE name IN ('total PGA inuse','total PGA allocated');

PROMPT <h2 style=color:purple; padding:8px; width:max-content;><b>Sessions and Connections</b></h2>
SELECT username, status, COUNT(*) AS "Session Count"
FROM v$session
WHERE username IS NOT NULL
GROUP BY username, status;


PROMPT <div class="footer">Report generated by ifstechservices.com</div>

SPOOL OFF
SET MARKUP HTML OFF
SET TERMOUT ON