Solved

oracle schema growth

Posted on 2011-09-20
6
2,722 Views
Last Modified: 2013-12-19
Hello All,
I am using the below mentioned sql script(oracle 10g database) to capture the schema growth for the last 1 week. I referred for the following link:http://www.dba-oracle.com/t_database_growth_reports.htm for getting this sql:

select sum(space_used_delta)/1024/1024 "Space used(M)", sum(c.bytes)/1024/1024 "Total space(M)", round(sum(space_used_delta)/sum(c.bytes)*100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a, dba_objects b, dba_segments c
where end_interval_time > trunc(sysdate) - 7
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = 'DITMGR'

Questions:
1) Sometimes, I am getting the value of  "Percent of Total Disk Usage" (third field in the above sql) as greater than 100%. Am I missing anything in the sql? How can it be more than 100%.
2) Per my understanding, the first field "Space Used (M)" (from the above sql) provides the delta space consumed for the last 1 week only and not the total space consumed before 1 week(since I am using the WHERE clause condition "end_interval_time > trunc(sysdate) - 7"). Is my understanding correct? I am surprised by the output and that is why I wanted to confirm
3) Per my understanding, the second field "Total Space(M)" is the total schema size and it should not vary based on the where clause condition here - "end_interval_time > trunc(sysdate) - 7" or "end_interval_time > trunc(sysdate) - 30" should yield the same schema size in the above sql. Correct?

It would be really helpful if you could help me in understanding the above mentioned questions. Thanks.
0
Comment
Question by:sivi_3883
  • 3
  • 3
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36568644
Did you go download the complete script?

From the link you posted there did you see the line:
...
dba_segments c
see code depot for full script
where end_interval_time > trunc(sysdate) - &days_back
...
0
 

Author Comment

by:sivi_3883
ID: 36569420
ooops!..Thanks for catching it..
hmm 80 bucks to get the full script :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36569439
There are a lot of scripts posted out there.  Many of them in the online docs.
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.

 

Author Comment

by:sivi_3883
ID: 36581124
Hello slightwv,
I tried my best in searching online. Can you please point out to the right online doc for getting oracle schema growth rate(oracle 10g Database)?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36581237
Sorry but I'm away from the office today and cannot search for them on my mobile device.

I'll look for some when I get back in.

I would try searching for the views used in the SQL you have: dba_hist_snapshot and dba_hist_seg_stat
Those are not common view and any results should be related to what you are after.
0
 

Author Closing Comment

by:sivi_3883
ID: 36910548
Did nt get the solution. But the comment was helpful
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

825 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