日常遇到的数据库上的常用排查命令

前言

记录一下日常遇到的数据库上的常用排查命令，善用 ctrl+F，日常更新中……

查询耗时

Oracle

SELECT
	a.sql_text SQL语句,
	b.etime 执行耗时,
	c.user_id 用户id,
	c.sample_time 执行时间,
	c.instance_number 实例数,
	u.username 用户名,
	a.sql_id SQL编号 
FROM
	dba_hist_sqltext a,
	( SELECT sql_id, elapsed_time_delta / 1000000 AS etime FROM dba_hist_sqlstat WHERE elapsed_time_delta / 1000000 >= 1 ) b,
	dba_hist_active_sess_history c,
	dba_users u 
WHERE
	a.sql_id = b.sql_id 
	AND u.username = 'WXYH' 
	AND c.user_id = u.user_id 
	AND b.sql_id = c.sql_id 
	AND c.sample_time >= TO_DATE( '2021-09-13 16:00:00', 'yyyy-mm-dd hh24:mi:ss' ) 
	AND c.sample_time <= TO_DATE( '2021-09-13 16:30:00', 'yyyy-mm-dd hh24:mi:ss' ) 
	--and a.sql_text like '%IN%'
	--ORDER BY    sample_time DESC,    b.etime DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SqlServer

