This content has been marked as final. Show 8 replies
I don't know if you can monitor schema space growth but you can try to monitor space growth at object level with DBMS_SPACE.OBJECT_GROWTH_TREND: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#CHDBHGEI.
Edited by: P. Forstmann on 19 févr. 2010 09:00
If you're licensed for AWR then you can look at DBA_HIST_SEG_STAT & DBA_HIST_SEG_STAT_OBJ - that will give you a certain window depending on your AWR retention settings.
Then again, you can also sample dba_segments, etc - you just need to put this sampled data somewhere.
Here's a general article on capturing trends from statspack/awr. Although the particular segment size information you're after is not in statspack/awr, it may give general ideas.
You could write a small script that calculates the segment growth from dba_segments for that particular schema & inserts the value into a table.
Schedule the script to run daily/weekly/monthly.
Use the table to get an idea of schema growth over time.
as a side question, how often will the dba_segments view show updated information, is it immediate, do you have to re-run stats. I am trying to monitor growth on my databases as well, but it does not seem to be showing any changes in tables that I know data is going into. I am querying the dba_segments view.
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
I am querying the dba_segments view.Correct.
Space is "pre-allocated" in "big chunks"; exactly how depends upon answer to above questions
Currently the servers are Windows 2003, and the database version is 22.214.171.124
So only when more space is allocated is when it will be shown in the view.
So only when more space is allocated is when it will be shown in the view.CORRECT
DBA_SEGMENTS will be updated whenever a new segment/extent is added to an object.