Disk Space by Table

[fsnyder]

A group of users maintains tables in a database with a limited disk space ...

Is there a way to determine the absolute and/or relative amounts of disk space each table is taking up within the database ?

Hopefully there's an SQL based solution.

Thanks

 

[tdatgod]

.width 254

sel sum(currentperm) as permspace,
    TRIM(TRIM(databasename) || '.'  || TRIM(tablename)) 
    (format 'X(61)') as Tablename 
from dbc.tablesize
where databasename = user
order by 1 desc, 2
group by 2;

          permspace Tablename
------------------- -----------------------------------
         22,021,120 frank.tableb
             22,528 frank.CREATE_TABLE_MK2100T0
              3,072 frank.tablec
              2,048 frank.tablea

user will automatically return the database of the person logged on. if you want BOB's totals try

where databasename = 'bob'


You can play with the SQL to SUM up the database to get the total count of the database or you can simply use the

view diskspace

to get the totals across the whole database.

 

[tdatgod]

And actually if Roberta and Robert both use Database Frank you might want to also include in the select or the where clause the

AccountName

Which is the LOGON ID of the person who created the table regardless of the database name.

That way you can know if the table was created by Roberta, Robert, Frank or who else has access to database Frank.

 

[WILLIEWANKA]

You might also want to keep in mind the amount of skew for a table. If any skew exists, the effective size of the table is much larger than the above query will show. If one AMP is full, it doesn't matter how much space is available on the others.