I am trying to get the table space size occupied per table in a database. The below query gives me for a table. But I need space occupied by a table for a single or few partitions within the table. Thanks in advance
SELECT DATABASENAME, TABLENAME, SUM(CURRENTPERM) /(1024* 1024 * 1024) (decimal(15,6))
FROM DBC.TABLESIZE a, dbc.tables b
WHERE a.DATABASENAME ='xxx'
AND a.TABLENAME = 'xxx'
and a.databasename = b.databasename
and a.tablename = b.tablename
GROUP BY 1,2
I don't think, you can get this info through any of the DBC tables. I am not sure if any of the databse utilities have any such command, but I'll check.
There is one way through which at least you can have some rough estimate:
1) Calculate the size of the table
2) Calculate the number of rows in a table
3) Now derive the partition size based on number of rows in each partition divided by total number of rows in a table multiply by the table size
With the help of some complex sql, sub-query and using derive tables etc, you can get the output in desired format.
Thanks pawan. I was able to do like that which gives me rough estimates. I am still wondering if any dbc.tables would be helpful as it is partitioned.