Use EM12c to Get Database Storage Information

Have you ever been asked by your SAN admin "Can you give me a 6 to 12 month database future growth report"? What about your manager asking can you "Give me database storage trends over the last 6 months"? Well in my case many times to the point I got tried so I built some tables, procedures and views based on the data in Enterprise Manager. I have been using the data since EM10g and now in EM12c.

Follow me on Twitter

The tables contain data for three different types of trends and the data is populated using three different procedures. The data is selected by using the 3 different views.

Tables:
DBA_DATABASE_STORAGE_USAGE- This tables contains the current storage as of the last run of the proc.

DBA_HISTORICAL_STORAGE_USAGE- This table contains the historical data the all months since the target database instance as been in EM.

DBA_FUTURE_STORAGE_USAGE- This table contains all the database storage since the database target has been in EM. The way you get the future storage is based on a view that you can modify based on your storage requirements.

Procedures:
DBA_DB_STORAGE- This proc populates the DBA_DATABASE_STORAGE_USAGE tables with all the current storage.

DBA_HISTORICAL_STORAGE- This proc populates the DBA_HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.

DBA_FUTURE_STORAGE- This proc populates the DBA_HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.

Views:
DBA_CURRENT_DB_STORAGE_USAGE- This views shows the current storage usage allocated space, used space and allocated free space. Also shows the usage in percent. The last two columns show the DATAPUMP and RMAN space usages. The RMAN column is allocated_space * 0.30 and the export is based on allocated space * 0.15. You can change this as need base on your requirements.

Below is an explain output from my vbox setup

DBA_HISTORICAL_DB_STORAGE_USAGE- This view shows historical database storage it shows allocated space, used space and allocated free space. You will also see the percent used and each row is the first of the month for each database instance target in Enterprise Manager.

Below is an explain output from my vbox setup

DBA_FUTURE_STORAGE_USAGE- This view shows future database growth based on the max allocated_space -min allocated_space *12 which you can change as needed. The back 12 month is based on max allocated space -min allocated space *12*0.30. You may ask why 0.30 well I compress my RMAN backups and RMAN has almost a 70% compression rate if you database is not full of lobs. The export 12 month backup is based on on max allocated space -min allocated space *12*0.15. The used space is base on max used space -min used space *12 this is useful if the SAN admin starts ask about white space.

Below is an explain output from my vbox setup note my vbox setup is to small and does not have the numbers to show 12 month growth.

Below is all the scripts to create the tables, procedures and views. I have the schema set to DBA_REPOSITORY so you need to edit the below with your own schema. You can easily do a find and replace.

