Solved

check the database growth..

Posted on 2006-07-13
7
1,517 Views
Last Modified: 2010-08-05
How  to calculate growth of the database since  last one month?
0
Comment
Question by:expertblr
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now