--表信息--
select owner,
table_name,
tablespace_name,
num_rows,/*表数据量*/
temporary,/*是否是临时表*/
degree,/*并行度*/
blocks,/*快的数量*/
empty_blocks,/*空块的数量*/
last_analyzed,/*统计信息更新时间*/
PARTITIONED/*是否分区*/
from dba_tables
where table_name = ‘DEPT‘
--表索引信息--
select UNIQUENESS,
TABLESPACE_NAME,
index_type,
BLEVEL,
LEAF_BLOCKS,
distinct_keys,
CLUSTERING_FACTOR,--等于表得块数good 等于表得行数bad
NUM_ROWS,
LAST_ANALYZED,
PARTITIONED,
b.COLUMN_NAME,
b.COLUMN_POSITION,
STATUS
from dba_indexes a, dba_ind_columns b
where a.owner = b.INDEX_OWNER
and a.index_name = b.INDEX_NAME
and a.table_name=‘DEPT‘
--表字段信息
select OWNER,
TABLE_NAME,
COLUMN_NAME,
NUM_DISTINCT,
NUM_NULLS,
LAST_ANALYZED,
HISTOGRAM
from dba_tab_col_statistics
where table_name = ‘DEPT‘
--表分区字段信息
select owner, name, column_name, column_position
from dba_part_key_columns
where name = ‘DEPT‘
--分区表分区类型
select owen, table_name, partition_type, subpartition_type
from dba_part_tables
--表分区统计详细
select table_owner,
table_name,
partition_name,
num_rows,
blocks,
empty_blocks,
LAST_ANALYZED
from dba_tab_partitions
where table_name = ‘DEPT‘;
--二级分区表统计详细
select table_owner,
table_name,
partition_name,
num_rows,
blocks,
empty_blocks,
LAST_ANALYZED
from dba_tab_subpartitions
where table_name = ‘DEPT‘;
--分区索引统计信息
select INDEX_OWNER,
INDEX_NAME,
PARTITION_NAME,
BLEVEL,
DISTINCT_KEYS,
NUM_ROWS,
LAST_ANALYZED,
STATUS
from dba_ind_partitions
where index_name = ‘xxx‘
--分区索引分区类型
select owner, index_name, partition_type, locality,
from dba_part_indexes
where index_name = ‘xxx‘