Solved

check the database growth..

Posted on 2006-07-13
7
1,523 Views
Last Modified: 2010-08-05
How  to calculate growth of the database since  last one month?
0
Comment
Question by:expertblr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Accepted Solution

by:
MohanKNair earned 30 total points
ID: 17099274
Compare the contents dba_extents last month and today. Take a snapshot of the data every month.

create table my_extents_data as select * from dba_extents where 1=2;
alter table my_extents add(curdt date);

Run this command every month

insert into my_extents_data(curdt, OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO) select sysdate, OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO from dba_extents;
0
 

Author Comment

by:expertblr
ID: 17099420
alter table my_extents add(curdt date);

I think its is :

alter table my_extents_data add(curdt date);

But this table contains only what having the dba_extents ...

>Compare the contents dba_extents last month and today. Take a snapshot of the data every month.

what fields i need to compare...

Please explain detail...
0
 

Author Comment

by:expertblr
ID: 17099488
Thinks so i need to check the sum of blocks... right..

select sum(blocks) from dba_extents;

It will give the total sum of blocks of the database... right...

If not explain how?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:MohanKNair
ID: 17099512
select a.dbsize, b.dbsize
FROM
(select round(sum(bytes)/(1024*1024)) dbsize from dba_extents) a,
(select round(sum(bytes)/(1024*1024)) dbsize from my_extents_data where to_char(curdt,'mmyyyy')='062006') b;
0
 
LVL 35

Expert Comment

by:johnsone
ID: 17100595
Instead of DBA_EXTENTS, you could look at DBA_SEGMENTS.  DBA_SEGMENTS rolls up the extent information to the object level.  I am not sure of the detail you are looking for, but DBA_SEGMENTS should do the job with a less data.
0
 

Author Comment

by:expertblr
ID: 17101082
Jonesone,

will u please give the query with the dba_segments..
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 20 total points
ID: 17101969
Same queries, just change the view name.

create table my_segments as select * from dba_segments where 0=1;
alter table my_segments add load_date date;
insert into my_segments select a.*, sysdate from dba_segments a;

That would give you the data to look at.  From there, you could slice and dice it any way you want.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question