签到成功

知道了

CNDBA社区CNDBA社区

查看表索引、字段以及表分区相关统计信息脚本

2018-05-25 12:01 2412 2 原创 索引及约束
作者： Marvinn
原文链接：http://www.cndba.cn/Marvinn
本文链接：http://www.cndba.cn/Marvinn/article/2811

最近，在做SQL优化项目，直到昨天项目基本完成，哈哈，菜鸟识优化，也是胆颤心惊的，创建索引Online 并行也会造成阻塞,但是不开并行ONline,还是可以的…过程用到最多的查表相关信息，虽然说有监控系统，但是有时候创建的收集不及时，没有相关信息，然后，最近折腾了下相关SQL脚本一键查询…留备后续使用….http://www.cndba.cn/Marvinn/article/2811http://www.cndba.cn/Marvinn/article/2811

http://www.cndba.cn/Marvinn/article/2811
http://www.cndba.cn/Marvinn/article/2811
http://www.cndba.cn/Marvinn/article/2811
http://www.cndba.cn/Marvinn/article/2811
http://www.cndba.cn/Marvinn/article/2811http://www.cndba.cn/Marvinn/article/2811http://www.cndba.cn/Marvinn/article/2811http://www.cndba.cn/Marvinn/article/2811

set echo off
set scan on
set lines 200
set long 999999
set pages 66
set newpage 0
set verify off
set feedback off
set termout off
set timing off
SET wrap on

column uservar new_value Table_Owner noprint
select user uservar from dual;
set termout on
column TABLE_NAME heading 'Tables owned by &Table_Owner' format a150
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
accept table_name  prompt 'Please enter Table Name to show Statistics for: '


column owner heading 'Owner' for a10
column table_name  heading 'Table Name' for a12
column num_rows heading 'Num Rows' for 999,999,999,999
column blocks heading 'Num Blocks' for 999,999,999,999
column avg_row_len heading 'Avg Row len' for 999,999,999,999
column TEMPORARY heading 'Is Temporary' for a15
column GLOBAL_STATS heading 'Is|Global Static' for a15
column SAMPLE_SIZE heading 'Sample Size' for 999,999,999,999
column degree heading 'Degree' for 999
column LAST_ANALYZED heading 'Last Analyzed'  for a20

column column_name heading 'Column Name' for a15
column data_type heading 'Data Type' for a10
column nullable heading 'Is Null' for a15
column num_distinct heading 'Distinct Value' for 999,999,999,999
column density heading 'Density' for 999,999,999,999,999
column low_value heading 'Col Low Value' for a20
column col_high_value heading 'Col High Value' for a20
column num_nulls heading 'Num Null' for 999,999,999,999
column histogram heading 'Histogram' for a15
column num_buckets heading 'Num Buckets' for 999,999

column constraint_name heading  'Cons Name' for a20
column constraint_type heading 'Cons Type' for a15
column position heading 'Cons Position' for a15
column R_constraint_name heading 'R_Cons Name' for a20
column status heading 'Status' for a10
column invalid heading 'Is Invalid'  for a15
column cons_col_name heading 'Cons Col Name' for a15

column index_name heading  'Index Name' for a15
column index_type heading  'Index Type' for a15
column index_col_name heading 'Index Col_Name' for a15
column uniqueness heading  'Unique' for a20
column partitioned heading  'Is_Partition|Index' for a15
column blevel heading  'Index Blevel' for 999,999
column leaf_blocks heading  'Index|Leaf Blocks' for 999,999,999,999,999
column distinct_keys heading  'Index|Distinct Value' for 999,999,999,999,999
column clustering_factor heading  'Cluster Factor' for 999,999,999,999,999
column index_col_name heading  'Index|Column Name' for a20
column column_position heading  'Index|Column Position' for 999,999
column descend heading  'IS_Desc' for a8

