Solved

oracle schema growth

Posted on 2011-09-20
6
2,654 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

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 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

26 Experts available now in Live!

Get 1:1 Help Now