Database Growth report

 Use this query to find future database growth projection.



--Output 


Database   Create     Database   Used    Used    Free    Free                    Previous   Previous   Current    Current    Previous   Previous   Current    Current

Name       Time       Size       Space   in %    Space   in %    Week    Week in Month      Month in % Month      Month in % Year       Year in %  Year %     Year in %

---------- ---------- ---------- ------- ------- ------- ------- ------- ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------


set longchunksize 500;
set pagesize 1000;
set lines 2000;
set linesize 500;
Col database_name format a10 heading 'Database|Name'
Col create_time format a10 heading 'Create|Time'
Col database_size format a10 heading 'Database|Size'
Col used_space format a7 heading 'Used|Space'
Col used_in format a7 heading 'Used|in %'
Col free_space format a7 heading 'Free|Space'
Col free_in format a7 heading 'Free|in %'
Col growth_week format a7 heading 'Week'
Col growth_week_in format a7 heading 'Week in %'
Col growth_current_month format a10 heading 'Current|Month'
Col growth_current_month_in format a10 heading 'Current|Month in %'
Col growth_previous_month format a10 heading 'Previous|Month'
Col growth_previous_month_in format a10 heading 'Previous|Month in %'
COl growth_previous_year format a10 heading 'Previous|Year'
Col growth_previous_year_in format a10 heading 'Previous|Year in %'
Col growth_current_year format a10 heading 'Current|Year %'
Col growth_current_year_in format a10 heading 'Current|Year in %'
SELECT *
FROM   (SELECT (SELECT name
                FROM   v$database) Database_Name,
               (SELECT Min(creation_time)
                FROM   v$datafile) Create_Time,
               Round(( SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2)
               || ' GB'            Database_Size,
               Round(( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - Round(
                     FREE.p / 1024 / 1024 / 1024), 2)
               || ' GB'            Used_Space,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           Round(SUM(USED.bytes) / 1024 / 1024 / 1024, 2) * 100,
               2)
               || '% GB'           Used_in,
               Round(( FREE.p / 1024 / 1024 / 1024 ), 2)
               || ' GB'            Free_Space,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - ( (
                             SUM(USED.bytes) / 1024 / 1024 / 1024 ) - Round(
                             FREE.p / 1024 / 1024 / 1024)
                                                                          ) ) /
                     Round(
                           SUM(USED.bytes) / 1024 / 1024 / 1024, 2) * 100, 2)
               || '% GB'           Free_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * 7, 2)
               || ' GB'            Growth_WEEK,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * 7, 3)
               || '% GB'           Growth_WEEK_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT
                     To_char(Add_months(SYSDATE, -1), 'dd')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Previous_Month,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     To_char(
                     Add_months(SYSDATE, -1)
                     ,
                     'dd')
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Previous_Month_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT To_char(SYSDATE, 'dd')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Current_Month,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     To_char(SYSDATE, 'dd')
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Current_Month_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT Trunc(SYSDATE, 'YYYY') -
                                                        Add_months(Trunc(SYSDATE
                                                                   ,
                                                                   'YYYY'), -12)
                                                 FROM   dual), 2)
               || ' GB'            Growth_Previous_Year,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     Trunc(SYSDATE, 'YYYY') - Add_months(Trunc(SYSDATE, 'YYYY'),
                                              -12)
                                                                 FROM   dual), 3
               )
               || '% GB'           Growth_Previous_Year_in,
               Round(( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) -
                       ( FREE.p / 1024 / 1024 / 1024 ) ) /
                           (SELECT SYSDATE - Min(creation_time)
                           FROM   v$datafile) * (SELECT Add_months(Trunc(SYSDATE
                                                                   ,
                                                                   'YYYY'), 12)
                                                        - Trunc(SYSDATE, 'YYYY')
                                                 FROM   dual), 2)
               || ' GB'            Growth_Current_Year,
               Round(( ( ( SUM(USED.bytes) / 1024 / 1024 / 1024 ) - (
                         FREE.p / 1024 / 1024 / 1024 ) )
                             /
                               (SELECT SYSDATE - Min(creation_time)
                               FROM   v$datafile) / Round((
                       SUM(USED.bytes) / 1024 / 1024 / 1024 ), 2
                                                    ) * 100 ) * (SELECT
                     Add_months(Trunc(SYSDATE, 'YYYY'), 12) -
                     Trunc(SYSDATE, 'YYYY')
                        FROM   dual), 3)
               || '% GB'           Growth_Current_Year_in
        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); 


------Without spaces----


set longchunksize 500;

set pagesize 1000;

set lines 2000;

set linesize 500;

Col Database_Name format a10 HEADING 'Database|Name'

Col Create_Time format a10 HEADING 'Create|Time'

Col Database_Size format a10 HEADING 'Database|Size'

Col Used_Space format a7 HEADING 'Used|Space'

Col Used_in format a7 HEADING 'Used|in %'

Col Free_Space format a7 HEADING 'Free|Space'

