Statistics

Submitted by dave on Tue, 11/10/2009 - 17:49

Table/Histogram Summary

This can be useful if you have a specific list of tables you are interested in. 
SELECT OWNER, 
       TABLE_NAME,
       (SELECT COUNT(*) FROM DBA_HISTOGRAMS WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB1,
       (SELECT COUNT(*) FROM DBA_HISTOGRAMS@TMOTST1 WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB2
FROM DBA_TABLES
WHERE OWNER = UPPER('&OWNER_NAME.') 
ORDER BY 1,2;

Table Column Histogram Detail

Needs work! 
SELECT OWNER, 
       TABLE_NAME,
       COLUMN_NAME
--       ,
--       (SELECT COUNT(*) FROM DBA_HISTOGRAMS WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB1,
--       (SELECT COUNT(*) FROM DBA_HISTOGRAMS@TMOTST1 WHERE DBA_TABLES.TABLE_NAME=DBA_HISTOGRAMS.TABLE_NAME and DBA_TABLES.OWNER=DBA_HISTOGRAMS.OWNER) as HIST_PARTS_DB2
FROM DBA_TAB_COLUMNS
WHERE OWNER = UPPER('&OWNER_NAME.') 
ORDER BY 1,2,DBA_TAB_COLUMNS.COLUMN_ID;