Procedure to check DB growth
1.Create a Table By the Name DB_GROWTH…with following details…
CREATE TABLE DB_GROWTH
( DAY DATE,
DATABASE_SIZE_GB NUMBER,
DAILY_GROWTH_GB NUMBER);
2.create or replace PROCEDURE database_growth
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024) INTO today_size FROM SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM db_growth WHERE to_date(d
ay,’dd-mon-yy’)=to_date(SYSDATE -1,’dd-mon-yy’);
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size – yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
3.Submit in DBMS_JOBS
variable jobno number;
begin
dbms_job.submit(
:jobno,
‘database_growth ;’,
trunc(sysdate+1) + 4/24,
‘trunc(sysdate+1) + 4/24’
);
commit;
end;
/
Excelent dude….This is realy useful script….
Keep updating….!
i am new to dba please let me know how to check the growth after creating the above script
plz mail me
Dear Jo,
Once you created requested table, just run the procedure or schedule it for daily basis and you will get the data in table db_growth .
This is nothing but day,current DB size and daily Db growth size…. simple….
Let me know if you have any confusion.
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”
Sorry Samadhan i am not clear.
i have created the table then how to create that procedure and how to run it…
apologies for asking the same question again and again becoz i am new to DBA
Dear Jo,
Its ok…. You need not to be sorry at all…..
Please follow the below steps.
1)Create one new schema XYZ for the these objects.
2)Create the two given table using below DDL.
CREATE TABLE “XYZ”.”DB_GROWTH” ( “DAY” DATE, “DATABASE_SIZE_GB” NUMBER, “DAILY_GROWTH_GB” NUMBER) TABLESPACE “XYX” PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
CREATE TABLE “XYX”.”DB_GROWTH_AVG” ( “MONTH” VARCHAR2(20), “DATABASE_AVG_SIZE_GB” NUMBER) TABLESPACE “XYX” PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING
3)Create the below two procedure. Just run these two procedure at SQl prompt.
Pls note this procedure is to check daily DB growth and data will be updated in table “XYZ”.”DB_GROWTH” .
CREATE PROCEDURE “OASIS”.”DATABASE_GROWTH”
AS
today_size NUMBER;
yesterday_size NUMBER;
growth_size NUMBER;
cnt NUMBER;
BEGIN
SELECT sum(bytes)/(1024*1024*1024) INTO today_size FROM sys.SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM db_growth ;
IF cnt > 0
THEN
SELECT database_size_gb INTO yesterday_size FROM db_growth WHERE to_date(day,’dd-mon-yy’)=to_date(SYSDATE -1,’dd-mon-yy’);
ELSE
yesterday_size:=today_size;
END IF;
growth_size := today_size – yesterday_size;
INSERT INTO db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Pls note below Procedure is for monthly DB growth so you need to execute on monthly basis.
CREATE PROCEDURE “OASIS”.”DATABASE_GROWTH_AVG”
AS
this_month_avg number;
BEGIN
select round((sum(DAILY_GROWTH_GB))/(to_number(to_char(sysdate-1,’DD’),9999)),0
) into this_month_avg from db_growth where to_char(DAY,’MON-YY’)=to_char(sysdate-1,’MON-YY’);
INSERT INTO db_growth_avg VALUES(to_char(sysdate-1,’MON-YY’),this_month_avg);
EXCEPTION
WHEN others THEN
INSERT INTO db_growth_avg VALUES(sysdate,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
And schedule it as per the requirement on daily and monthly basis using DBMS_jobs.
Hope u get it….. Let me know incase you need any clarification.
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”