Know your tables in Oracle

DBA_TABLES View 

Have you ever thought of knowing statistics about your tables in oracle.
Statistics in terms of Owner,Tablespace, Number of Rows, Number of Blocks used, Number of Blocks empty,etc.
I was just trying to see what system tables does oracle has with it.
I tried

select * from tab;

BOOM BOOM what a surprise More then 2000 objects listed ...................
some of these objects are tables and some of these objects are views.



Going ahead I thought Lets concentrate on each of the objects individually
So I started with a view called 'DBA_TABLES'

Interestingly DBA_TABLES is really an important view when you want to know more about your tables

DBA_TABLES describes all relational tables in the database. Its columns are the same as those in ALL_TABLES.

Some important columns of DBA_TABLES and their use.

OWNER

Owner of the table

TABLE_NAME 

Name of the table 

TABLESPACE_NAME 

Name of the tablespace containing the table

NUM_ROWS 

The number of rows in the table 

BLOCKS 

The number of used blocks in the table

TABLE_LOCK 

Whether table locking is enabled or disabled

EMPTY BLOCKS 

The number of empty ( never used ) block in the table

I think these columns will be more then enough to get information on your tables.

So lets start to know your tables in Oracle Database with some use cases.

1. You want to know how many rows does your each table have for a particular schema/user

SQL> select owner,table_name,num_rows from DBA_TABLES where owner='HR';

OWNER            TABLE_NAME                       NUM_ROWS

------------------------------ ------------------------------ ----------

HR                     REGIONS                                   7

HR                     LOCATIONS                              23

HR                     DEPARTMENTS                        27

HR                     JOBS                                           19

HR                     EMPLOYEES                             107

HR                     JOB_HISTORY                           10

HR                     COUNTRIES                                25

7 rows selected.

2. Yow want to know which tables use how many blocks and how many empty blocks are present for each table. ( got a question as to what is a Data Block : is smallest logical unit to store Oracle Data. )

SQL> select table_name,blocks,empty_blocks from DBA_TABLES where owner='HR';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

------------------------------ ---------- ------------

REGIONS                                         5            0

LOCATIONS                                    5            0

DEPARTMENTS                              5            0

JOBS                                                 5            0

EMPLOYEES                                    5            0

JOB_HISTORY                                 5            0

There it is , You can see no table of yours has any Empty blocks.

3. You want to see how many total rows( from all the tables ) are there for your multiple users. In short you want to see total rows contained by each user for all his tables.

SQL> select owner, sum(num_rows) as Total_row_count from DBA_TABLES where owner

='HR' or owner='SCOTT' group by owner;

OWNER                          TOTAL_ROW_COUNT

------------------------------ ---------------

HR                                              218

SCOTT                                       23

One more way of having this result is using the case statement

SQL> select owner,case when owner='HR' then sum(num_rows)

           when owner='SCOTT' then sum(num_rows)

           end as total_row_count

            from DBA_TABLES

           group by owner

           having owner in ('HR','SCOTT');

OWNER                          TOTAL_ROW_COUNT

------------------------------ ---------------

HR                                               218

SCOTT                                       23

4. You want to see the top two tables having the largest number of records in your user/schema

SQL> select owner, table_name, num_rows from (select owner, table_name, num_rows

           from DBA_TABLES where owner='HR' order by num_rows desc) where rownum < 3;

OWNER                          TABLE_NAME                       NUM_ROWS

------------------------------ ------------------------------ ----------

HR                             EMPLOYEES                                   107

HR                             DEPARTMENTS                              27

Here it is, The top two tables with the highest number of rows in your schema.

One more important Tip which I would like to tell you. DBA_TABLES does not update all of its columns automatically. Instead it has to be updated manually.

A scenarios which you will come across scratching your head.

Supposedly you query the DBA_TABLES and get count of records for a particular table.

The count comes to be 1000 rows

Then you perform some bulk loading on the same table and insert around more 5000 records

Now you perform a 

select count(*) from table_name; on the same table 

The count should be 6000 rows.

Now you check the count using the query on DBA_TABLES

SQL> select owner,table_name,num_rows from DBA_TABLES where owner='HR' and table_name='table_name';

The count shown by DBA_TABLES is still 1000........

Surprised !!! Don't be surprised this is the observed behavior.

The reason you are getting a different value in the count of records on table in DBA_TABLES is because the num_rows column in the DBA_TABLES needs to updated using the analyze function

Just try this

analyze table_name compute statistics;

Yes thats it. 

Now you can check the DBA_TABLES for the num_records value for your table and its updated.

In this blog we studied about the DBA_TABLES view . Stay tuned for more information on Oracle Administration

Please let me know your comments on this posts.

 If you find anything wrong, please correct me.