0

I have been trying to figure this out for a while. I want to know how many data blocks are being used by my tables. The tables have a huge number of rows, around 200,000. But when I run the following query:

select table_name, blocks from all_tables where owner='me';

I get the following output :

TABLE1    0
TABLE2    0
TABLE3    0

and so on for all my tables. Why is this so? Is the total used data blocks not being read properly, or is there something that needs to be done to correct it?

Ajoy
  • 1,838
  • 3
  • 30
  • 57
  • 1
    This question and answers might help give you what you need: http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle – Bravax Aug 09 '13 at 09:17

1 Answers1

2

That information is only updated when you gather statistics (using the dbms_stats package). It's not a dynamic view.

Another way to get this information is via the dba_segments view instead of dba_tables. This is rather expensive/slow though.

Mat
  • 202,337
  • 40
  • 393
  • 406
  • Thansk @Mat. Querying the blocks using dba_segments worked. Could you tell me how I could update the *_tables using dbms_stats? – Ajoy Aug 09 '13 at 09:27
  • 1
    Google "oracle gather statistics" for tons of info. If you don't know about statistics, I urge you to read about them ASAP, it's a pretty important thing in Oracle. – Mat Aug 09 '13 at 09:30