DM8日常运维命令总结（四）

查询数据库表空间数据文件使用大小限制

SQL> DECLARE
K INT:=(SELECT cast(PAGE()/1024 as varchar));

BEGIN
SELECT
        F."PATH" 数据文件                ,
        F.CLIENT_PATH,
        G.NAME 所属表空间,
        F.MAX_SIZE||'M' 文件扩展限制,
        (CASE F.AUTO_EXTEND WHEN '1' THEN '是'  ELSE '否' END) 文件自动扩容,
        (CASE F.NEXT_SIZE WHEN '0' THEN '默认值' ELSE F.NEXT_SIZE||'M' END) 文件下次扩容值,
        F.TOTAL_SIZE*K/1024 ||'M' 文件当前大小 ,
        ROUND((F.FREE_SIZE * K/1024 /(F.TOTAL_SIZE * K/1024.0)),2)*100 ||'%' AS 空闲率,
        (1-ROUND((F.FREE_SIZE * K/1024 /(F.TOTAL_SIZE * K/1024.0)),2))*100 ||'%' AS 使用率   
FROM    SYS."V$DATAFILE" F LEFT JOIN SYS.V$TABLESPACE G ON F.GROUP_ID=G.ID ORDER BY 1;
END;/

查询系统所有的账号

SQL> select username,account_status,created,default_tablespace,default_index_tablespace,temporary_tablespace from dba_users;

查询所有的数据库对象

SELECT
 A.USERNAME "用户名",
 (SELECT COUNT(1) FROM DBA_TABLES B WHERE B.OWNER = A.USERNAME) "表数量",
 ( SELECT COUNT(1) FROM DBA_VIEWS G WHERE G.OWNER = A.USERNAME ) "视图数量",
 ( SELECT COUNT(1) FROM DBA_TRIGGERS H WHERE H.OWNER = A.USERNAME ) "触发器数量",
 ( SELECT COUNT(DISTINCT I.NAME) FROM DBA_SOURCE I WHERE I.OWNER = A.USERNAME AND I.TYPE = 'FUNCTION' ) "函数数量",
 ( SELECT COUNT(1) FROM DBA_SEQUENCES J WHERE J.SEQUENCE_OWNER = A.USERNAME ) "序列数量",
 ( SELECT COUNT(DISTINCT L.NAME) FROM DBA_SOURCE L WHERE L.OWNER = A.USERNAME AND L.TYPE = 'PROCEDURE' ) "存储过程数量",
 ( SELECT COUNT(1) FROM DBA_DB_LINKS M WHERE M.OWNER = A.USERNAME ) "DBLINK数量",
 ( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'SYS_%' AND OWNER =A.USERNAME) "索引数量",
 ( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='TYPE' AND OWNER =A.USERNAME ) "自定义类型",
 ( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER =A.USERNAME) "PKG数量"
FROM
 DBA_USERS A WHERE A.USERNAME IN ('MMIS_INNOVATION','MMIS_HD','SENYI','WANGHAI','GK_INNOVATION','SCM_INNOVATION');

查看数据库集群相关参数值

select '实例名称' 数据库选项,INSTANCE_NAME 数据库集群相关参数值 FROM v$instance union all
select '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v$instance 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 union all
select '唯一魔数',cast(permanent_magic as varchar) union all
select 'LSN',cast(cur_lsn as varchar) from v$rlog;

查看达梦错误码汇总

select * from v$err_info;

查看数据库的到期时间

select　*　from　v$license;

查询 SQL 执行记录

SELECT * FROM V$SQL_HISTORY;

查看建库时的字符集

select unicode();
--根据返回值进行判断
--0 表示 GB18030，1 表示 UTF-8，2 表示 EUC-KR

如何获取达梦当前模式名

SQL> SELECT SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID());

查看实例是否是空格自动填充

select  * from v$dm_ini where para_name = 'BLANK_PAD_MODE';

查看当前的密码策略

select PWD_POLICY,SF_GET_USERNAME_BY_ID(ID),* from  SYSUSERS;

查看数据库是否开启加密通信

SELECT * FROM V$DM_INI WHERE PARA_NAME= 'COMM_ENCRYPT_NAME';
#如未开启，可以用以下命令开启SP_SET_PARA_STRING_VALUE(2,'COMM_ENCRYPT_NAME','DES_OFB');

查看是否是免密登录

select * from v$dm_ini where para_name='ENABLE_LOCAL_OSAUTH';
如果未开启，可以用SP_SET_PARA_VALUE(2,'ENABLE_LOCAL_OSAUTH',1);

查看备份路径

select para_name,para_value from v$dm_ini where para_name='BAK_PATH';

查找出活动会话中执行时间大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 
from v$sessions s  where state='ACTIVE')
where t > 1

查看对象或资源发生等待问题

SELECT * FROM v$sessions WHERE state='ACTIVE'
AND dbms_lob.substr(sf_get_session_sql(sess_id)) LIKE '%语句片段%'；

查看执行效率低的SQL

SELECT
        *
FROM
        (
                SELECT
                        sess_id                                         ,
                        sql_text                                        ,
                        datediff (ss, last_recv_time, SYSDATE) Y_EXETIME,
                        SF_GET_SESSION_SQL (SESS_ID) fullsql            ,
                        clnt_ip
                FROM
                        V$SESSIONS
                WHERE
                        STATE = 'ACTIVE'
        )
WHERE
        Y_EXETIME >= 2; 

查看是否有异常SQL

Select top 60 datediff(ss,last_send_time,sysdate) TIME,sess_id,run_status,sql_text,AUTO_CMT,user_name,appname,CLNT_IP,trx_id  from  v$sessions  order by TIME desc;

查看句柄数是否满了

select
        sql_text               ,
        state                  ,
        n_stmt "句柄的容量"         ,
        n_used_stmt as "使用的句柄数",
        curr_sch               ,
        user_name              ,
        trx_id                 ,
        create_time            ,
        clnt_type              ,
        clnt_ip                ,
        run_status
from
        v$sessions;

查看是否用户设置了最大空闲时间参数

select b.username 账号,b.password_versions 密码策略,a.sess_per_user 同时拥有的会话数,a.conn_idle_time 会话访问服务器的时间上限 from sysusers a,dba_users b where a.id=b.user_id;

查询当前数据库实例是否有阻塞

select * from V$TRXWAIT ;
SELECT SESS_ID,SQL_TEXT,TRX_ID from V$SESSIONS;
sp_close_session(sess_id)；
#查看死锁历史select * from V$DEADLOCK_HISTORY;
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, TID 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 <> 0
                AND T2.LTYPE = 'OBJECT'
                AND T2.TABLE_ID <> 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;

查看死锁

SELECT O.NAME,L.* FROM V$LOCK L,SYSOBJECTS O WHERE L.TABLE_ID=O.ID AND BLOCKED=1;