Jump to content
Bandi

SQL query to get,  table with row count, partitioning type, compression details.

Recommended Posts

Hi. And I, and welcome to the forum!

First, you need to re-analyze the table stats to gather num_rows:

http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm

EXEC DBMS_STATS.gather_table_stats('MY_SCHEMA', 'MY_TABLE', granularity => 'AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Next, try a query Like  this:

Sslect

   Partition_name,

   Compression,

   Num_rows

from

   dba_tab_partitions;

More notes here:


 http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Dba_tab_partitions&sa=Search&siteurl=www.dba-oracle.com%2Foracle_tips_dbms_stats1.htm&ref=www.google.com%2F&ss=10420j8625348j18

Share this post


Link to post
Share on other sites

Thank you very much for the details..

 

However, above query can only gives us the details about  partitioned tables. 

I would like t pull Num_rows for partitioned and non partitioned tables.

Share this post


Link to post
Share on other sites

Hi Bandi,

You know, if you are running this in production, you may want to do a count(*), since you risk causing SQL execution plans from changing.

To include non-partitioned tables, join into dba_tables, or better still, use the UNION operator.

If you choose a join  you may also need to qualify the join where partitioned=“NO’ .

If you don’t want to write the query yourself, I may have this script in my script collection:

http://www.rampant-books.com/download_adv_mon_tuning.htm

Good luck!

 

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×