Database Info Report (Size, Up-time, Setting And Growth Reports)

Contents of this Post

1. List of Database Files
2. Up-time For Database
3. PMON Up-time Of Database
4. Database Last Restore/Recover Time
5. Database is started with spfile or pfile?
6. Default Setting Of DATABASE
7. Total Database Size + Free Space + Used Space Reports
8. Tablespace Wise Size DB Size
9. Database Growth Report (Database Wise & Tablespace Wise)
10. Growth Report Using dbms_space.OBJECT_GROWTH_TREND

List of Database Files
set lines 200 pages 200
column "DataBase Files" for a60
select 1 "Sl No",'>>>>>> DATA FILES <<<<<<' "DataBase Files" from dual
union
select 1.1,name from v$datafile
union
select 2,chr(10) from dual union select 2,'>>>>>> CONTROL FILES <<<<<<' from dual
union
select 2.1,name from v$controlfile
union
select 3,chr(10) from dual union select 3,'>>>>>> REDOLOG FILES <<<<<<' from dual
union
select 3.1,member from v$logfile
union
select 4,chr(10) from dual union select 4,'>>>>>> TEMPORARY FILES <<<<<<' from dual
union
select 4.1,NAME from v$tempfile
order by 1;

================================================================================

Up-time For Database

col "Host Name" for a20
col "Instance Name" for a30
col UPTIME for a55
select
   'Hostname : ' || host_name As "Host Name"
   ,'Instance Name : ' || instance_name "Instance Name"
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') As "DB Start Time"
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
   sys.v_$instance;

PMON Up-time
SELECT TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') "PMON Started"
FROM v$session
--where sid=1
WHERE program LIKE '%PMON%';

================================================================================

Last Restore/Recover Time
select RESETLOGS_CHANGE#
          ,to_char(RESETLOGS_TIME
          ,'DD-Mon-YYYY HH:MI:SS AM') RESETLOGS_TIME
          ,PRIOR_RESETLOGS_CHANGE#
          ,to_char(PRIOR_RESETLOGS_TIME
          ,'DD-Mon-YYYY HH:MI:SS AM') PRIOR_RESETLOGS_TIME
from V$DATABASE_INCARNATION;

================================================================================

Database is started with spfile or pfile?
col VALUE for a77
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type", value FROM sys.v_$parameter WHERE name = 'spfile';

Default Setting Of DATABASE
set lines 200 pages 200
column PROPERTY_VALUE for a30
column DESCRIPTION for a50
select * from database_properties where PROPERTY_NAME like 'DEFAULT%';

================================================================================

DATABASE SIZE

Total Database Size + Free Space + Used Space Reports:
set lines 200 pages 200
col "Total Database Size" format a20
col "Free Space (Logical)" format a20
col "Used Space (Logical)" format a20

select    round(sum(used.bytes)/1024/1024/1024,2) || ' GB' "Total Database Size",
            round(free.p/1024/1024/1024,2) || ' GB' "Free Space (Logical)",
            round(segment.use/1024/1024/1024,2) || ' GB' "Used Space (Logical)"
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,
            (SELECT SUM(bytes) use FROM sm$ts_used) segment
group by free.p,segment.use
/

Tablespace Wise Size
set lines 200 pages 200
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF AVAIL_GB USED_GB FREE_MB ON REPORT

select a.TABLESPACE_NAME, round(a.BYTES/1073741824,2) AVAIL_GB, round(b.BYTES/1073741824,2) USED_GB, round(c.BYTES/1048576,2) FREE_MB
from sm$ts_avail a, sm$ts_used b, sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=c.TABLESPACE_NAME;

CLEAR BREAKS
CLEAR COMPUTES
CLEAR COLUMNS

================================================================================

DATABASE GROWTH
Note:- This report will provide the approx value

Growth Database Wise
select     sum(z.USED_MEG)/sum(1) "Growth_per_day MB"
           ,(sum(z.USED_MEG)) "Total_Growth MB",sum(1) Total_Days_cnt
           ,min(DATE_OF_TEST)||' To '||max(DATE_OF_TEST) "Report Date From"
from       (select x.DATE_OF_TEST DATE_OF_TEST,y.TOT_MEG - x.TOT_MEG TOT_MEG
           ,y.USED_MEG-x.USED_MEG USED_MEG, y.FREE_MEG - x.FREE_MEG FREE_MEG
from       (select trunc(DATE_OF_TEST) DATE_OF_TEST,round(sum(TOT_MEG),2) TOT_MEG
           ,round(sum(USED_MEG),2) USED_MEG, round(sum(FREE_MEG),2) FREE_MEG
from       ADMIN.TABLESPACE_DETAILS
where      TABLESPACE_NAME <> 'UNDOTBS1'
group by   trunc(DATE_OF_TEST)) x
           ,(select trunc(DATE_OF_TEST) DATE_OF_TEST
           ,round(sum(TOT_MEG),2) TOT_MEG
           ,round(sum(USED_MEG),2) USED_MEG
           , round(sum(FREE_MEG),2) FREE_MEG
from       ADMIN.TABLESPACE_DETAILS
where      TABLESPACE_NAME <> 'UNDOTBS1'
group by   trunc(DATE_OF_TEST))y
where      x.DATE_OF_TEST = y.DATE_OF_TEST-1) z;


Growth Tablespace Wise (10g and Above)
select b.tsname tablespace_name
          ,MAX(b.used_size_mb) cur_used_size_mb
          ,round(AVG(inc_used_size_mb),2)avg_increas_mb
from  ( SELECT a.days,a.tsname
          ,used_size_mb
          ,used_size_mb - LAG (used_size_mb,1)
          OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
         ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu
         , dba_hist_tablespace_stat  ts
         ,dba_hist_snapshot  sp
         , dba_tablespaces  dt
where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b
GROUP BY b.tsname
ORDER BY b.tsname;


[10g Above]
SQL> select * from table(dbms_space.OBJECT_GROWTH_TREND('RMAN','DB','TABLE'));

TIMEPOINT                                                                   SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
17-JAN-14 06.00.44.974958 AM                                                       4927       65536 INTERPOLATED
18-JAN-14 06.00.44.974958 AM                                                       4927       65536 GOOD
19-JAN-14 06.00.44.974958 AM                                                       4927       65536 PROJECTED