TABLES
--------------------------------------------------------
--  File created - Friday-June-28-2013
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table DBA_DATABASE_STORAGE_USAGE
--------------------------------------------------------

  CREATE TABLE "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE"
   ( "TARGET_GUID" VARCHAR2(40 BYTE),
"ALLOCATED_SPACE_GB" VARCHAR2(10 BYTE),
"USED_SPACE_GB" VARCHAR2(10 BYTE),
"ALLOCATED_FREE_SPACE_GB" VARCHAR2(10 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBA_REPOSITORY_DATA" ;

   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE"."ALLOCATED_SPACE_GB" IS 'Total physical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE"."USED_SPACE_GB" IS 'Total logical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE"."ALLOCATED_FREE_SPACE_GB" IS 'Total free physical space in GB';
--------------------------------------------------------
--  DDL for Table DBA_FUTURE_STORAGE_USAGE
--------------------------------------------------------

  CREATE TABLE "DBA_REPOSITORY"."DBA_FUTURE_STORAGE_USAGE"
   ( "TARGET_GUID" VARCHAR2(40 BYTE),
"ALLOCATED_SPACE_GB" NUMBER,
"USED_SPACE_GB" NUMBER,
"ALLOCATED_FREE_SPACE_GB" NUMBER,
"CALENDAR_MONTH" DATE,
"USED_PCT" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBA_REPOSITORY_DATA" ;

   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_FUTURE_STORAGE_USAGE"."ALLOCATED_SPACE_GB" IS 'Total physical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_FUTURE_STORAGE_USAGE"."USED_SPACE_GB" IS 'Total logical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_FUTURE_STORAGE_USAGE"."ALLOCATED_FREE_SPACE_GB" IS 'Total free physical space in GB';
--------------------------------------------------------
--  DDL for Table DBA_HISTORICAL_STORAGE_USAGE
--------------------------------------------------------

  CREATE TABLE "DBA_REPOSITORY"."DBA_HISTORICAL_STORAGE_USAGE"
   ( "TARGET_GUID" VARCHAR2(40 BYTE),
"ALLOCATED_SPACE_GB" NUMBER,
"USED_SPACE_GB" NUMBER,
"ALLOCATED_FREE_SPACE_GB" NUMBER,
"CALENDAR_MONTH" DATE,
"USED_PCT" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBA_REPOSITORY_DATA" ;

   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_HISTORICAL_STORAGE_USAGE"."ALLOCATED_SPACE_GB" IS 'Total physical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_HISTORICAL_STORAGE_USAGE"."USED_SPACE_GB" IS 'Total logical space used in GB';
   COMMENT ON COLUMN "DBA_REPOSITORY"."DBA_HISTORICAL_STORAGE_USAGE"."ALLOCATED_FREE_SPACE_GB" IS 'Total free physical space in GB';
--------------------------------------------------------
--  DDL for Index TARGET_GUID_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "DBA_REPOSITORY"."TARGET_GUID_PK" ON "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE" ("TARGET_GUID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBA_REPOSITORY_DATA" ;
--------------------------------------------------------
--  Constraints for Table DBA_DATABASE_STORAGE_USAGE
--------------------------------------------------------

  ALTER TABLE "DBA_REPOSITORY"."DBA_DATABASE_STORAGE_USAGE" ADD CONSTRAINT "TARGET_GUID_PK" PRIMARY KEY ("TARGET_GUID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DBA_REPOSITORY_DATA"  ENABLE;

PROCS
--------------------------------------------------------
--  DDL for Procedure DBA_DB_STORAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "DBA_REPOSITORY"."DBA_DB_STORAGE" IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       DBA_DB_STORAGE
   PURPOSE:    To populate the DBA_database_storage_usage table with storage data info

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        4/19/2013   javier ruiz      1. Created this procedure.

******************************************************************************/
-- gets target_guid
   CURSOR get_target_guid
   IS
      select target_guid
  from mgmt_targets
 where target_type = 'oracle_database';

-- get storage info
CURSOR get_storage (v_target_guid VARCHAR2)
IS
select round (sum (t.tablespace_size / 1024 / 1024 / 1024), 2)
          as allocated_gb,
       round (sum (t.tablespace_used_size / 1024 / 1024 / 1024), 2)
          as used_gb,
       round (
          sum (
             (t.tablespace_size - tablespace_used_size) / 1024 / 1024 / 1024),
          2)
          as allocated_free_gb
  from mgmt$db_tablespaces t,
       (select target_guid
  from mgmt_targets
 where target_guid = v_target_guid
         and (target_type = 'oracle_database')) tg
 where t.target_guid = tg.target_guid;


   v_target_guid                  VARCHAR2 (50);
   v_allocated_space           VARCHAR2 (20);
   v_used_gb                      VARCHAR2 (20);
   v_allocate_free_gb        VARCHAR2 (20);


BEGIN
   tmpVar := 0;

 -- truncating dba_database_storage_usage table
execute immediate 'truncate table DBA_database_storage_usage';

-- we go get the target_guid
FOR c1 IN get_target_guid
   LOOP
      v_target_guid := c1.target_guid;
      --DBMS_OUTPUT.put_line ('THE TARGET_GUID IS: ' || v_target_guid);

-- we go get the storage data
      FOR c2 IN get_storage (v_target_guid)
            LOOP
            v_allocated_space:=c2.allocated_gb;
            v_used_gb:=c2.used_gb;
            v_allocate_free_gb:=c2.allocated_free_gb;

            --DBMS_OUTPUT.put_line ('GUID is ' || v_target_guid);
            --DBMS_OUTPUT.put_line ('Allocated Space ' || v_allocated_space);
            --DBMS_OUTPUT.put_line ('Used Space ' || v_used_gb);
            --DBMS_OUTPUT.put_line ('Allocate Free Space  ' || v_allocate_free_gb);

-- we insert the storage data
            insert into DBA_database_storage_usage
            values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb);
            commit;

            END LOOP;
   END LOOP;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DBA_DB_STORAGE;

/
--------------------------------------------------------
--  DDL for Procedure DBA_FUTURE_STORAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "DBA_REPOSITORY"."DBA_FUTURE_STORAGE" IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       DBA_FUTURE_STORAGE
   PURPOSE:    To populate the DBA_FUTURE_STORAGE_USAGE table with storage data info

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        5/23/2013   javier ruiz       1. Created this procedure.

******************************************************************************/
-- gets target_guid
   CURSOR get_target_guid
   IS
      select target_guid
  from mgmt_targets
 where target_type = 'oracle_database';

-- get storage info
CURSOR get_storage (v_target_guid VARCHAR2)
IS
SELECT
       TO_DATE(TO_CHAR(a_size.month_timestamp,'MON RR'),'MON RR') AS CALENDAR_MONTH,
       round(avg(a_size.size_gb),2) AS allocated_gb,
       round(avg(used.used_gb),2) AS USED_GB,
       round(avg(a_size.size_gb - used.used_gb),2) AS allocated_FREE_GB,
       round(avg((used.used_gb*100)/
                decode(a_size.size_gb,0,1,a_size.size_gb))
                ,2) AS USED_PCT
     FROM
      (SELECT
           m.rollup_timestamp AS month_timestamp,
           sum(m.average/1024) AS size_gb
         FROM
           mgmt$metric_daily m,
           mgmt$target_type t
         WHERE
           t.target_guid=HEXTORAW(v_target_guid) AND
           (t.target_type='rac_database' OR
           (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
           m.target_guid=t.target_guid AND
           m.metric_guid=t.metric_guid AND
           t.metric_name='tbspAllocation' AND
           (t.metric_column='spaceAllocated') AND
           m.rollup_timestamp >= sysdate-365 and
           m.rollup_timestamp <= sysdate
         GROUP BY m.metric_column, m.rollup_timestamp) a_size,
       (SELECT
           m.rollup_timestamp AS month_timestamp,
           sum(m.average/1024) AS used_gb
         FROM
           mgmt$metric_daily m,
           mgmt$target_type t
         WHERE
           t.target_guid=HEXTORAW(v_target_guid) AND
           (t.target_type='rac_database' OR
           (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
           m.target_guid=t.target_guid AND
           m.metric_guid=t.metric_guid AND
           t.metric_name='tbspAllocation' AND
           (t.metric_column='spaceUsed') AND
           m.rollup_timestamp >= sysdate-365 and
           m.rollup_timestamp <= sysdate
         GROUP BY m.metric_column, m.rollup_timestamp) used
     WHERE
       a_size.month_timestamp =used.month_timestamp
     GROUP BY TO_CHAR(a_size.month_timestamp,'MON RR');


   v_target_guid                  VARCHAR2 (50);
   v_sid                        VARCHAR2(50);
   v_allocated_space           VARCHAR2 (20);
   v_used_gb                      VARCHAR2 (20);
   v_allocate_free_gb        VARCHAR2 (20);
   v_calendar_month             VARCHAR2 (20);
   v_used_pct                   VARCHAR2 (20);


BEGIN
   tmpVar := 0;

 -- truncating DBA_FUTURE_STORAGE_USAGE table
execute immediate 'truncate table DBA_FUTURE_STORAGE_USAGE';

-- we go get the target_guid
FOR c1 IN get_target_guid
   LOOP
      v_target_guid := c1.target_guid;
      --DBMS_OUTPUT.put_line ('THE TARGET_GUID IS: ' || v_target_guid);

-- we go get the storage data
      FOR c2 IN get_storage (v_target_guid)
            LOOP
            v_allocated_space:=c2.allocated_gb;
            v_used_gb:=c2.used_gb;
            v_allocate_free_gb:=c2.allocated_free_gb;
            v_calendar_month := c2.calendar_month;
            v_used_pct := c2.used_pct;

            --DBMS_OUTPUT.put_line ('GUID is ' || v_target_guid);
            --DBMS_OUTPUT.put_line ('Allocated Space ' || v_allocated_space);
            --DBMS_OUTPUT.put_line ('Used Space ' || v_used_gb);
            --DBMS_OUTPUT.put_line ('Allocate Free Space  ' || v_allocate_free_gb);

-- we insert the storage data
            insert into DBA_FUTURE_STORAGE_USAGE
            values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb,v_calendar_month,v_used_pct);
            commit;

            END LOOP;
   END LOOP;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DBA_FUTURE_STORAGE;

/
--------------------------------------------------------
--  DDL for Procedure DBA_HISTORICAL_STORAGE
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "DBA_REPOSITORY"."DBA_HISTORICAL_STORAGE" IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       DBA_HISTORICAL_STORAGE
   PURPOSE:    To populate the DBA_historical_storage_usage table with storage data info

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2/20/2011   javier ruiz       1. Created this procedure.

******************************************************************************/
-- gets target_guid
   CURSOR get_target_guid
   IS
      select target_guid
  from mgmt_targets
 where target_type = 'oracle_database';

-- get storage info
CURSOR get_storage (v_target_guid VARCHAR2)
IS
SELECT
       TO_DATE(TO_CHAR(a_size.month_timestamp,'MON RR'),'MON RR') AS CALENDAR_MONTH,
       round(avg(a_size.size_gb),2) AS allocated_gb,
       round(avg(used.used_gb),2) AS USED_GB,
       round(avg(a_size.size_gb - used.used_gb),2) AS allocated_FREE_GB,
       round(avg((used.used_gb*100)/
                decode(a_size.size_gb,0,1,a_size.size_gb))
                ,2) AS USED_PCT
     FROM
      (SELECT
           m.rollup_timestamp AS month_timestamp,
           sum(m.average/1024) AS size_gb
         FROM
           mgmt$metric_daily m,
           mgmt$target_type t
         WHERE
           t.target_guid=HEXTORAW(v_target_guid) AND
           (t.target_type='rac_database' OR
           (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
           m.target_guid=t.target_guid AND
           m.metric_guid=t.metric_guid AND
           t.metric_name='tbspAllocation' AND
           (t.metric_column='spaceAllocated') AND
           m.rollup_timestamp >= sysdate-365 and
           m.rollup_timestamp <= sysdate
         GROUP BY m.metric_column, m.rollup_timestamp) a_size,
       (SELECT
           m.rollup_timestamp AS month_timestamp,
           sum(m.average/1024) AS used_gb
         FROM
           mgmt$metric_daily m,
           mgmt$target_type t
         WHERE
           t.target_guid=HEXTORAW(v_target_guid) AND
           (t.target_type='rac_database' OR
           (t.target_type='oracle_database' AND t.TYPE_QUALIFIER3 != 'RACINST')) AND
           m.target_guid=t.target_guid AND
           m.metric_guid=t.metric_guid AND
           t.metric_name='tbspAllocation' AND
           (t.metric_column='spaceUsed') AND
           m.rollup_timestamp >= sysdate-365 and
           m.rollup_timestamp <= sysdate
         GROUP BY m.metric_column, m.rollup_timestamp) used
     WHERE
       a_size.month_timestamp =used.month_timestamp
     GROUP BY TO_CHAR(a_size.month_timestamp,'MON RR');


   v_target_guid                  VARCHAR2 (50);
   v_sid                        VARCHAR2(50);
   v_allocated_space           VARCHAR2 (20);
   v_used_gb                      VARCHAR2 (20);
   v_allocate_free_gb        VARCHAR2 (20);
   v_calendar_month             VARCHAR2 (20);
   v_used_pct                   VARCHAR2 (20);


BEGIN
   tmpVar := 0;

 -- truncating DBA_historical_storage_usage table
execute immediate 'truncate table DBA_historical_storage_usage';

-- we go get the target_guid
FOR c1 IN get_target_guid
   LOOP
      v_target_guid := c1.target_guid;
      --DBMS_OUTPUT.put_line ('THE TARGET_GUID IS: ' || v_target_guid);

-- we go get the storage data
      FOR c2 IN get_storage (v_target_guid)
            LOOP
            v_allocated_space:=c2.allocated_gb;
            v_used_gb:=c2.used_gb;
            v_allocate_free_gb:=c2.allocated_free_gb;
            v_calendar_month := c2.calendar_month;
            v_used_pct := c2.used_pct;

            --DBMS_OUTPUT.put_line ('GUID is ' || v_target_guid);
            --DBMS_OUTPUT.put_line ('Allocated Space ' || v_allocated_space);
            --DBMS_OUTPUT.put_line ('Used Space ' || v_used_gb);
            --DBMS_OUTPUT.put_line ('Allocate Free Space  ' || v_allocate_free_gb);

-- we insert the storage data
            insert into DBA_historical_storage_usage
            values (v_target_guid,v_allocated_space,v_used_gb,v_allocate_free_gb,v_calendar_month,v_used_pct);
            commit;

            END LOOP;
   END LOOP;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DBA_HISTORICAL_STORAGE;


/

VIEWS

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

--  DDL for View DBA_CURRENT_DB_STORAGE

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

  CREATE OR REPLACE FORCE VIEW "DBA_REPOSITORY"."DBA_CURRENT_DB_STORAGE" ("DB_NAME", "CALENDAR_MONTH", "ALLOCATED_SPACE_GB", "USED_SPACE_GB", "ALLOCATED_FREE_SPACE_GB", "USED_PCT", "backup_space_used_rman", "backup_space_used_export") AS 

  select c.VALUE AS DB_NAME ,to_char(calendar_month,'yyyy-mm-dd') as calendar_month,

ceil(allocated_space_gb) as allocated_space_gb,

ceil(used_space_gb) as used_space_gb,

ceil(allocated_free_space_gb) as allocated_free_space_gb,

used_pct,

ceil(allocated_space_gb*0.30) as "backup_space_used_rman",ceil(used_space_gb*0.15) as"backup_space_used_export" from DBA_HISTORICAL_STORAGE_USAGE a,

(SELECT value,target_guid

            FROM MGMT$DB_INIT_PARAMS

           WHERE name = 'db_name') c

where a.target_guid = c.target_guid

order by 1 asc,2 asc;

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

--  DDL for View DBA_FUTURE_DB_STORAGE

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

  CREATE OR REPLACE FORCE VIEW "DBA_REPOSITORY"."DBA_FUTURE_DB_STORAGE" ("DB_NAME", "12_month_growth_gb", "12_month_backup_gb", "export_12_month_backup_gb", "used_12_month_growth_gb") AS 

  select c.VALUE AS DB_NAME ,

ceil(max(allocated_space_gb)-min(allocated_space_gb))*12as "12_month_growth_gb",

ceil(max(allocated_space_gb)-min(allocated_space_gb))*12*0.30 as "12_month_backup_gb",

ceil(max(used_space_gb)-min(used_space_gb))*12*0.15 as "export_12_month_backup_gb",

ceil(max(used_space_gb)-min(used_space_gb))*12as "used_12_month_growth_gb" from 

DBA_FUTURE_STORAGE_USAGE a,

(SELECT value,target_guid

            FROM MGMT$DB_INIT_PARAMS

           WHERE name = 'db_name') c

where a.target_guid = c.target_guid

and calendar_month between trunc(to_date(sysdate), 'MONTH')-31 and trunc(to_date(sysdate), 'MONTH')

group by c.VALUE

ORDER BY 1 ASC;

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

--  DDL for View DBA_HISTORICAL_DB_STORAGE

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

  CREATE OR REPLACE FORCE VIEW "DBA_REPOSITORY"."DBA_HISTORICAL_DB_STORAGE" ("DB_NAME", "ALLOCATED_SPACE_GB", "USED_SPACE_GB", "ALLOCATED_FREE_SPACE_GB", "CALENDAR_MONTH", "USED_PCT") AS 

  select c.VALUE AS DB_NAME,

allocated_space_gb,

used_space_gb,

allocated_free_space_gb,

calendar_month,used_pct

 from DBA_HISTORICAL_STORAGE_USAGE a,

 (SELECT VALUE, target_guid

            FROM MGMT$DB_INIT_PARAMS

           WHERE name = 'db_name') c

where a.target_guid = c.target_guid

order by 1,4 asc;

PRIVILEGES:
The following privileges need to be granted to the user account that will own the tables, views and procedures.
GRANT SELECT ON SYSMAN.MGMT$DB_INIT_PARAMS TO DBA_REPOSITORY;
GRANT SELECT ON SYSMAN.MGMT$DB_TABLESPACES TO DBA_REPOSITORY;
GRANT SELECT ON SYSMAN.MGMT$METRIC_DAILY TO DBA_REPOSITORY;
GRANT SELECT ON SYSMAN.MGMT$TARGET_TYPE TO DBA_REPOSITORY;
GRANT SELECT ON SYSMAN.MGMT_TARGETS TO DBA_REPOSITORY;
GRANT SELECT ON SYSMAN.MGMT_TARGET_PROPERTIES TO DBA_REPOSITORY;