查询各种数据源，查询运行中的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;

5、查看数据库连接数

SELECT db, COUNT(*) AS connection_count
FROM information_schema.processlist
WHERE db IS NOT NULL
GROUP BY db
ORDER BY connection_count DESC;

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;