文章目录
前言
记录一下日常遇到的数据库上的常用排查命令,善用 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;
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
查询堵塞语句
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
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
死锁查询
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#;
SqlServer
SELECT
request_session_id spid,
OBJECT_NAME ( resource_associated_entity_id ) tableName
FROM
sys.dm_tran_locks
WHERE
resource_type = 'OBJECT'
死锁处理
Oracle
ALTER SYSTEM KILL SESSION 'sid,s.serial#';
SqlServer
kill spid
查看表空间情况
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;
查看表空间文件是否自动扩容
Oracle
SELECT
tablespace_name,
file_name,
autoextensible
FROM
dba_data_files;
将指定的文件关闭自动扩容功能
Oracle
ALTER database datafile '/u01/app/oracle/oradata/****/sysaux01.dbf' autoextend off;
将指定的文件开启自动扩容功能
Oracle
ALTER database datafile '/u01/app/oracle/oradata/****/sysaux01.dbf' autoextend on;
对锁定用户解锁
Oracle
alter user xxxx identified by xxxx account unlock;
密码永不过期
Oracle
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
查询指定时间段内执行的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;
查询未提交的事务和语句
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;
修改主键类型
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;
查询历史时间数据
Oracle
select * from table AS OF TIMESTAMP to_timestamp('20190704 14:00:00','yyyymmdd hh24:mi:ss');
开启行迁移
Oracle
alter table tablename enable row movement;
闪回指定时间片段的数据
Oracle
flashback TABLE tablename TO timestamp to_timestamp( '20190704 14:00:00', 'yyyymmdd hh24:mi:ss' );
关闭行迁移
Oracle
ALTER TABLE tablename disable ROW movement;
查看当前的数据库连接数
Oracle
SELECT
count( * )
FROM
v$process;
数据库允许的最大连接
Oracle
select value from v$parameter where name ='processes';
修改数据库最大连接数
Oracle
alter system set processes = 1000 scope = spfile;
alter system set sessions=1200 scope=spfile;
创建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;
闪回、回滚完整流程
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;
按位与
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;
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) |
---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 1 | 2 | 2 | 0 | 3 | 0 | 1 |
MySQL
SELECT 0&0,0&1,1&0,1&1,2&2,2&3,2&4,3&3,3&4,3&5
0&0 | 0&1 | 1&0 | 1&1 | 2&2 | 2&3 | 2&4 | 3&3 | 3&4 | 3&5 |
---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 1 | 2 | 2 | 0 | 3 | 0 | 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;