【与达梦同行】dm8元数据查询经常使用SQL

开头引读

DM8视Oracle为最大的竞争对手，它内部的函数和视图和Oracle是一模一样的，但是本人几天的琢磨过程中，发现Oracle与DM8的元数据结构组织还是有些不一样，有些Oracle有的而DM8没有，所以本人整理了一下DM8的元数据方面的SQL。

下面都是干货，经过本人测试的。一些SQL 是在运维工作中总结整理而成的，一些SQL 来源于各个网站或者博客网站，这些SQL对于运维 DM8的工程师甚至运维ORACLE工作会很方便。

DM8相关视图和系统表

动态性能视图可以记录数据库内存中正在执行的SQL信息、事物信息等，DM8常用性能视图如下。

视图分类
系统状态监控类
v$sysstat,v$dataﬁle,v$rlog,v$ckpt_history,v$database,v$instance,V$resource_limit,v$danger_event。

内存监控类
V$buﬀerpool,v$cacheitem,v$cachepln,v$dict_cache,v$mem_pool。

会话，锁，事物类
V$sessions,v$session_history,v$lock,v$deadlock_history,v$trx,v$trxwait。

等待事件类
V$event_name,v$session_wait_history,v$system_event,v$session_event。

SQL 历史类
V$sql_history,v$long_exec_sqls,v$system_long_exec_sqls,v$sort_history,v$pre_return_history,v$runtime_err_history,v$sqltext。

配置文件类
V$dm_ini,v$dm_arch_ini,v$parameter,v$dm_mal_ini。


系统表

SYSOBJECTS
可查询所有对象

SYSCOLUMNS
可查询表的所有列的信息

dba_tables
可查询表信息（包括系统表）

dba_users
可查询系统所有用户

dba_data_files
可查询数据库文件信息

dba_segments
可查询模式

all_tables
可查询所有用户的表

user_tables
可查询当前用户所拥有的所有表

user_tablespaces
可查询表空间

1、查询表的主外键关联

select a.CONSTRAINT_NAME,b.TABLE_NAME 引用表,b.COLUMN_NAME 引用列,c.TABLE_NAME 被引用表,c.COLUMN_NAME 被引用列 from
user_constraints a,
user_cons_columns b,
user_cons_columns c
where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
and a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and c.TABLE_NAME like 'T_PROD%';

2、每日归档量查询

SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS "Size(M)",
       TRUNC(completion_time)
  FROM v$archived_log
 GROUP BY TRUNC(completion_time);

3、查询碎片程度高的表和索引

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) "使用大小M",
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "实际大小M",
       round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
             (BLOCKS * 8192 / 1024 / 1024),
             3) * 100 || '%' "实际使用率%"
  FROM USER_TABLES
 where blocks > 100
   and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
       (BLOCKS * 8192 / 1024 / 1024) &lt; 0.3
 order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
          (BLOCKS * 8192 / 1024 / 1024) desc;

4、查看表空间可用百分比

select a.tablespace_name,b.total / 1024 / 1024 / 1024 total_gb,
a.free/1024/1024/1024 free_gb,
ROUND((total - free) / total, 4) * 100 "使用率%"
from (select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes) total
from dba_data_files
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

5、查询活动会话数

select count(*) from v$sessions where state='ACTIVE';

6、已执行超过2秒的活动SQL

select * from (
SELECT sess_id,sql_text,datediff(ss,last_send_time,sysdate) Y_EXETIME,
       SF_GET_SESSION_SQL(SESS_ID) fullsql,clnt_ip
 FROM V$SESSIONS WHERE STATE='ACTIVE')
 where Y_EXETIME>=2;

7、锁查询

select o.name,l.* from v$lock l,sysobjects o where l.table_id=o.id and blocked=1;

8、阻塞查询

