达梦运维常用sql

–查询数据量真实大小

SELECT
sum(df.TOTAL_SIZE)*SF_GET_PAGE_SIZE/1024/1024 as total_mb,
(sum(df.TOTAL_SIZE) - sum(df.FREE_SIZE))*SF_GET_PAGE_SIZE/1024/1024 as used_mb ,
sum(df.FREE_SIZE)*SF_GET_PAGE_SIZE/1024/1024 as free_mb
FROM "SYS".V$TABLESPACE AS ts, "SYS".V$DATAFILE AS df WHERE ts.ID = df.GROUP_ID;

–查询当前活动会话已执行的时间

select datediff(s,last_recv_time,sysdate) exectime,state,user_name,clnt_ip,clnt_ver,dbms_lob.SUBSTR(sf_get_session_sql(sess_id)) sql_txt
from v$sessions
where state='ACTIVE'
order by exectime desc;

–显示服务器启动以来执行时间最长语句

select * from V$SYSTEM_LONG_EXEC_SQLS order by 4 desc;

–最近1000条sql中最慢的

select * from V$LONG_EXEC_SQLS order by 4 desc;

–阻塞查询

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

–查询事务未提交的表

select b.object_name,c.sess_id,a.* from v$lock a,dba_objects b,v$sessions c
where a.table_id=b.object_id
and ltype='OBJECT'
and a.trx_id=c.trx_id;

–查看未被命中的sql次数

select COUNT(*) from V$CACHEITEM a ,v$cachesql b
  where a.address=b.cache_item and  a.n_hit=1;

–查看被缓存的SQL次数

select COUNT(*) from V$CACHEITEM a ,v$cachesql b
  where a.address=b.cache_item ;  

–查看命中次数超过一次的sql次数

select COUNT(*) from V$CACHEITEM a ,v$cachesql b
  where a.address=b.cache_item and  a.n_hit>1;

–查询数据库实例相关信息

select '实例名称' 数据库选项,INSTANCE_NAME  数据库集群相关参数值 FROM v$instance union all
select top 1 '数据库版本', banner ||'.'||id_code from v$version
union all
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end union all
SELECT '页大小',cast(PAGE()/1024 as varchar) union all
SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar) union all
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select '数据库模式',MODE$ from v$instance;