Monday, February 1, 2010

Database Table Size

I've always wondered how big a table is...up until recently I depended on the DBAs to retrieve such information for me.

Thanks to my good, and very helpful, friend, Mr. Thomas Roach, I no longer have to wait or bother the DBAs.

%_SEGMENTS contains a column called BYTES. Use this column to determine the size of your table, with just a little math.
SELECT segment_name, SUM( bytes ) / 1024 / 1024 mb
FROM user_segments
GROUP BY segment_name
ORDER BY 1
/

SEGMENT_NAME MB
------------------------------ ----------
BMP_DIVNBR_CUST 13.375
BMP_DIVNBR_JOINFACT 37.0625
BMP_DIVNBR_PROD 13.375
BMP_DIVNBR_SALES 78.6875
CUST 940
DIV .125
IDX_CUSTSKDIVNBR_SALES 2676.6875
IDX_PRODSKDIVNBR_SALES 2701.6875
JOIN_FACT 4298.125
PIM 312
PK_DIVSK .125
PK_PIMSK 8
PK_TMSK .25
PROD 2274.4375
SALES 116122.375
TIME .875
UQ_CUSTSKDIVNBR_CUST 40.125
UQ_PRODSKDIVNBR_PROD 144
There's a plethora of these scripts out in the wild...but I was originally inspired by helping Mr. Neil Kodner out back in November (which I refer to as the "missing" month). Read his take on it here.

7 comments:

Clever Idea Widgetry said...

It's cute watching you learn stuff.

Clever Idea Widgetry said...

Does it matter to you how many segments the table's indexes are using? Your data dictionary query doesn't include them.

oraclenerd said...

not yet...but funny thing, I added a sum of extents and blocks shortly after posting this. I suppose a count of segments would be helpful (but I wouldn't know how to interpret it yet).

(cute)chet

hillbillyToad said...

Of course, Toad shows this info on the Stats/Size tab in the Schema Browser, but a handy script nonetheless.

Tom said...

Ah, but for TOAD to work, it queries the stats from DBA_TABLE, DBA_TAB_PARTITIONS and the like, so unless your stats are up to date, the numbers will be off.

DBA_SEGMENTS is real time and it shows how many blocks the table is chewing up.

Now you can update stats on the objects using DBMS_STATS.GATHER_TABLE_STATS and then do some math.

In DBA_TABLES and DBA_TAB_PARTITIONS it might be close to accurate depending on if you did estimate_percent or not. You can also take num_rows *avg_row_len to get how much space your rows are actually taking up "approximately" that is.

Tom said...

Chet, I see the tables and indexes. Try this. It will organize the tables with their respective indexes. That (hopefully you can add the rest in your head)

SELECT lpad(' ', 8*(level-1)) || segment_name segment_name, object_type, mb
FROM
(SELECT a.segment_name, b.table_name, decode(b.index_name, null, 'TABLE', 'INDEX') object_type, SUM( a.bytes ) / 1024 / 1024 mb
FROM dba_segments a, dba_indexes b
WHERE a.OWNER = 'BI'
and a.segment_name = b.index_name (+)
GROUP BY segment_name, b.table_name, b.index_name)
start with table_name is null
connect by prior segment_name = table_name

Tom said...

Man, I can't even believe I wrote that script. If you asked me to write it today, I am not so sure I could. Amazing how you forget stuff. Then again, you can surprise yourself with how easy you are to repick things up or recall.