Oracle 分析表

分析表

select owner,table_name,TABLESPACE_NAME from dba_tables where table_name=upper('&1');

select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='&1'; 

select a.column_name,
    b.num_rows,
    a.num_distinct Cardinality,
    round(a.num_distinct/b.num_rows*100,2) selectivity,
    a.histogram,
    a.num_buckets
    from dba_tab_col_statistics a,dba_tables b
        where a.owner=b.owner
            and a.table_name=b.table_name
            and a.owner=upper('&1')
            and a.table_name=upper('&2');

 col COLUMN_NAME for a25
col index_name for a30
col table_owner for a15
select i.table_owner,
       i.index_name,
       i.status,
       c.COLUMN_NAME,
       c.COLUMN_POSITION,
       i.NUM_ROWS,
       s.NUM_DISTINCT,
       s.NUM_NULLS,
       i.LAST_ANALYZED
  from dba_indexes i, dba_ind_columns c, dba_tab_col_statistics s
 where i.index_name = c.index_name
   and i.table_name = s.table_name
   and c.COLUMN_NAME = s.COLUMN_NAME
   and i.table_name = upper('&1')
   and i.table_owner = upper('&2')
 order by 1,2, 5;

set linesize 200 pagesize 900
col owner for a15
col table_name for a20
col partition_name for a20
col subPARTITION_NAME for a20
col PARTITION_POSITION for 9999
col SUBPARTITION_POSITION for 9999
select * from dba_tab_statistics where table_name = upper('&table') order by PARTITION_NAME, subPARTITION_NAME;

表权限：

set linesize 300 pagesize 900
col GRANTEE for a20
col owner for a15
col TABLE_NAME FOR A40
COL PRIVILEGE FOR A10
select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where TABLE_NAME =upper('&table') order by PRIVILEGE;

表大小：

select owner,sum(bytes)/1024/1024 MB from dba_segments where owner is not null group by owner order by sum(bytes);
select sum(bytes)/1024/1024/1024 GB from dba_segments where segment_name =upper('&segment_name') and owner=upper('&owner');

---

表索引：

set linesize 200 pagesize 900
col column_name for a30
col index_name for a30
col density for 0.9999999999999
col index_type for a10
col tablespace_name for a30
col density for 9.999999999999
col histogram for a15
select a.index_name, a.column_position, a.column_name, a.char_length, a.descend, b.num_distinct, b.density, b.histogram from dba_ind_columns a, dba_tab_columns b where a.table_name =upper('&table') AND a.TABLE_OWNER=upper('&owner') and a.table_name=b.table_name and a.table_owner=b.owner and a.column_name=b.column_name order by index_name, column_position, column_name;

---

表列：

col owner for a15
col table_name for a15
col column_name for a30
col data_type for a15
col low_value for a30
col high_value for a30
col density for 9.999999999999
col histogram for a15
set linesize 300 pagesize 900
select column_id,column_name,data_type,DATA_LENGTH LENGTH,num_distinct,density,NUM_NULLS,histogram,low_value,high_value from dba_tab_columns where table_name=upper('&table') and owner=upper('&owner') order by column_id; 

set linesize 200 pagesize 900
col column_name for a30
col index_name for a30
col density for 0.9999999999999
col index_type for a10
col tablespace_name for a30
select index_name, column_position, column_name, char_length, descend from dba_ind_columns where table_name =upper('&table') AND TABLE_OWNER=upper('&owner') order by index_name,column_position,column_name;