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#;
本文来自博客园,作者:CharyGao,转载请注明原文链接:https://www.cnblogs.com/Chary/articles/18279371