查询各种数据源,查询运行中的sql以及终止进程总结
mysql
1、查询运行中的sql
(1) SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE db='xx';
(2) show processlist;
select concat('kill ',id ,';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE db='xx';
2、杀死进程
kill pId;
3、查询mysql活动事务的状态
SELECT * FROM information_schema.INNODB_TRX;
4、查看使用或者锁定的表
SHOW OPEN TABLES WHERE In_use > 0;
pg
1、查询正在执行中的sql
SELECT * FROM pg_stat_activity
where state = 'active';
2、--最大连接数
show max_connections
3、杀死进程
SELECT pg_terminate_backend(pId)
FROM pg_stat_activity
WHERE state = 'active';
4、--杀死进程语句
SELECT 'select pg_terminate_backend('||pid||');' FROM pg_stat_activity
where state = 'active';
oracle
1、查询最大连接数,当前连接数
SELECT
(SELECT value FROM v$parameter WHERE name = 'processes') AS max_connections,
(SELECT COUNT(*) FROM v$session WHERE username IS NOT NULL) AS current_connections
FROM dual;
2、查询正在执行的sql
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.sql_id,
s.prev_sql_id,
q.sql_text,
s.program,
s.machine,
s.osuser,
s.logon_time
FROM
v$session s
LEFT JOIN
v$sql q
ON
s.sql_id = q.sql_id
WHERE
s.status = 'ACTIVE' -- 只查询状态为活动的会话
AND s.username IS NOT NULL -- 排除后台进程
ORDER BY
s.logon_time DESC;