文章目录

• 前言
• 查询耗时
• • Oracle
  • SqlServer
• 查询堵塞语句
• • Oracle
  • SqlServer
• 死锁查询
• • Oracle
  • SqlServer
• 死锁处理
• • Oracle
  • SqlServer
• 查看表空间情况
• • Oracle
• 查看表空间文件是否自动扩容
• • Oracle
• 将指定的文件关闭自动扩容功能
• • Oracle
• 将指定的文件开启自动扩容功能
• • Oracle
• 对锁定用户解锁
• • Oracle
• 密码永不过期
• • Oracle
• 查询指定时间段内执行的SQL语句
• • Oracle
• 查询未提交的事务和语句
• • Oracle
• 修改主键类型
• • Oracle
• 查询历史时间数据
• • Oracle
• 开启行迁移
• • Oracle
• 闪回指定时间片段的数据
• • Oracle
• 关闭行迁移
• • Oracle
• 查看当前的数据库连接数
• • Oracle
• 数据库允许的最大连接
• • Oracle
• 修改数据库最大连接数
• • Oracle
• 创建md5加密函数
• • Oracle

前言

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

查询耗时

Oracle

SELECTa.sql_text SQL语句,b.etime 执行耗时,c.user_id 用户id,c.sample_time 执行时间,c.instance_number 实例数,u.username 用户名,a.sql_id SQL编号 
FROMdba_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 
WHEREa.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

SELECTtop 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 
FROMsys.dm_exec_query_stats qs WITH ( nolock ) CROSS apply sys.dm_exec_sql_text ( qs.sql_handle ) AS qt 
WHEREexecution_count > 1 AND total_worker_time / 1000 > 1000 
ORDER BYtotal_worker_time DESC

查询堵塞语句

Oracle

SELECTb.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' ) 
FROMv$sql A,v$session b,v$lock c 
WHEREc.TYPE = 'TX' AND A.sql_id = b.sql_id AND b.SID = c.SID UNION ALL
SELECTb.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' ) 
FROMv$sql A,v$session b,v$lock c 
WHEREc.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 (SELECTblocking_session_id,session_id,sql_handle FROMsys.dm_exec_requests WHEREblocking_session_id <> 0 UNION ALLSELECTA.blocking_session_id,A.session_id,A.sql_handle FROMsys.dm_exec_requests AJOIN CTE_SID B ON A.SESSION_ID = B.BSID ) SELECTC.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 
FROMCTE_SID CJOIN sys.dm_exec_sessions S ON C.sid = s.session_id CROSS APPLY sys.dm_exec_sql_text ( C.sql_handle ) Q 
ORDER BYsid

死锁查询

Oracle

SELECTl.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 登录数据库时间 
FROMv$locked_object l,all_objects o,v$session s 
WHEREl.object_id = o.object_id AND l.session_id = s.SID 
ORDER BYSID,s.serial#;

SqlServer

SELECTrequest_session_id spid,OBJECT_NAME ( resource_associated_entity_id ) tableName 
FROMsys.dm_tran_locks 
WHEREresource_type = 'OBJECT'

死锁处理

Oracle

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

SqlServer

kill spid

查看表空间情况

Oracle

SELECTA.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 
WHEREA.tablespace_name = b.tablespace_name;

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

Oracle

SELECTtablespace_name,file_name,autoextensible 
FROMdba_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

SELECTT.SQL_TEXT,T.FIRST_LOAD_TIME 
FROMv$sqlarea T 
WHERETO_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 BYT.FIRST_LOAD_TIME DESC;

查询未提交的事务和语句

Oracle

SELECTs.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 
FROMv$session s,v$transaction T 
WHEREs.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

SELECTcount( * ) 
FROMv$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 );
BEGINretval := ( CASE WHEN passwd IS NULL THEN NULL ELSE utl_raw.cast_to_raw ( DBMS_OBFUSCATION_TOOLKIT.MD5 ( INPUT_STRING => passwd ) ) END );
RETURN retval;
END;