How to identify High volume tables / Empty tables in Oracle

To maintain database and to improve the performance; we need to have an eye at huge volume tables and also we may in need to delete unused tables ( zero record tables ). 

We can identify Huge tables or Empty tables easily using NUM_ROWS


What is NUM_ROWS?

NUM_ROWS is a column available in Oracle inbuilt table  " ALL_TABLES "

The table "ALL_TABLES"  hold the key summary information about all the existing tables at Schema and database level.


Simply, to identify the tables which have more than 1 million records:-

Below is the query to execute.  It will retrieve table name, num of rows and many more information.

 SELECT   * from ALL_TABLES 
 WHERE    OWNER ='GLOGOWNER'
     AND    NUM_ROWS > 1000000;

Top 10 new features of Oracle 12c

To identify top huge volume tables :-

SELECT table_name, tablespace_name, num_rows FROM dba_tables WHERE owner='GLOGOWNER' ORDER BY num_rows DESC;

The above statement will result with table names in the order of high volume records to low number of records. 


To identify list of empty tables :-

 SELECT   * from ALL_TABLES 
 WHERE    OWNER ='GLOGOWNER'
     AND    NUM_ROWS = 0;

Article about SQL Loader

 

NOTE:-  The count of rows what we get out of the above queries are not exactly matching with live number of records; since the record count will be updated when the table has been undergone gather statistics  either by   ANALYZE statement or   DBMS_STATS package.  These statements will be normally executed by DBAs as part of maintenance activity. 

So, the count would not be exactly match with current data count but approximately matches ( 95%)


To get the count exactly match :-

Execute any of the below statement for a particular table to analyze or gather statistics. Once this is done, NUM_ROWS column will be updated with exact row count of the table.
 

 ANALYZE TABLE SHIPMENT; 
                          [OR]
  EXEC DBMS_STATS.gather_table_stats('GLOGOWNER', 'SHIPMENT');

GLOGOWNER =  Schema or User name

SHIPMENT     =  Table name

Click the link and Like this FB page  to get more articles like this.