ORACLENERD: Database Table Size

Database Table Size

Monday, February 1, 2010

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.

Labels: dba, howto, oradb, sql

¶ 2/01/2010 11:47:00 AM

Comments:

It's cute watching you learn stuff.

# posted by

oraclenude : February 1, 2010 at 11:59 AM

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

# posted by

oraclenude : February 1, 2010 at 4:41 PM

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

# posted by

oraclenerd : February 1, 2010 at 4:48 PM

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

# posted by

hillbillyToad : February 2, 2010 at 9:50 AM

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.

# posted by

Tom : February 2, 2010 at 7:47 PM

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

# posted by

Tom : February 2, 2010 at 8:38 PM