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 mbThere'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.
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
7 comments:
It's cute watching you learn stuff.
Does it matter to you how many segments the table's indexes are using? Your data dictionary query doesn't include them.
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
Of course, Toad shows this info on the Stats/Size tab in the Schema Browser, but a handy script nonetheless.
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.
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
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.
Post a Comment