spool show_stat_dt.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "TABLE NAME" on "COLUMN NAME" on "INDEX NAME"; 
 
/* ************************************************ */
/*                                                  */
/* substitute the table_name variable with the new  */
/* name in CAPITAL LETTERS                          */
/*  - will show the histogram stats for a table     */
/*                                                  */
/*      @c:\my_own\oracle\sql\show_stat_dt.sql      */ 
/*                                                  */
/* ************************************************ */

select substr(TABLE_NAME, 1, 16)     "TABLE NAME",    
       substr(to_char(COLUMN_ID, '99'), 1, 3)     "NUM",
       substr(COLUMN_NAME, 1, 20)     "COLUMN NAME",
       substr(to_char(NUM_BUCKETS, '999'), 1, 4) "SIZE",
       substr(to_char(LAST_ANALYZED, 'DD-MON-YY HH:MIam'),1,18) "WHEN:",
       substr(to_char(SAMPLE_SIZE, '999,999,999'), 1, 12)    "Num Values"
from dba_tab_columns
where table_name = upper('&&TABLE_NAME') 
order by 1 asc,
         COLUMN_ID asc,
         3 asc,
         4 asc;
         
set echo off;
spool off;
undefine TABLE_NAME;
set long 80;
commit;










