Saturday, September 1, 2007

HOW TO CHECK GROWTH OF THE DATABASE......?

Step : 1 Calculate total Size of tablespace
select sum(bytes)/1024/1024 "TOTAL SIZE (MB)" from dba_Data_files
Step : 2 Calculate Free Space in Tablespace
select sum(bytes)/1024/1024 "FREE SPACE (MB)" from dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
select t2.total "TOTAL SIZE",t1.free "FREE SPACE",(t1.free/t2.total)*100 "FREE (%)" ,(1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 , (select sum(bytes)/1024/1024 total from dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
create table db_growth
as select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE% "
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
insert into db_growth
select *
from (
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
create view v_db_growth
as select *
from
(
select sysdate,t2.total "TOTAL_SIZE",t1.free "FREE_SPACE",(t1.free/t2.total)*100 "FREE%"
from
(select sum(bytes)/1024/1024 free
from dba_free_space) t1 ,
(select sum(bytes)/1024/1024 total
from dba_Data_files) t2
)

Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
insert into db_growth select *
from v_db_growth
Step : 8 Check everything goes fine.
select * from db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
alter session set nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
declare
jobno number;
begin
dbms_job.submit(
jobno, 'begin insert into db_growth select * from v_db_growth;commit;end;', sysdate, 'SYSDATE+ 24', TRUE);
commit;
end;

PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
select * from user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
exec dbms_job.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 13 Finally all data populated in db_growth table
select * from db_growth

18 comments:

Anonymous said...

Hi,
Whats the difference b/w step 4 and step 5?

Anonymous said...

Hi i just want to know the concept.

rgds
lara

Anonymous said...

Hello this is kinda of off topic but I was wanting to know
if blogs use WYSIWYG editors or if you have
to manually code with HTML. I'm starting a blog soon but have no coding know-how so I wanted to get advice from someone with experience. Any help would be enormously appreciated!

Here is my page - LeParfait

Anonymous said...

Thanks for sharing your info. I truly appreciate your efforts and I am waiting for your further write
ups thank you once again.

Also visit my weblog: Test Force Xtreme Reviews

Anonymous said...

Saved as a favorite, I like your blog!

Look at my blog; Pure Garcinia Cambogia

Anonymous said...

Hey I know this is off topic but I was wondering if you knew
of any widgets I could add to my blog that automatically tweet my newest twitter updates.
I've been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

Look at my webpage: Saphire Ecigs

Anonymous said...

Thankfulness to my father who shared with me regarding this website, this weblog is
in fact amazing.

Here is my webpage; Cambogia Extract

Anonymous said...

I do not know whether it's just me or if everybody else experiencing problems with your website. It looks like some of the written text within your content are running off the screen. Can somebody else please comment and let me know if this is happening to them as well? This might be a issue with my browser because I've had this happen previously.

Kudos

Here is my web page; Le derme luxe

Anonymous said...

Hi there, I log on to your new stuff daily. Your humoristic style is awesome, keep doing what
you're doing!

My web blog; Pay day loan lender

Anonymous said...

Your way of describing everything in this piece of writing is actually pleasant, every one can easily be aware of it, Thanks a lot.


Feel free to surf to my webpage: Garcinia Cambogia Diet

Anonymous said...

Excellent article. Keep writing such kind of info on your page.

Im really impressed by it.
Hey there, You have done a great job. I'll definitely digg it and individually suggest to my friends. I am confident they'll be benefited from this web site.


Look at my weblog: Skin care

Anonymous said...

Keep on working, great job!

Stop by my homepage ... party pills cheap

Anonymous said...

Hello mmy fɑmnily member! I want too ѕay that thi aarticle
is аmazing, ցreat written and come with approximately all significant infos.
I'd lke to look more posts liike this .

Here iѕs my web blog: how much is my used car worth

Anonymous said...

e cigarette, e cigarette health, e cigarette forum, electronic cigarettes, e cigarette reviews, vapor cigarette

raybanoutlet001 said...

nike shoes
sac longchamp
ugg boots
oakley sunglasses
ed hardy
omega watches sale
ralph lauren
ugg boots
michael kors bags
michael kors bags

Unknown said...

hermes belt
jordan 11
adidas ultra boost
nike air zoom
balenciaga shoes
adidas ultra boost
adidas tubular
baseball jerseys
longchamp outlet
golden goose

jeje said...

nfl jerseys
michael kors
hermes birkin
lebron 13 shoes
nike dunk low
converse shoes
yeezy boost
adidas yeezy boost
chrome hearts online
light up shoes

theesea said...

Extra resources replica bags london original site replica bags in london Dolabuy Hermes replica bags on amazon