Thursday, January 10, 2008

procedure for Calculating Database Growth and scheduling in DBMS JOBS....

1.
Create a Table By the Name db_growth...with following details...

Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MB 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;
/
print :jobno

9 comments:

Tonguç said...

-- after 10g

select * from
table(dbms_space.OBJECT_GROWTH_TREND
('ARUP','BOOKINGS','TABLE'));

http://www.oracle.com/technology/pub/articles/10gdba/week15_10gdba.html

Speedpigz said...

I'm not a programmer but statement 2 fails on my database:
.
Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE DATABASE_GROWTH:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1 PL/SQL: SQL Statement ignored
8/56 PL/SQL: ORA-00942: table or view does not exist
SQL> show user
USER is "SYSTEM"
SQL> desc db_growth;
Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MB NUMBER

SQL>

Speedpigz said...

This is the statement I'm using:

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 sys.SM$TS_USED;
SELECT COUNT(1) INTO cnt FROM system.db_growth ;
IF cnt > 0
THEN
SELECT database_size_mb INTO yesterday_size FROM system.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 system.db_growth VALUES(sysdate,today_size,growth_size);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO system.db_growth VALUES(sysdate,today_size,0);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Anonymous said...

The table sys.SM$TS_USED does not exixts. That's why you receive the error

Anonymous said...

Hello !.
You re, I guess , perhaps very interested to know how one can collect a huge starting capital .
There is no need to invest much at first. You may start earning with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with affiliates everywhere: In USA, Canada, Cyprus.
Do you want to become really rich in short time?
That`s your choice That`s what you desire!

I feel good, I started to take up income with the help of this company,
and I invite you to do the same. If it gets down to select a proper partner utilizes your money in a right way - that`s AimTrust!.
I make 2G daily, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://hemynefed.arcadepages.com/gapalo.html
and go! Let`s take this option together to become rich

Anonymous said...

I wish not approve on it. I over warm-hearted post. Specially the title attracted me to be familiar with the whole story.

Anonymous said...

Easily I to but I about the collection should prepare more info then it has.

Anonymous said...

Good day, sun shines!
There have were times of hardship when I didn't know about opportunities of getting high yields on investments. I was a dump and downright stupid person.
I have never thought that there weren't any need in big starting capital.
Nowadays, I feel good, I begin take up real money.
It gets down to choose a proper companion who uses your money in a right way - that is incorporate it in real business, parts and divides the profit with me.

You may ask, if there are such firms? I have to answer the truth, YES, there are. Please get to know about one of them:
http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]

Tani said...

I'm just a beginner but statement fails on my database. please clarify more how do i use it in 11G.
sap ecc 7.0