Data / Data files / Database GROWTH

[symon_braunbaer]

Dear experts,

I have a practical question on reading / determining the exact fluctuations in the sizeof

a database.

I am publishing this question in the oracle section, as my database is oracle, but if I am

not wrong, all this should be valid for all databases.

So, the question itself: I have a system, where all the datafiles / tablespaces are set to

AUTOEXTEND, the size for each growth is 200 MB (meaning, if growing automatically,

the increment size will be 200 MB). Now I would like to see, if datafiles grew automatically,

lets say for today! And if yes - by how much increments.

Furthermore, I would like to ask - browsing ST04, in Space / Database / Overview on the

history tab - all the daily / weekly / monthly changes - how to evaluate, whether this was

only an "internal" growth, when the database grew in account of decreasing the free space

in the DB itself, and when it has also caused a data file to grow automatically, as it has its

AUTOEXTEND option enabled ???

[former_member645914]

Hi,

For Example:

Consider you have a datafile 1 with Autoextended ON. With the MaxSize 5GB and next extent 200M. Next extent size 200 MB will be allocated when there is a operation triggered and there is no option to quantify how many extents will be created. Extent allocation is purely based on the amount of operation happens. datafile 1 will grow till 5GB.

When you monitor the growth history in ST04.it shows you the tablespace growth which is logical. it has got the Total Size and Free Space and the Auto Extend Size which is till the size it can grow.

If you need any further clarification write back.

Hope this helps.

Regards,

Baskar

[former_member188883]

Hi Symon,

You may query following tables:

dba_hist_seg_stat

dba_hist_tablespace_stat

Also you may check with following query and get desired results

select file_name, bytes, maxbytes, increment_by*(bytes/blocks) "INCREMENT",

maxbytes-bytes remaining, (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS

from dba_data_files where autoextensible = 'YES'

Hope this helps.

Regards,

Deepak Kori

[symon_braunbaer]

Hello Deepak,

thanks, this is an information that is nice to know.

But coming once back to the question - is there a way to query if a datafile grew recently in size (because it is AUTOEXTENSIBLE) and when ?

[symon_braunbaer]

Dear Deepak,

after quite a lot of googling,I found this:

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname,

max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,

max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB

FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt

WHERE tsu.tablespace_id= ts.ts#

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name

AND ts.tsname NOT IN ('SYSAUX','SYSTEM')

GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname

ORDER BY ts.tsname, days;

It comes the closest to what I really need. And what I need, is the above query,

but by data file, not by tablespaces.

Thanks a lot!!