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.
at

7 comments:

  1. Clever Idea WidgetryFebruary 1, 2010 at 11:59 AM

    It's cute watching you learn stuff.

    ReplyDelete
  2. Clever Idea WidgetryFebruary 1, 2010 at 4:41 PM

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

    ReplyDelete
  3. oraclenerdFebruary 1, 2010 at 4:48 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

    ReplyDelete
  4. hillbillyToadFebruary 2, 2010 at 9:50 AM

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

    ReplyDelete
  5. TomFebruary 2, 2010 at 7:47 PM

    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.

    ReplyDelete
  6. TomFebruary 2, 2010 at 8:38 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

    ReplyDelete
  7. TomAugust 27, 2019 at 4:28 AM

    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.

    ReplyDelete