Col Free_in format a7 HEADING 'Free|in %'

Col Growth_WEEK format a7 HEADING 'Week'

Col Growth_WEEK_in format a7 HEADING 'Week in %'

Col Growth_Current_Month format a10 HEADING 'Current|Month'

Col Growth_Current_Month_in format a10 HEADING 'Current|Month in %'

Col Growth_Previous_Month format a10 HEADING 'Previous|Month'

Col Growth_Previous_Month_in format a10 HEADING 'Previous|Month in %'

COl Growth_Previous_Year format a10 HEADING 'Previous|Year'

Col Growth_Previous_Year_in format a10 HEADING 'Previous|Year in %'

Col Growth_Current_Year format a10 HEADING 'Current|Year %'

Col Growth_Current_Year_in format a10 HEADING 'Current|Year in %'


select * from (select

(select name from v$database) Database_Name,(select min(creation_time) from v$datafile) Create_Time,

ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2) || ' GB' Database_Size,

ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ROUND(FREE.P / 1024 / 1024/1024 ),2) || ' GB' Used_Space,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024/1024 ,2)*100,2) || '% GB' Used_in,

ROUND((FREE.P / 1024 / 1024/1024 ),2) || ' GB' Free_Space,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ((SUM(USED.BYTES) / 1024 / 1024/1024 ) - ROUND(FREE.P / 1024 / 1024/1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024/1024,2 )*100,2) || '% GB' Free_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' GB' Growth_WEEK,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*7,3) || '% GB' Growth_WEEK_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select to_char(add_months(sysdate,-1),'dd') from dual),2) || ' GB' Growth_Previous_Month,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*(select to_char( add_months(sysdate,-1),'dd') from dual),3) || '% GB' Growth_Previous_Month_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select to_char(sysdate,'dd') from dual),2) || ' GB' Growth_Current_Month,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*(select to_char(sysdate,'dd') from dual),3) || '% GB' Growth_Current_Month_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select trunc(sysdate,'YYYY')-add_months(trunc(sysdate,'YYYY'),-12)  from dual),2) || ' GB' Growth_Previous_Year,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*(select trunc(sysdate,'YYYY')-add_months(trunc(sysdate,'YYYY'),-12)  from dual),3) || '% GB' Growth_Previous_Year_in,

ROUND(((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)*(select add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY') from dual),2) || ' GB' Growth_Current_Year,

ROUND((((SUM(USED.BYTES) / 1024 / 1024/1024 ) - (FREE.P / 1024 / 1024/1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024/1024 ),2)*100)*(select add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY') from dual),3) || '% GB' Growth_Current_Year_in

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);

Comments

Post a Comment

Dear User,

Thank you very much for your kind response

Popular posts from this blog

Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c

Image
Agent Installation on Windows Server. SQL Server (Failover Cluster) target addition in OEM 12c This document is prepared for monitoring windows server where sql server installed.Mostly used OEM for monitoring databases. In this document we will cover agent installation with target addition into OEM 12c. We will also cover prerequisite like enabling port for communication from agent to oms server Agent Installation on windows server  As a part of installation, we require to fulfil prerequisite like connectivity between OEM server and Window server. For Port opening and communication between two servers Ports required to open : 4903 , 7799 , 3872 Following steps require doing for manually installation of Agent in Windows server. Verifying if the agent exists on OMS. To begin, open your Cloud Control and go to "Setup -> Extensibility -> Self Update". Open the "Agent Software" folder and check if the Agent Software of Windows x64 is with statu...
Read more

Oracle 10g/11g Linux:SMS the alert logs ORA- errors generated in timestamp

Image
SMS Alert logs ORA- errors This is something new in database administration part.Suppose DBA was out of office and got call from company manager to come to office.There is some problem in database.Generally DBA connect database and check connection possible or not then check any alert log entry.It is better if DBA and their company people got messages whenever any ORA- errors came in certain period of time. For this,I found some solution to read alert log and send sms to respective people. It is mandatory if we put database in archive log and where frequent archives generated.This is require because of the timestamp of archives used to get the start and end time of alertlogs entry. This is all done through SHELL script and on linux machine. Here it is the magical script can help to get the info of SMS by mail ans SMS. This shell designed to read alerts of last 30 minutes. #!/bin/bash #Go to bdump location cd /oracle1/oradata/pindb/bdump/ #SMS d...
Read more

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server.

Oracle 11g: Install Instant Client 11.2.0.3.0 on Linux x86_64 Server. ***What is Instant Client &benefits? As per Oracle docs, Oracle Database Instant Client Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle. ISVs and Partners   benefit greatly from packaging Instant Client along with their applications, saving customers the extra step of installing and configuring an Oracle client. Free. The same fully-featured, high-performance applications that Oracle ISVs and partners have always delivered will continue to work, untouched. Customers can try new packaged applications and Oracle client features quickly without worrying about other installations. Larger enterprises ca...
Read more