column partition_position heading  'Partition Position' for 999,999
column partition_name heading  'Partition Name' for a15
column COMPOSITE heading  'Is_Multi|Partition' for a15
column SUBPARTITION_COUNT heading  'Sub|Partiton Counts' for 999,999,999,999
column COMPRESSION heading  'Is Compressed' for a10
column HIGH_VALUE heading 'Partition|High Value' for a85

column SUBPARTITION_POSITION heading 'Sub|Partiton Position' for 999,999
column SUBPARTITION_NAME heading 'Sub|Partiton Name' for a15


prompt
prompt ************************************
prompt Table INFO 查看表行数，块数以及行长
prompt ************************************
prompt 

select owner ,
       table_name ,
       num_rows ,
       blocks ,
       avg_row_len ,
       TEMPORARY ,
       GLOBAL_STATS ,
       SAMPLE_SIZE ,
       degree ,
       to_char(LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  from dba_tables
 where owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/


prompt
prompt *********************************
prompt Table Field INFO 查看表列信息
prompt *********************************
prompt 

select s.column_name ,
       s.data_type ,
       s.nullable ,
       s.num_distinct ,
       round(s.density,6) density,
       s.num_nulls ,
       s.histogram ,
       s.num_buckets ,
       to_char(s.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  from dba_tab_columns s
 where owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/

select s.column_name ,
       s.low_value ,
       s.high_value col_high_value,
       to_char(s.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  from dba_tab_columns s
 where owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/


prompt
prompt **************************************
prompt Table Constraints INFO 查看表约束信息
prompt **************************************
prompt

SELECT a.column_name cons_col_name,
       a.constraint_name ,
       b.constraint_type ,
       a.position ,
       b.R_constraint_name ,
       b.status ,
       b.invalid
  FROM user_cons_columns a, user_constraints b
 where a.owner = upper(nvl('&Owner',user)) and a.table_name = upper('&Table_name')
   AND a.constraint_name = b.constraint_name;
/


prompt
prompt ************************************
prompt Table Index INFO 查看表索引相关信息
prompt ************************************
prompt

select index_name,
       index_type,
       uniqueness,
       status ,
       partitioned ,
       TEMPORARY ,
       degree ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  from dba_indexes
 where owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/

select index_name,
       index_type,
       blevel ,
       leaf_blocks ,
       distinct_keys ,
       num_rows ,
       clustering_factor ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED
  from dba_indexes
 where owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/


prompt
prompt ********************************************
prompt Table Index Field INFO 查看表索引列相关信息
prompt ********************************************
prompt

select index_name , column_name index_col_name, column_position ,descend 
  from dba_ind_columns
 where table_owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/


prompt
prompt *************************************
prompt Table Partition INFO 查看表分区情况
prompt *************************************
prompt

select partition_position,
       partition_name ,
       COMPOSITE ,
       num_rows ,
       AVG_ROW_LEN ,
       blocks ,
       SUBPARTITION_COUNT ,
       COMPRESSION ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')  LAST_ANALYZED
  from dba_tab_partitions
 where table_owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/   


select partition_position ,
       partition_name ,
       HIGH_VALUE ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
  from dba_tab_partitions
 where table_owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/ 


prompt
prompt ******************************************
prompt Table Subpartitions INFO 查看表子分区信息
prompt ******************************************
prompt 

select partition_name ,
       SUBPARTITION_POSITION ,
       SUBPARTITION_NAME ,
       num_rows ,
       AVG_ROW_LEN ,
       blocks ,
       COMPRESSION ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
  from dba_tab_subpartitions
 where table_owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/

select partition_name ,
       SUBPARTITION_POSITION ,
       SUBPARTITION_NAME ,
       HIGH_VALUE ,
       to_char(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZED 
  from dba_tab_subpartitions
 where table_owner = upper(nvl('&Owner',user)) and table_name = upper('&Table_name')
/

set echo on

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Marvinn

Marvinn

关注

路漫漫其修远兮、吾将上下而求索

  • 99
    原创
  • 0
    翻译
  • 2
    转载
  • 36
    评论
  • 访问：458431次
  • 积分：449
  • 等级：中级会员
  • 排名：第12名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