Re: Data size of a single table

Hello Markus,

thank you for your answer! The statement can be executed, but unfortunately there's no block data in the user tables. I also tried just

SELECT BLOCKS
FROM USER_TABLES T1
WHERE T1.TABLE_NAME = 'my_table_name';

but it didn't return any value. How can this be? I logged on with SYSTEM rights, so I guess it can't be that I haven't got enough rights. Do I have to change any options so that these facts are logged?

Thank you,
 Jörg

"Eltschinger Markus" <do_not_mail_at_test.com> schrieb im Newsbeitrag news:1047213768.309739_at_exnews...
> Hello Jörg,
>
> here's a simple query of how much space have been *used* by a table:
>
> SELECT T1.TABLE_NAME,
> T1.TABLESPACE_NAME,
> (T1.BLOCKS * T2.BLOCK_SIZE) / (1024.0 * 1024.0) AS USED_SPACE_IN_MB
> FROM USER_TABLES T1
> INNER JOIN USER_TABLESPACES T2
> ON T2.TABLESPACE_NAME = T1.TABLESPACE_NAME
> WHERE T1.TABLE_NAME = '<put here the name of the table>'
>
> but, if you want to know how much space have been *allocated* (reserved)
for a table, you could
> use:
>
> SELECT T1.SEGMENT_NAME,
> T1.TABLESPACE_NAME,
> T1.BYTES,
> (T1.BLOCKS * T2.BLOCK_SIZE) / (1024.0 * 1024.0) AS
ALLOC_SPACE_IN_MB
> FROM USER_SEGMENTS T1
> INNER JOIN USER_TABLESPACES T2
> ON T2.TABLESPACE_NAME = T1.TABLESPACE_NAME
> WHERE T1.SEGMENT_NAME = '<put here the name of the table>'
>
>
> Kind regards,
> Markus Eltschinger
> ______________________________
> Swisscom IT Services Ltd
> Data Warehouse Development
> 1752 Villars-Sur-Glâne FR
> Switzerland
> http://www.swisscom.com/it/content/index_EN.html
>
>
>
> "Jörg Brenninkmeyer" <jbrenni_at_freenet.de> wrote in message
> news:b4fafe$1c5q$1_at_redenix.uni-muenster.de...
> > Hello,
> >
> > can andybody tell me how I can see the size in KB/MB of a single table
> > within a tablespace? In the enterprise manager and the perfomance
manager I
> > only found information about entire tablespacles. Thank you!
> >
> > Greetings,
> > Jörg
> >
> >
>
>
Received on Tue Mar 25 2003 - 15:03:44 CST