Re: Display number of blocks of a table

> Hello,
>
> I tried the following statement to get the size of a table, but it just
> doesn'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?
>
> SELECT BLOCKS
> FROM USER_TABLES T1
> WHERE T1.TABLE_NAME = 'my_table_name';
>
> Thank you for any answers!

Joerg,

There are two possibilities why the query didn't return anything meaningfully:

First: If you're logged on as system and query user_tables, you do in fact query tables that belong to system. In fact, querying user_tables always gives you the characterstics of only tables that belong to the user who issues this query. So, either do

select ... from all_tables where owner = 'JOERG' and table_name = 'my_table_name'

or

select ... from user_tables where table_name = 'my_table_name' (logged on as table owner)

Sedond: You have to invoke dbms_stats.gather_table_stats:

exec dbms_stats.gather_table_stats(<owner>, 'my_table_name) in order to populate the block field.

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html