1:15个mysql dba常用脚本
1:显示数据库版本信息:
SELECT @@version;
2:显示数据库实例信息:
SHOW VARIABLES LIKE "%version%";
3:显示数据库表空间使用情况:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;
4:显示数据库连接信息:
SHOW PROCESSLIST;
5:显示数据库查询缓存命中率:
SHOW STATUS LIKE 'Qcache_hits';
6:显示数据库锁信息:
SHOW OPEN TABLES WHERE In_use > 0;
7:显示数据库慢查询:
SELECT * FROM mysql.slow_log;
8:显示数据库索引统计信息:
SHOW INDEX FROM table_name;
9:显示数据库存储过程和函数:
SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;
10:显示数据库备份和恢复脚本:
# 备份数据库
mysqldump -u username -p database_name > backup.sql
# 还原数据库
mysql -u username -p database_name < backup.sql
11:查询数据库用户列表
SELECT User, Host FROM mysql.user;
12:创建新用户并授权
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;
13:修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
14:优化表
OPTIMIZE TABLE table_name;
15:查看数据库状态
SHOW STATUS;
2:10个oracle dba常用脚本
-
查询锁定的进程并解锁:
-
查询锁定进程:
sql复制代码
SELECT L.SESSION_ID, S.SERIAL#, L.ORACLE_USERNAME, L.OS_USER_NAME, S.MACHINE, S.TERMINAL, O.OBJECT_NAME
FROM V$LOCKED_OBJECT L
INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID;
-
解锁进程(需替换'SESSION_ID, SERIAL#'):
sql复制代码
ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
-
-
监控等待事件:
-
查询enqueue等待事件:
sql复制代码
SELECT b.SID, b.serial#, b.username, machine, event, wait_time, ...
FROM v$session_wait a, v$session b
WHERE a.event = 'enqueue' ...;
-
-
监控表空间I/O:
-
查询表空间读写情况:
sql复制代码
SELECT df.tablespace_name, f.phyrds, f.phywrts ...
FROM v$filestat f, dba_data_files df
WHERE f.file# = df.file_id;
-
-
查询长时间运行的SQL:
-
需要编写更复杂的脚本或使用Oracle内置工具如AWR报告。
-
-
查看用户数据库对象:
-
查看用户拥有的表、视图、索引等:
sql复制代码
SELECT object_name FROM user_objects WHERE object_type = 'TABLE'; -- 示例为表
-
-
查看用户权限:
-
查询用户所拥有的权限:
sql复制代码
SELECT * FROM session_privs;
-
-
修改用户密码:
-
修改用户密码:
sql复制代码
ALTER USER username IDENTIFIED BY newpassword;
-
-
新建、删除用户:
-
新建用户:
sql复制代码
CREATE USER username IDENTIFIED BY password;
-
删除用户(级联删除):
sql复制代码
DROP USER username CASCADE;
-
-
给用户赋权:
-
给用户分配权限:
sql复制代码
GRANT CREATE SESSION, CREATE TABLE TO username;
-
-
监控文件系统的I/O:
-
查询数据文件的读写情况:
sql复制代码
SELECT substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts
FROM v$datafile a, v$filestat b
WHERE a.file# = b.file#;
-
阅读 1.2万