All Forums Database
DeepakDhabade 18 posts Joined 02/14
25 Mar 2014
how to know the % table size growth/reduce in teradata

I am new to TERADATA technology. Can anyone let me know how to know the % change in table size in teradata. Is there any specific query to identify the % change in table size ???
Or else is there any table (system/DBC) from which we will get the required result.
We need to find the % change in table size for every week/15days/month.
Kindly help me to get the required data.
Thanks in adavance.
Deepak Dhabade

krishaneesh 140 posts Joined 04/13
25 Mar 2014

As such if you have the PDCR or SYS_MGMT enabled to hold the historical query log related data, you will see a table tablespace_hst which gives the associated information at a day level for all the tables in all the databases.

sk85 2 posts Joined 03/14
26 Mar 2014

Hi Deepak,
I am unsure about the tablespace_hst option mentioned above, but the following query should give you some direction towards the entire approach:




,sum (currentperm)/(1024*1024*1024) as current_GB

from dbc.tablesize

where tablename = 'FLOWN_COUPON'

and databasename = 'TEST_MOSAIC_DB'

group by 1,2

order by 1,2


I am not sure if there are dbc tables which store the % change in table size, hence, you will need to create a table of your own with a date field to store the results of the query above and then calculate the % change on your own.


Hope this helps!




Raja_KT 1246 posts Joined 07/09
26 Mar 2014

If you don't have a tool, then you can resort to using scripts(linux, perl, python etc) which will do computation.Then you can schedule your scripts as per your requirement using schedulers.
Sample query to see space:
select sum(currentperm) actualspace, max(currentperm)*(hashamp()+1) effective_space from dbc.tablesize
where databasename = 'yourdb' and tablename = 'yourtblname';

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

DeepakDhabade 18 posts Joined 02/14
28 Mar 2014

Thanks a lot for your valuable suggestions :-)
According to my knowledge if the change in table size is 10 % (It may be increase or decrease) then stats would be stale.
So I wanted to know the % change in tables, If the % change is =< 10 % then stats will stale and I wanted to work on that stats stale.
Please help me to come up from this situation.
Deepak Dhabade

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2014

If you only want to achieve this knowledge of stale-stats from this .... why dont you just create COLLECT STATS script and schedule it with your batch?
If in some tables the data volume is huge i.e. collecting stats may take alot of time, you can schedule that seperately over the weekend or non-business critical hours.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.