达梦数据库日常巡检脚本
1.查看数据库信息
|
select * from ( SELECT a.*, page/1024 ||'K' page_size, SF_GET_EXTENT_SIZE() ||'K' EXTENT_SIZE , decode(SF_GET_CASE_SENSITIVE_FLAG(), 1, '是', 0, '否') CASE_SENSITIVE, decode(SF_GET_UNICODE_FLAG(), 1, 'UTF8', 0, 'GB18030') UNICODE_FLAG, decode(SF_GET_LENGTH_IN_CHAR(), 1, '是', 0, '否') LENGTH_IN_CHAR FROM V$DATABASE a ); |
2、归档配置信息
select
*
from
(
SELECT
A.*,
(
SELECT SUM(LEN)/(1024*1024)*100 FROM V$ARCH_FILE
)
/arch_space_limit
||'%' used_per
FROM
V$DM_ARCH_INI A
);
3、查看授权信息
select EXPIRED_DATE from V$LICENSE;
点击查询，查看返回结果 NULL是正常的版本。如果有时间，即为试用版
4、查看实例信息
SELECT * FROM V$INSTANCE;
5、查看表空间信息
select * from (
select c.*,d.used_per||'%' used_per from V$TABLESPACE c join(
SELECT a.id,100-(sum(b.free_size)*100/sum(b.total_size)) used_per FROM V$TABLESPACE a,V$DATAFILE b
where a.id=b.GROUP_ID
group by a.id) d
on c.id=d.id
order by c.id);
6、查看数据文件信息
select * from V$DATAFILE;
7、查看重做日志文件信息
select * from (
SELECT
A.FILE_ID ,
A.PATH ,
A.CLIENT_PATH,
A.RLOG_SIZE ,
B.FREE_SPACE ,
B.TOTAL_SPACE,
B.CUR_FILE
from
(
select a1.* from V$RLOGFILE a1 where true
)
A,
(
select a2.* from V$RLOG a2 where true
)
B);
8、查看用户信息
select * from DBA_USERS;
9.用户授权信息
select
*
from
(
SELECT
a.*
FROM
(
SELECT
GRANTEE ,
GRANTED_ROLE PRIVILEGE ,
'ROLE_PRIVS' PRIVILEGE_TYPE,
CASE ADMIN_OPTION WHEN 'Y' THEN 'YES' ELSE 'NO' END ADMIN_OPTION
FROM
DBA_ROLE_PRIVS
UNION
SELECT
GRANTEE ,
PRIVILEGE ,
'SYS_PRIVS' PRIVILEGE_TYPE,
ADMIN_OPTION
FROM
DBA_SYS_PRIVS
UNION
SELECT
GRANTEE,
PRIVILEGE
||' ON '
||OWNER
||'.'
||TABLE_NAME PRIVILEGE ,
'TABLE_PRIVS' PRIVILEGE_TYPE,
GRANTABLE
FROM
DBA_TAB_PRIVS
)
a
WHERE
GRANTEE IN
(
SELECT
USERNAME
FROM
ALL_USERS
WHERE
USERNAME NOT IN ('SYS', 'SYSDBA', 'SYSSSO', 'SYSAUDITOR')
)
ORDER BY
GRANTEE ,
PRIVILEGE_TYPE,
PRIVILEGE
);
10、用户资源限制信息
select * from (
select b.name,
a.SESS_PER_USER,
a.CONN_IDLE_TIME,
a.FAILED_NUM,
a.LIFE_TIME,
a.REUSE_TIME,
a.REUSE_MAX,
a.LOCK_TIME,
a.GRACE_TIME,
a.LOCKED_STATUS,
a.LASTEST_LOCKED,
a.PWD_POLICY,
a.RN_FLAG,
a.ALLOW_ADDR,
a.NOT_ALLOW_ADDR,
a.ALLOW_DT,
a.NOT_ALLOW_DT,
a.LAST_LOGIN_DTID,
a.LAST_LOGIN_IP,
a.FAILED_ATTEMPS
from SYSUSERS a,SYS.SYSOBJECTS b where a.id=b.id);
11、作业信息
select
*
from
(
SELECT
A.ID ,
A.NAME ,
A."ENABLE" ,
A.USERNAME ,
A.CREATETIME,
A.MODIFYTIME,
A.DESCRIBE ,
B.LAST_DATE
||' '
||B.LAST_SEC LAST_TIME,
B.NEXT_DATE
||' '
||B.NEXT_SEC NEXT_TIME,
B.WHAT
FROM
SYSJOB.SYSJOBS A,
SYSJOB.USER_JOBS B
WHERE
A.ID=B.JOB
);
12、作业的运行历史信息
SELECT * FROM (
SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO
FROM (SELECT EXEC_ID, NAME,STEPNAME,START_TIME,END_TIME,ERRTYPE,ERRCODE,ERRINFO,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY EXEC_ID DESC) RN
FROM SYSJOB.SYSSTEPHISTORIES2) WHERE RN<=10);
13、死锁信息查询和清除
查询死锁
select s.sess_id,s.SQL_TEXT,s.RUN_STATUS from v$sessions s , v$lock l where l.tid=s.trx_Id and l.blocked=1;
执行查到的脚本，可以清除死锁
select 'SP_CLOSE_SESSION(' ||s.sess_id||')' from v$sessions s,v$lock l where s.trx_id=l.tid and l.blocked=1;
14、堵塞查询
select * from (
WITH TRX_TAB AS
(SELECT O1.NAME,L1.TRX_ID FROM V$LOCK L1,SYSOBJECTS O1 WHERE L1.TABLE_ID=O1.ID AND O1.ID<>0),
TRX_SESS AS (
SELECT L.TRX_ID WT_TRXID, L.ROW_IDX BLK_TRXID,L.BLOCKED,(SELECT NAME TABLE_NAME FROM TRX_TAB A WHERE A.TRX_ID=L.TRX_ID) WT_TABLE,
S1.SESS_ID WT_SESS,S2.SESS_ID BLK_SESS,
S1.USER_NAME WT_USER_NAME,S2.USER_NAME BLK_USER_NAME,S1.SQL_TEXT,S1.CLNT_IP,DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS
FROM V$LOCK L,V$SESSIONS S1,V$SESSIONS S2
WHERE L.TRX_ID=S1.TRX_ID AND L.ROW_IDX=S2.TRX_ID)
SELECT SYSDATE STATTIME,* FROM TRX_SESS where BLOCKED=1);
15、内存池查询
select * from V$MEM_POOL;
16、数据缓冲池查询
select * from V$BUFFERPOOL;
17、数据字典缓存查询
select * from V$DB_CACHE;
18、统计信息查询
select * from V$SYSSTAT where classid in (11,5) order by classid desc;
19、会话信息查询
select * from (
SELECT STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME,COUNT(*) COUNTS FROM V$SESSIONS GROUP BY STATE,CLNT_IP,CLNT_TYPE,CURR_SCH,USER_NAME ORDER BY STATE);
20、最慢20条sql查询
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
21、最消耗内存的20条sql查询
select * from V$SYSTEM_LARGE_MEM_SQLS order by mem_used_by_k desc;
22. 最消耗IO的sql查询
select sess_id,top_sql_text,time_used,N_LOGIC_READ,N_PHY_READ from V$SQL_HISTORY order by N_PHY_READ desc;
23. ASM磁盘组信息
24. ASM磁盘信息
25. ASM文件信息
26. DSC节点信息查询
27. DCR配置信息查询
select * from V$DCR_INFO;
28. 主备延迟查询（在备机上查询）
select DATEDIFF(MS,LAST_CMT_TIME,APPLY_CMT_TIME) from V$RAPPLY_STAT;
29、数据库实例参数查询
select DECODE(para_value,1,'是','否') "LENGTH_IN_CHAR",
page "PAGE_SIZE",
SF_GET_EXTENT_SIZE() "EXTENT_SIZE",
DECODE(unicode,'1','utf8',0,'gbk','EUC-KR') "CHARSET",
decode(CASE_SENSITIVE,'1','敏感','不敏感') CASE_SENSITIVE
from v$dm_ini
where para_name = 'LENGTH_IN_CHAR';
30、查看用户锁定时间和解锁用户
查看锁定时间
select username,lock_date from dba_users where username='TEST';
解锁用户
alter user TEST account unlock;
