关于数据质量核查相关的脚本续

# 查看正在执行的SQL，通过报表迅速定位SQL语句
select a.username, a.sid,a.machine,b.sql_text, b.sql_fulltext,a.sql_exec_start,a.logon_time
  from v$session a, v$sqlarea b
 where a.sql_address = b.address;

#表大小分析
select table_name,num_rows,a.blocks
 from all_tables a;

# 表热度分析
select * from v$segment_statistics
 where statistic_name='logical reads' 
   and object_name='TT'

# 表主外键依赖->知识图谱
select a.owner, --主键拥有者
       a.table_name, --主键表
       b.column_name, --主键列
       c.owner ref_owner, --外键拥有者
       c.table_name ref_table_name, --外键表
       d.column_name ref_column_name--外键列
  from user_constraints  a
  left join user_cons_columns b on  a.constraint_name=b.constraint_name
  left join user_constraints c on a.constraint_name=c.r_constraint_name
  left join user_cons_columns d on c.constraint_name=d.constraint_name
 where  a.constraint_type='P'
    and c.table_name is not null
 order by a.table_name

# 表依赖的存储过程->知识图谱
select a.owner,a.name,a.type
  from dba_dependencies a
 where a.referenced_name='RECORD10W'
   and a.referenced_type in ('TABLE','VIEW')
   and a.type='PROCEDURE'

# 数据字典
select a.table_name,a.column_name,
         case when a.data_type in ('CHAR','VARCHAR2','NVARCHAR2')  then a.data_type||'('||a.data_length||')'
                 when a.data_type in ('BLOB','CLOB','DATE','TIMESTAMP(6)','FLOAT','LONG') then a.data_type
                 when a.data_type in ('NUMBER') and a.data_length=22 and a.data_precision is null and a.data_scale=0 THEN 'INTEGER'
                 when a.data_type in ('NUMBER') and a.data_length=22 and a.data_precision is null and a.data_scale is null THEN 'NUMBER'
                 when a.data_type in ('NUMBER') and a.data_length=22 and a.data_precision is not null and a.data_scale is not null THEN 'NUMBER'||'('||a.data_precision||','||a.data_scale||')'
         end datatype,
         a.nullable,
         case when d.position is not null  then 'P' end primarykey, 
         b.comments
  from user_tab_columns a
  left outer join user_col_comments b on a.TABLE_NAME=b.table_name and a.column_name=b.column_name
  left outer join user_constraints c on a.TABLE_NAME=c.table_name and c.constraint_type='P'
  left join user_cons_columns d on c.constraint_name=d.constraint_name and a.TABLE_NAME=d.table_name and a.column_name=d.column_name
 where a.table_name='TESTTYPE'
 order by a.table_name,a.column_id

#执行最多的SQL
select s.sql_text,
        s.executions "执行次数",
        u.username "用户名"
  from v$sqlarea s
  left join all_users u
    on u.user_id = s.parsing_user_id
 where u.username in ('METADATA')
    and s.parsing_schema_name not iN ('SYS','SYSTEM','SYSMAN')