select owner,
table_name,
column_name,
num_distinct,
last_analyzed
from all_tab_col_statistics
where owner in (‘EXAM1′,’TEST2′,’EXAMPLE2’);

select *
from all_part_col_statistics;

select *
from all_indexes;

select *
from sys.aux_stats\$;

select owner,
table_name,
last_analyzed
from all_tab_col_statistics
where owner not in (‘SYS’,’PERFSTAT’,’DBSNMP’,’SYSTEM’,’SYSMAN’);

select table_name,
clustering_factor,
num_rows
from dba_indexes
where table_owner in (‘SCHEMA_OWNER’) and
clustering_factor > 0;

select table_name,
clustering_factor,
num_rows
from dba_indexes
where table_owner in (‘SCHEMA_OWNER’) and
clustering_factor > 0;

select owner from dba_tables where table_name = ‘TEST_SETUP’;

select count(*) from dnalims.test_setup;
select num_rows from dba_tables where table_name=’TEST_SETUP’;

set linesize 150
set pagesize 66
set head on
column table_name format a15 heading ‘Table’
column index_name format a15 heading ‘Index’
column num_rows format 999,999,999 heading ‘Rows’
column num_blocks format 999,999 heading ‘Data Blocks’
column avg_data_blocks_per_key format 999,999 heading ‘Data Blks /Key’
column avg_leaf_blocks_per_key format 999,999 heading ‘Leaf Blks /Key’
column clustering_factor format 999,999 heading ‘Clst Fact’
column blocks format 999,999 heading ‘Blks’
column t.num_rows/i.clustering_factor format 999,999 heading ‘Ratio’

SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,’MM/DD/YYYY HH24:MI:SSSSS’) Created
from dba_indexes i,
dba_objects o,
dba_tables t
where i.index_name = o.object_name and
i.table_name = t.table_name and
table_owner = ‘DNALIMS’ and
t.table_name = ‘TEST_SETUP’ and
t.num_rows > 0 and
i.clustering_factor > 0
order by 1;
/

set linesize 150
set pagesize 66
set head on
column table_name format a15 heading ‘Table’
column index_name format a15 heading ‘Index’
column num_rows format 999,999,999 heading ‘Rows’
column num_blocks format 999,999 heading ‘Data Blocks’
column avg_data_blocks_per_key format 999,999 heading ‘Data Blks /Key’
column avg_leaf_blocks_per_key format 999,999 heading ‘Leaf Blks /Key’
column clustering_factor format 999,999 heading ‘Clst Fact’
column blocks format 999,999 heading ‘Blks’
column t.num_rows/i.clustering_factor format 999,999 heading ‘Ratio’

SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,’MM/DD/YYYY HH24:MI:SSSSS’) Created
from dba_indexes i, dba_objects o, dba_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
and table_owner = ‘DNALIMS’
and t.num_rows > 0
and i.clustering_factor > 0
order by 1;
/

Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool c:\bei\tmp
Select ‘Select ”’||owner||’.’||table_name||’ : ”||count(*) from ‘||table_name||’;’,
to_char(sysdate, ‘YYYYMMDDHH24MISS’) d, user u
from user_tables
where owner not in (‘SYS’,’SYSTEM’,’PERFSTAT’)
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off

Select ‘WRM$_WR_CONTROL : ‘||count(*)||num_rows from WRM$_WR_CONTROL;
column table_name format a15 heading ‘Table’
column index_name format a15 heading ‘Index’
column num_rows format 999,999,999 heading ‘Rows’
column num_blocks format 999,999 heading ‘Data Blocks’
column avg_data_blocks_per_key format 999,999 heading ‘Data Blks /Key’
column avg_leaf_blocks_per_key format 999,999 heading ‘Leaf Blks /Key’
column clustering_factor format 999,999 heading ‘Clst Fact’
column blocks format 999,999 heading ‘Blks’
column t.num_rows/i.clustering_factor format 999,999 heading ‘Ratio’

SELECT i.table_name,
i.index_name,
t.num_rows,
t.blocks,
i.avg_data_blocks_per_key,
i.avg_leaf_blocks_per_key,
i.clustering_factor,
t.num_rows/i.clustering_factor,
to_char(o.created,’MM/DD/YYYY HH24:MI:SSSSS’) Created
from dba_indexes i, dba_objects o, dba_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
and table_owner = ‘DNALIMS’
and t.num_rows > 0
and i.clustering_factor > 0
order by 1;
/

Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool c:\bei\tmp
Select ‘Select ”’||owner||’.’||table_name||’ : ”||count(*) from ‘||table_name||’;’,
to_char(sysdate, ‘YYYYMMDDHH24MISS’) d, user u
from user_tables
where owner not in (‘SYS’,’SYSTEM’,’PERFSTAT’)
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off

Select ‘WRM$_WR_CONTROL : ‘||count(*)||num_rows from WRM$_WR_CONTROL;