Updating the NUM_ROWS in USER_TABLES in Oracle

Good or bad, I use the NUM_ROWS values in the USER_TABLES table to help filter lists of tables when presenting them to the user. If I have a database and only about 25% of tables have records (and there are thousands of tables), then it is a lot nicer to only sift through the tables that do have records, and knowing how many records is even better (even if it is only an estimate). My problem is that these values don't just get updated automatically for you. You might find that they are all 0 even when there is data present. You can update all of the values with something like this: 

exec dbms_stats.gather_schema_stats('mySchema', cascade=>TRUE) 

A lot more info on this topic can be found at: 

http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm  For example: num_rows1 exec dbms_stats.gather_schema_stats('OC_EOC', cascade=>TRUE); PL/SQL procedure successfully completed. ruw_num2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: