Re: Q: What did I miss in sys.dba_tables?

If you are using Oracle 7+ then you have to analyze your tables to get num_rows, blocks etc information.

Method 1: (PL/SQL option required)

    SQL> execute dbms_utility.analyze_schema('SCOTT', 'COMPUTE');

    SCOTT's schema is analyzed.

Method 2: (login as table owner)

    SQL> analyze table <your table name> compute statistics;

You can either use COMPUTE or ESTIMATE parameters. If you want to delete statistics then you DELETE parameter.

2 things to note:

1. When you collect statistics (information) they are not automatically updated when you perform DML to your tables. You must rerun method 1 or 2.
2. You automatically start using ORACLE's COST BASED approach in your queries instead of using RULE BASED approach. This may speed up or slow down your queries.

Look at Administrator's Guide v.7 pg 8-62

Asim Aslam
DBA/Developer
BMC
> Hi, Oracle gurus, please shed light on this:
>
> I want to see how many rows and other information related to the
> tables in our database by querying sys.dba_tables. The query is
> as simple as this:
>
> select owner, table_name, num_rows, blocks, ...
> from sys.dba_tables;
>
> For the results, I got values for owner and table_name. However, all the
> other columns, num_rows, blocks, ..., are null! I know that a lot of tables
> in the database are not empty. What did I miss here?
>
> Thanks for your help.
>
>
Received on Fri Sep 29 1995 - 00:00:00 CET