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;