Following script can be used to check the following information about tables and columns:
1. Table Last Analyzed date
2. Table Distinct Rows and Number of blocks
3. Columns density and Column Distinct Rows
undefine tbl_name
undefine owner
SET VERIFY OFF
SET ECHO OFF
accept tbl_name prompt ‘Enter Table Name :’
accept owner prompt ‘Enter Owner Name :’
col column_name format a30
col density format 99999999
col num_distinct format 999,999,999
select num_rows,degree,blocks,last_analyzed
from dba_tables
where table_name =upper(‘&tbl_name’)
/
select column_name,
density,
num_distinct
–low_value,
–high_value
from dba_tab_columns
where table_name =upper(‘&tbl_name’)
and owner=upper(‘&owner’)
order by column_id
/