[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

oracle schema growth

Posted on 2011-09-20
6
Medium Priority
?
3,106 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 78

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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36569439
There are a lot of scripts posted out there.  Many of them in the online docs.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

640 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