Solved

oracle schema growth

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Two PAYER_IDs with PAYER_TYPES these two types 4 35
Oracle Database Upgrade 13 62
Oracle RMAN Database Restore 5 51
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 52
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

910 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

20 Experts available now in Live!

Get 1:1 Help Now