Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3033
  • Last Modified:

oracle schema growth

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
sivi_3883
Asked:
sivi_3883
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
sivi_3883Author Commented:
ooops!..Thanks for catching it..
hmm 80 bucks to get the full script :)
0
 
slightwv (䄆 Netminder) Commented:
There are a lot of scripts posted out there.  Many of them in the online docs.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
sivi_3883Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sivi_3883Author Commented:
Did nt get the solution. But the comment was helpful
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now