with locks as(
  select o.name,l.*,s.sess_id,s.sql_text,s.clnt_ip,s.last_send_time  from v$lock l,sysobjects o,v$sessions s
  where l.table_id=o.id and l.trx_id=s.trx_id ),
 lock_tr as (   select trx_id wt_trxid,row_idx blk_trxid from locks where blocked=1),
 res as(    select sysdate stattime,t1.name,t1.sess_id wt_sessid,s.wt_trxid,
     t2.sess_id blk_sessid,s.blk_trxid,t2.clnt_ip,SF_GET_SESSION_SQL(t1.sess_id) fulsql,
     datediff(ss,t1.last_send_time,sysdate) ss,t1.sql_text wt_sql  from lock_tr s,locks t1,locks t2
    where t1.ltype='OBJECT'  and t1.table_id&lt;>0   and t2.ltype='OBJECT'  and t2.table_id&lt;>0
     and s.wt_trxid=t1.trx_id  and s.blk_trxid=t2.trx_id)
select distinct wt_sql,clnt_ip,ss,wt_trxid,blk_trxid  from res;

9、查看线程工作状态

select NAME,THREAD_DESC,count(1) from v$threads  group by  NAME,THREAD_DESC order by 3;  

dm_quit_thd ：用于执行正常关闭数据库的线程。
dm_io_thd ：IO线程，由IO_THR_GROUPS参数控制，默认为2个线程，如下我们可以从系统中获得证明。
dm_rsyswrk_thd ：异步归档线程，属于归档线程，主要负责将任务队列中的任务，按照归档类型进行相应的归档处理，一般有日志flushh线程触发。
dm_chkpnt_thd ：检查点线程，主要负责CKPT_LSN的管理。
dm_redolog_thd ：日志flush线程，负责日志刷盘，当事物提交或者发生检查点是触发。
dm_hio_thd ：IO线程，主要处理HFS相关的IO读取操作，比如HUGE表的IO读取就有该线程负责完成。
dm_sqllog_thd ：Thread for writing dmsql dmserver。
dm_purge_thd ：purge线程。主要负责回滚段清理。
dm_tskwrk_thd ：任务线程，由参数TASK_THREADS控制，取值范围为1-1000，默认为16，主要负责完成服务端SQL的解析运行等任务。
dm_trctsk_thd ：日志信息记录线程，主要负责数据库告警跟踪信息写入告警日志文件中。
dm_wrkgrp_thd ：工作线程，由参数WORKER_THREADS控制，取值范围为1-64，默认值为16，主要负责所有实际的数据相关操作。
dm_audit_thd ：审计线程，主要负责审计日志记录与更新。
dm_sched_thd ：调度线程，每秒钟轮询一次，主要负责接管数据库内部所有需要定时调度的任务，调度线程具备唤醒工作线程、向任务队列中添加任务队列、动态缓冲区检查、SQL缓存清理等权限。
dm_lsnr_thd ：监听线程。主要负责数据库服务器端口监听，处理客户端请求，并将连接请求加入到工作线程的任务队列，由工作线程完成任务处理。监听线程在数据库服务启动完成之后才启动，关闭数据库时首先被关闭。

10、查询单个表空间TBL_SPACE使用率

select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space 
where tablespace_name='TBL_SPACE' group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files 
where tablespace_name='TBL_SPACE' group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;

11、查看临时表空间数据文件位置，大小，及是否自动扩展

select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible 
from dba_data_files 
where tablespace_name in ('') order by tablespace_name;

12、查询用户下所有创建表的语句

select
'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from dual;' 
from user_tables;

13、查询普通用户语句

select username from dba_users where account_status='OPEN';

14、修改数据文件大小

alter database datafile '&path_name' resize 10G;

15、查看 SQL 的执行计划

select * from v$sql_plan ;


select * from v$sql_plan where sql_id='&sql_id';

select cache_item from v$cachepln where sqlstr = ‘%待优化sql%’


select a.cache_item,a.sqlstr ,b.*
from v$cachepln a,dba_objects b 
where a.sqlstr like '%CTISYS%'
and a.tableid like '%'||b.object_id||'%'
and b.object_name='DM_GOODS'   --表名
and b.object_type='TABLE';     -- 类型

16、查看表的统计信息



select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,ROW_MOVEMENT,PARTITIONED,GLOBAL_STATS,
USER_STATS,to_char(LAST_ANALYZED,'YYYY-MM-DD') 
from dba_tables;


