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

13 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

oakleyses said...

tory burch outlet, polo ralph lauren, ugg boots, chanel handbags, tiffany jewelry, longchamp outlet, longchamp pas cher, gucci handbags, sac longchamp pas cher, uggs on sale, jordan pas cher, michael kors pas cher, ray ban sunglasses, oakley sunglasses, polo outlet, louboutin pas cher, longchamp outlet, burberry pas cher, louis vuitton, cheap oakley sunglasses, replica watches, replica watches, longchamp outlet, christian louboutin outlet, prada handbags, oakley sunglasses wholesale, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, louis vuitton, prada outlet, nike outlet, louis vuitton outlet, christian louboutin, nike free run, nike roshe, ugg boots, nike air max, polo ralph lauren outlet online, kate spade outlet, christian louboutin uk, tiffany and co, nike free, oakley sunglasses, jordan shoes, louis vuitton outlet, nike air max, ray ban sunglasses

oakleyses said...

nike tn, ralph lauren uk, true religion outlet, nike air max uk, burberry handbags, michael kors, abercrombie and fitch uk, nike blazer pas cher, oakley pas cher, timberland pas cher, hogan outlet, uggs outlet, michael kors outlet online, replica handbags, sac vanessa bruno, true religion outlet, converse pas cher, guess pas cher, ray ban pas cher, ray ban uk, true religion outlet, michael kors outlet, mulberry uk, nike roshe run uk, hollister uk, coach outlet, kate spade, nike air max, new balance, true religion jeans, north face, coach outlet store online, uggs outlet, michael kors outlet online, polo lacoste, michael kors outlet, lululemon canada, nike air force, vans pas cher, michael kors outlet, hollister pas cher, michael kors outlet online, sac hermes, michael kors, burberry outlet, michael kors outlet online, north face uk, nike air max uk

oakleyses said...

converse, louboutin, soccer jerseys, ferragamo shoes, hermes belt, longchamp uk, p90x workout, north face outlet, baseball bats, instyler, nike trainers uk, babyliss, mont blanc pens, soccer shoes, nfl jerseys, valentino shoes, oakley, hollister clothing, abercrombie and fitch, chi flat iron, ralph lauren, nike air max, north face outlet, gucci, ray ban, vans, beats by dre, herve leger, jimmy choo outlet, giuseppe zanotti outlet, mac cosmetics, ghd hair, reebok outlet, nike roshe run, hollister, mcm handbags, timberland boots, nike air max, lululemon, bottega veneta, insanity workout, nike huaraches, vans outlet, hollister, iphone cases, wedding dresses, new balance shoes, converse outlet, asics running shoes, celine handbags

oakleyses said...

marc jacobs, ugg,uggs,uggs canada, louis vuitton, swarovski, barbour uk, montre pas cher, moncler outlet, louis vuitton, canada goose outlet, louis vuitton, pandora charms, wedding dresses, juicy couture outlet, moncler, canada goose outlet, louis vuitton, canada goose uk, ugg uk, canada goose, ugg pas cher, coach outlet, hollister, moncler, canada goose outlet, ugg, pandora jewelry, moncler outlet, karen millen uk, pandora jewelry, links of london, louis vuitton, canada goose, canada goose, swarovski crystal, moncler uk, moncler, moncler, doudoune moncler, juicy couture outlet, canada goose jackets, thomas sabo, replica watches, ugg,ugg australia,ugg italia, barbour, pandora uk, supra shoes, lancel, toms shoes