How to use EM12c Repository Views to get Database Storage Details

~ sharmadeepak345

Hi All,

Recently I was caught in a situation when there was a sudden increase in my PeopleSoft financials database. My Manager and SystemAdmin asks me to provide them
“database storage trends over the last 6 months” and expected “6 to 12 month database future growth report”.

It will be very hectic if you have to do this again and again unless you built some tables, procedures and views based on the data in Enterprise Manager. In this blog
of mine I have created some of the usefull tables using which you can easily provide data to your reporting lead or Infrastructure Team.

In this demo we have divided the storage into three different categories and we will create three separate tables for them. The data will be populated in them using the
procedures which will be calling original tables from “SYSMAN” schema and the final data is selected by using the 3 different views.
Mentioned below are the details of used Tables:
=====================================================
DATABASE_STORAGE_USAGE- will contain the current database storage as of the last run of the stored procedure.
HISTORICAL_STORAGE_USAGE- will contain the historical data for the all months, target database instance has been in EM12c.
FUTURE_STORAGE_USAGE- will contain all the database storage since the database target has been in EM12c.

The output of the future storage is based on a view which you can modify based on your storage requirements.

Mentioned below are the details of used Procedures:
=====================================================
DB_STORAGE- This procedure populates the DATABASE_STORAGE_USAGE table with all the current storage.
HISTORICAL_STORAGE- This procedure populates the HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.
FUTURE_STORAGE- This procedure populates the HISTORICAL_STORAGE_USAGE table with all the storage since the database instance target was add into Enterprise Manager.

Mentioned below are the details of used View:
================================================
CURRENT_DB_STORAGE_USAGE–

This view not only shows the current storage usage allocated space, used space and allocated free space but also shows the usage in percent.  DATAPUMP and RMAN space usage has also been included in the last two columns. The RMAN column is allocated_space * 0.30 and the export is based on allocated space * 0.15.  The same can be modified as per the requirements.

HISTORICAL_DB_STORAGE_USAGE–

This view shows historical database storage it shows allocated space, used space and allocated free space addition to that, the percent used space can also be viewed where each row is the first of every month for each database instance target in Enterprise Manager.

FUTURE_DB_STORAGE_USAGE–

This view shows future database growth based on the max allocated_space -min allocated_space *12 which you can modify as per requirement.  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 if your database is not full of lobs “large objects”, RMAN has almost a 70% compression rate. The export 12 months backup is based on {maximum allocated space – minimum allocated space} *12*0.15.

The used space is base on {maximum used space – minimum used space} * 12.

 Before we proceed further grant below mentioned PRIVILEGES to REP_USER user which owns tables, views and procedures

GRANT SELECT ON SYSMAN.MGMT$DB_INIT_PARAMS TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$DB_TABLESPACES TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$METRIC_DAILY TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT$TARGET_TYPE TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT_TARGETS TO REP_USER;
GRANT SELECT ON SYSMAN.MGMT_TARGET_PROPERTIES TO REP_USER;

Mentioned below are the scripts to create the tables, procedures and views. The schema being used in this demo is “REP_USER“,  you can modify as per your environment.

——————————————————–
— DDL for Table DATABASE_STORAGE_USAGE
——————————————————–

CREATE TABLE “REP_USER”.”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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”DATABASE_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Table FUTURE_STORAGE_USAGE
——————————————————–
CREATE TABLE “REP_USER”.”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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”FUTURE_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Table HISTORICAL_STORAGE_USAGE
——————————————————–
CREATE TABLE “REP_USER”.”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 “REP_USER_DATA” ;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”ALLOCATED_SPACE_GB” IS ‘Total physical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”USED_SPACE_GB” IS ‘Total logical space used in GB’;
COMMENT ON COLUMN “REP_USER”.”HISTORICAL_STORAGE_USAGE”.”ALLOCATED_FREE_SPACE_GB” IS ‘Total free physical space in GB’;
——————————————————–
— DDL for Index TARGET_GUID_PK
——————————————————–
CREATE UNIQUE INDEX “REP_USER”.”TARGET_GUID_PK” ON “REP_USER”.”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 “REP_USER_DATA” ;
——————————————————–
— Constraints for Table DATABASE_STORAGE_USAGE
——————————————————–
ALTER TABLE “REP_USER”.”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 “REP_USER_DATA” ENABLE;
——————————————————–
— DDL for Procedure DB_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”DB_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
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;
execute immediate ‘truncate table DATABASE_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
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;
insert into 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
RAISE;
END DB_STORAGE;
/
——————————————————–
— DDL for Procedure FUTURE_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”FUTURE_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
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;
execute immediate ‘truncate table FUTURE_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
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;
insert into 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
RAISE;
END FUTURE_STORAGE;
/
——————————————————–
— DDL for Procedure HISTORICAL_STORAGE
——————————————————–
set define off;
CREATE OR REPLACE PROCEDURE “REP_USER”.”HISTORICAL_STORAGE” IS
tmpVar NUMBER;
CURSOR get_target_guid
IS
select target_guid
from mgmt_targets
where target_type = ‘oracle_database’;
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;
execute immediate ‘truncate table HISTORICAL_STORAGE_USAGE’;
FOR c1 IN get_target_guid
LOOP
v_target_guid := c1.target_guid;
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;
insert into 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
RAISE;
END HISTORICAL_STORAGE;
/

DDL for inolved Views

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

——————————————————–
— DDL for View CURRENT_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”CURRENT_DB_STORAGE_USAGE” (“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 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 FUTURE_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”FUTURE_DB_STORAGE_USAGE” (“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
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 HISTORICAL_DB_STORAGE_USAGE
——————————————————–
CREATE OR REPLACE FORCE VIEW “REP_USER”.”HISTORICAL_DB_STORAGE_USAGE”
(“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 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;

Now you can use these views to fetch data as per your requirement. Hope this will save your time and help you to easily provide information.

Thanks

Deepak

Published by sharmadeepak345

I am Punjabi guy born in Amritsar, fun loving, and family sick.unfortunately.. ;) I love traveling to different places. Happily married now. I started my career working as Technical Support Office for Microsoft in Convergys Gurgaon, after 4 months I got pissed off by lifestyle that I was living alone in Gurgaon. Later I got chance to work as ORACLE DBA in Bebo Technologies which actually I was looking for after my graduation, I worked there for 2 years and since Jan 2012 am working as ORACLE EM DBA in Oceaneering International Inc. Chandigarh India. I am also one of the five founders of AIOUG-North India Chapter, which now is doing great success. Recently in Sangam14 {AIOUG} I was awarded with "Best Volunteer of the year" award. I love to give sessions on different technologies in ORACLE however my specialty in EM12c Cloud Control.

Leave a comment