OraFAQ Forum: Server Administration » Calculate the actual size (in MB and KB) of a table and index

Home » RDBMS Server » Server Administration » Calculate the actual size (in MB and KB) of a table and index - SQL scipts?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60516]

Wed, 11 February 2004 22:53

Patrick Tahiri
Messages: 119
Registered: January 2004

Senior Member

Hi,

I would like to know where I can find a script or a SQL batch (that I can download or copy) to evaluate/calculate the actual size of an existing table in MB and KB?

Thank you very much for your help!

Regards,

Patrick Tahiri.

Report message to a moderator

Re: Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60520 is a reply to message #60516]

Thu, 12 February 2004 01:42

Thiru
Messages: 1089
Registered: May 2002

Senior Member

Patrick,
you can get the size from dba/all/user_tables after analyzing the concerned tables/schema ..

something like this..

thiru@9.2.0:SQL>select table_name,round(blocks*blk_size/1024) "size(K)",round(blocks*blk_size/1024/1024,2) "size(M)"
  2  from dba_tables,(select value blk_size from v$parameter where name='db_block_size')
  3  where owner='THIRU';

TABLE_NAME                        size(K)    size(M)
------------------------------ ---------- ----------
B                                       0          0
BIG_EMPTY                               0          0
CHILD                                   0          0
EMP                                    16        .02
EMP2                                   16        .02
EMPT                                   16        .02
ERRORS                                412         .4
......
....

-Thiru

Report message to a moderator

Re: Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60521 is a reply to message #60520]

Thu, 12 February 2004 02:19

Patrick Tahiri
Messages: 119
Registered: January 2004

Senior Member

Hi Thiru,

Thank you for the SQL script but I receive no rows back from the query? Strange...

SELECT dbat.table_name
,round(dbat.blocks*v_param.blk_size/1024) "size(K)"
,round(dbat.blocks*v_param.blk_size/1024/1024,2) "size(M)"
FROM dba_tables dbat
,(select value blk_size from v$parameter where name='db_block_size') v_param
WHERE table_name = 'log_incoming';
-- ALL THE TABLES: dbat.owner='sms_user';

Thank you for your help!

Regards,

Patrick Tahiri.

Report message to a moderator

Re: Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60522 is a reply to message #60521]

Thu, 12 February 2004 02:38

Thiru
Messages: 1089
Registered: May 2002

Senior Member

where table_name='LOG_INCOMING' ;

also you might want to add AND owner='OWNER_NAME' to narrow down.

-Thiru

Report message to a moderator

Re: Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60523 is a reply to message #60522]

Thu, 12 February 2004 02:48

Patrick Tahiri
Messages: 119
Registered: January 2004

Senior Member

This is great!! It worked! Case sensitive...

Thank you very much!!

Best regards,

Patrick Tahiri.

Report message to a moderator

Re: Calculate the actual size (in MB and KB) of a table and index - SQL scipts? [message #60862 is a reply to message #60520]

Wed, 10 March 2004 17:09

calculations for dummies
Messages: 1
Registered: March 2004

Junior Member

what is the comparison /calculation of 20.4kb and 1mb?
dell windows xp i am not very compuer savvy so email me i give details later if needed.

Report message to a moderator

Previous Topic:	Database Recovery
Next Topic:	Euro Symbol again.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 07 22:53:01 CST 2025