select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,ROW_MOVEMENT,PARTITIONED,GLOBAL_STATS,
USER_STATS,to_char(LAST_ANALYZED,'YYYY-MM-DD') 
from dba_tables where  table_name='&1';

17、查询慢SQL

SELECT
        SESS_ID    ,
        EXEC_TIME  ,
        FINISH_TIME,
        N_RUNS     ,
        TRX_ID     ,
        substr(sql_text,0,20)
FROM
        V$SYSTEM_LONG_EXEC_SQLS
ORDER BY
        EXEC_TIME DESC,
        N_RUNS DESC;

18、查询HENLEY2用户下LINEITEM对象的DDL语句

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 0
set long 50000

SELECT
        ''||DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) AS DDL
FROM
        DBA_OBJECTS
WHERE
upper(OWNER)=upper('HENLEY2')
and upper(OBJECT_NAME)=upper('LINEITEM');

19、查看数据库对象

select t2.name owner, t1.subtype$ object_type,
       t1.valid status, count(1) count# 
  from sysobjects t1, sysobjects t2
 where t1.schid = t2.id and t1.schid != 0
 group by t2.name, t1.subtype$, t1.valid;

20、查看控制文件

select para_value name from v$dm_ini where para_name='CTL_PATH';

21、查看当前用户所有的表

SQL> select table_name from user_tables;

LINEID     TABLE_NAME      
---------- ----------------
1          BMSQL_CONFIG
2          BMSQL_WAREHOUSE
3          BMSQL_DISTRICT
4          BMSQL_CUSTOMER
5          BMSQL_HISTORY
6          BMSQL_NEW_ORDER
7          BMSQL_OORDER
8          BMSQL_ORDER_LINE
9          BMSQL_ITEM
10         BMSQL_STOCK
11         CUSTOMER

LINEID     TABLE_NAME
---------- ----------
12         ORDERS
13         PART
14         PARTSUPP
15         REGION
16         SUPPLIER
17         NATION
18         LINEITEM

18 rows got

22、查看指定的表结构

SQL> desc  LINEITEM

LINEID     NAME            TYPE$   NULLABLE
---------- --------------- ------- --------
1          L_ORDERKEY      INTEGER N
2          L_PARTKEY       INTEGER N
3          L_SUPPKEY       INTEGER N
4          L_LINENUMBER    INTEGER N
5          L_QUANTITY      DOUBLE  N
6          L_EXTENDEDPRICE DOUBLE  N
7          L_DISCOUNT      DOUBLE  N
8          L_TAX           DOUBLE  N
9          L_RETURNFLAG    CHAR(1) N
10         L_LINESTATUS    CHAR(1) N
11         L_SHIPDATE      DATE    N

LINEID     NAME           TYPE$       NULLABLE
---------- -------------- ----------- --------
12         L_COMMITDATE   DATE        N
13         L_RECEIPTDATE  DATE        N
14         L_SHIPINSTRUCT CHAR(25)    N
15         L_SHIPMODE     CHAR(10)    N
16         L_COMMENT      VARCHAR(44) N

16 rows got

used time: 12.324(ms). Execute id is 2136202.

23、查看日志文件路径信息

select path,rlog_size from v$rlogfile;

24、查看达梦数据库的配置参数信息

select para_name,para_value from v$dm_ini

25、查看角色类型

select * from dba_roles;

26、创建表空间设定自动扩展，创建用户默认表空间，给用户赋值DBA权限

create tablespace TBS_TEST datafile ‘TBS_TEST01.dbf‘ size 128 autoextend on;

create user USER_TEST identified by "123456789" default tablespace TBS_TEST;

grant dba to USER_TEST;

27、查看执行SQL的执行计划

SQL> explain  select *  from  lineitem limit 1;

1   #NSET2: [158, 1, 327] 
2     #PRJT2: [158, 1, 327]; exp_num(16), is_atom(FALSE) 
3       #TOPN2: [158, 1, 327]; top_num(1)
4         #HFSCN2: [158, 921585, 327]; (LINEITEM)

used time: 1.350(ms). Execute id is 0.