SELECT
	top 20 total_worker_time / 1000 AS [总消耗 CPU 时间 ( ms ) ],
	execution_count [运行次数],
	qs.total_worker_time / qs.execution_count / 1000 AS [平均消耗 CPU 时间 ( ms ) ],
	last_execution_time AS [最后一次执行时间],
	max_worker_time / 1000 AS [最大执行时间 ( ms ) ],
	SUBSTRING (
		qt.text,
		qs.statement_start_offset / 2+1,
	( CASE WHEN qs.statement_end_offset = - 1 THEN DATALENGTH ( qt.text ) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 + 1 
	) AS [使用 CPU的语法 ],
	qt.text [完整语法],
	dbname = db_name ( qt.dbid ),
	object_name ( qt.objectid, qt.dbid ) ObjectName 
FROM
	sys.dm_exec_query_stats qs WITH ( nolock ) CROSS apply sys.dm_exec_sql_text ( qs.sql_handle ) AS qt 
WHERE
	execution_count > 1 
	AND total_worker_time / 1000 > 1000 
ORDER BY
	total_worker_time DESC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查询堵塞语句

Oracle

SELECT
	b.SID,
	A.sql_id,
	A.sql_text,
	A.hash_value,
	b.username,
	b.machine,
	A.module,
	DECODE( c.BLOCK, 1, 'blocking' ) blocking,
	DECODE( c.request, 0, 'null', 'blocked' ) blocked,
	TO_CHAR( b.logon_time, 'yyyy-mm-dd hh24:mi:ss' ) 
FROM
	v$sql A,
	v$session b,
	v$lock c 
WHERE
	c.TYPE = 'TX' 
	AND A.sql_id = b.sql_id 
	AND b.SID = c.SID UNION ALL
SELECT
	b.SID,
	A.sql_id,
	A.sql_text,
	A.hash_value,
	b.username,
	b.machine,
	A.module,
	DECODE( c.BLOCK, 1, 'blocking' ) blocking,
	DECODE( c.request, 0, 'null', 'blocked' ) blocked,
	TO_CHAR( b.logon_time, 'yyyy-mm-dd hh24:mi:ss' ) 
FROM
	v$sql A,
	v$session b,
	v$lock c 
WHERE
	c.TYPE = 'TX' 
	AND A.sql_id = b.prev_sql_id 
	AND b.SID = c.SID 
	AND c.BLOCK = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

SqlServer

WITH CTE_SID ( BSID, SID, sql_handle ) AS (
	SELECT
		blocking_session_id,
		session_id,
		sql_handle 
	FROM
		sys.dm_exec_requests 
	WHERE
		blocking_session_id <> 0 UNION ALL
	SELECT
		A.blocking_session_id,
		A.session_id,
		A.sql_handle 
	FROM
		sys.dm_exec_requests A
		JOIN CTE_SID B ON A.SESSION_ID = B.BSID 
	) SELECT
	C.BSID,
	C.SID,
	S.login_name,
	S.host_name,
	S.status,
	S.cpu_time,
	S.memory_usage,
	S.last_request_start_time,
	S.last_request_end_time,
	S.logical_reads,
	S.row_count,
	q.TEXT 
FROM
	CTE_SID C
	JOIN sys.dm_exec_sessions S ON C.sid = s.session_id CROSS APPLY sys.dm_exec_sql_text ( C.sql_handle ) Q 
ORDER BY
	sid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

死锁查询

Oracle

SELECT
	l.session_id SID,
	s.serial#,
	l.locked_mode 锁模式,
	l.oracle_username 登录用户,
	l.os_user_name 登录机器用户名,
	s.machine 机器名,
	s.terminal 终端用户名,
	o.object_name 被锁对象名,
	s.logon_time 登录数据库时间 
FROM
	v$locked_object l,
	all_objects o,
	v$session s 
WHERE
	l.object_id = o.object_id 
	AND l.session_id = s.SID 
ORDER BY
	SID,
	s.serial#;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SqlServer

SELECT
	request_session_id spid,
	OBJECT_NAME ( resource_associated_entity_id ) tableName 
FROM
	sys.dm_tran_locks 
WHERE
	resource_type = 'OBJECT'
1
2
3
4
5
6
7

死锁处理

Oracle

ALTER SYSTEM KILL SESSION 'sid,s.serial#';
1

SqlServer

kill spid
1

查看表空间情况

Oracle

SELECT
	A.tablespace_name "表空间名",
	total / 1024 / 1024 "表空间大小单位M",
	free / 1024 / 1024 "表空间剩余大小单位M",
	( total - free ) / 1024 / 1024 "表空间使用大小单位M",
	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;
1
2
3
4
5
6
7
8
9
10
11

查看表空间文件是否自动扩容

Oracle

SELECT
	tablespace_name,
	file_name,
	autoextensible 
FROM
	dba_data_files;
1
2
3
4
5
6

将指定的文件关闭自动扩容功能

Oracle

ALTER database datafile '/u01/app/oracle/oradata/****/sysaux01.dbf' autoextend off;
1

将指定的文件开启自动扩容功能

Oracle

ALTER database datafile '/u01/app/oracle/oradata/****/sysaux01.dbf' autoextend on;
1

对锁定用户解锁

Oracle

alter user xxxx identified by xxxx account unlock;
1

密码永不过期

Oracle

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
1

查询指定时间段内执行的SQL语句

Oracle

SELECT
	T.SQL_TEXT,
	T.FIRST_LOAD_TIME 
FROM
	v$sqlarea T 
WHERE
	TO_DATE( T.FIRST_LOAD_TIME, 'YYYY-MM-DD HH24:MI:SS' ) >= TO_DATE( '2019-02-20 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) 
	AND TO_DATE( T.FIRST_LOAD_TIME, 'YYYY-MM-DD HH24:MI:SS' ) <= TO_DATE( '2019-02-20 15:50:00', 'YYYY-MM-DD HH24:MI:SS' ) 
ORDER BY
	T.FIRST_LOAD_TIME DESC;
1
2
3
4
5
6
7
8
9
10

查询未提交的事务和语句

Oracle

SELECT
	s.SID,
	s.serial#,
	s.username,
	s.osuser,
	s.PROGRAM,
	s.event,
	TO_CHAR( s.LOGON_TIME, 'yyyymmdd-hh24:mi:ss' ),
	TO_CHAR( T.START_DATE, 'yyyymmdd-hh24:mi:ss' ),
	s.last_call_et AS last_ct,
	s.BLOCKING_SESSION block_sess,
	s.status,
	( SELECT q.SQL_TEXT FROM v$sql q WHERE q.LAST_ACTIVE_TIME = T.START_DATE AND ROWNUM <= 1 ) AS SQL_TEXT 
FROM
	v$session s,
	v$transaction T 
WHERE
	s.sADDR = T.SES_ADDR;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

修改主键类型

Oracle

--新增列
alter table blood_rec add id2 varchar2(32);
--备份值
update blood_rec set id2 = BLOOD_NO;
--删主键
alter table blood_rec drop primary key cascade drop index;
--允许空
alter table blood_rec modify BLOOD_NO null;
--删除约束条件，然后重新设置允许为空
--ALTER TABLE blood_rec DROP CONSTRAINT SYS_C0012330;
--更新空
update blood_rec set BLOOD_NO = null;
--改类型
alter table blood_rec modify BLOOD_NO varchar2(32) DEFAULT SUBSTR(SYS_GUID(),1,32);
--恢复值
update blood_rec set BLOOD_NO = id2;
--不允许空
alter table blood_rec modify BLOOD_NO not null;
--加主键
alter table blood_rec add constraint PK_blood_rec primary key (BLOOD_NO);
--删除列
alter table blood_rec drop column id2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

查询历史时间数据

Oracle

select * from table AS OF TIMESTAMP to_timestamp('20190704 14:00:00','yyyymmdd hh24:mi:ss');
1

开启行迁移

Oracle

alter table tablename enable row movement;
1

闪回指定时间片段的数据

Oracle

flashback TABLE tablename TO timestamp to_timestamp( '20190704 14:00:00', 'yyyymmdd hh24:mi:ss' );
1

关闭行迁移

Oracle

ALTER TABLE tablename disable ROW movement;
1

查看当前的数据库连接数

Oracle

SELECT
	count( * ) 
FROM
	v$process;
1
2
3
4

数据库允许的最大连接

Oracle

 select value from v$parameter where name ='processes';
1

修改数据库最大连接数

Oracle

 alter system set processes = 1000 scope = spfile;
 alter system set sessions=1200 scope=spfile;
1
2

---

创建md5加密函数

Oracle

CREATE 
	OR REPLACE FUNCTION MD5 ( passwd IN VARCHAR2 ) RETURN VARCHAR2 IS retval VARCHAR2 ( 32 );
BEGIN
		retval := ( CASE WHEN passwd IS NULL THEN NULL ELSE utl_raw.cast_to_raw ( DBMS_OBFUSCATION_TOOLKIT.MD5 ( INPUT_STRING => passwd ) ) END );
RETURN retval;
END;
1
2
3
4
5
6

闪回、回滚完整流程

Oracle

--1. 查询历史时间数据状态，用于判断时间片段上的数据是否正确
select * from tableName AS OF TIMESTAMP to_timestamp('20230321 14:00:00','yyyymmdd hh24:mi:ss');
--2. 开启行迁移
alter table tableName enable row movement;
--3. 闪回指定时间片段的数据
flashback table tableName to timestamp to_timestamp('20230321 14:00:00','yyyymmdd hh24:mi:ss');
--4. 关闭行迁移
alter table tableName disable row movement;
--5. 闪回完成
--如果过程需要提交语句(有的工具自动提交，有的则不会) ，注意commit。
--6. 验证闪回后的数据是否正常
select * from tableName;
1
2
3
4
5
6
7
8
9
10
11
12

按位与

Oracle

SELECT bitand(0, 0),bitand(0, 1),bitand(1, 0),bitand(1, 1),bitand(2, 2),bitand(2, 3),bitand(2, 4),bitand(3, 3),bitand(3, 4),bitand(3, 5) FROM dual;
1

MySQL

SELECT 0&0,0&1,1&0,1&1,2&2,2&3,2&4,3&3,3&4,3&5
1

列字段类型修改为clob

Oracle

示例表名：ANALYSIS_IMPROVE_BASE_INFO
原列名：CHECK_UNIT_ID
临时clob列名：CHECK_UNIT_ID_CLOB

--新增clob列
alter TABLE ANALYSIS_IMPROVE_BASE_INFO ADD(CHECK_UNIT_ID_CLOB CLOB);
--原列中的值更新到clob列中
update ANALYSIS_IMPROVE_BASE_INFO set CHECK_UNIT_ID_CLOB = CHECK_UNIT_ID;
COMMIT;
--验证clob数据
select CHECK_UNIT_ID,CHECK_UNIT_ID_CLOB from ANALYSIS_IMPROVE_BASE_INFO;
--删除原列
alter table ANALYSIS_IMPROVE_BASE_INFO drop column CHECK_UNIT_ID;
--修改clob列名
alter table ANALYSIS_IMPROVE_BASE_INFO rename column CHECK_UNIT_ID_CLOB to CHECK_UNIT_ID;
--再次验证
select CHECK_UNIT_ID from ANALYSIS_IMPROVE_BASE_INFO;
1
2
3
4
5
6
7
8
9
10
11
12
13