Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-26-2012
06:45 AM

09-26-2012
06:45 AM

Help required in querying dbc tables

Hello,

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

**and**

**GROUP** **BY** 1,2

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-26-2012
10:24 PM

09-26-2012
10:24 PM

Re: Help required in querying dbc tables

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-02-2012
12:39 AM

10-02-2012
12:39 AM

Re: Help required in querying dbc tables

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.