Passion is like genius; a miracle.

Blog on Software, Statistics, and Quant

computing tablesize in oracle


* 사전조건

pctfree 0 으로 만들고, alter table move 를 사용해 compact시킬 것. exp/imp는 compact 하게 만들어주지 않음.

* 질의만으로 처리하기

analyze 한 뒤,

SQL> compute sum of "TABLE SIZE(MB)" on report
SQL> break on report
  1  select table_name, (8192-avg_space)*blocks/1024/1024 
     "TABLE SIZE(MB)" from user_tables
  2* where table_name like 'K%'

TABLE_NAME                     TABLE SIZE(MB)
------------------------------ --------------
K0                                 100.124348
K1                                 94.5852556
K2                                 84.0471172
K3                                 88.2299194
K4                                 75.6872177
K5                                 50.0614929
K6                                 21.6360245
K7                                 7.16076374
sum                                521.532139

8 rows selected.


AFAIK, 인덱스 용량 산정은 질의만으로 해결하지못함.


Leave a Reply

Your email address will not be published. Required fields are marked *