查看Oracle数据库SQL执行历史

2015年03月26日 23:20:29 chinadm123 阅读数：14131

方法1：使用LogMiner工具

优点：可以完全挖掘日志内容，找出所有执行过的SQL语句

缺点：

1. 如果没有启用归档日志，则只能对联机日志进行挖掘

2. 需要掌握LogMiner的用法

方法2：查看HIST视图

优点：简单，只要使用普通的select语句查询

缺点：Oracle保留约1周的历史，所以要查太早的就不可能了

举例：

-- 找出哪个数据库用户用什么程序在最近三天执行过delete或truncate table的操作

  SELECT c.username,
         a.program,
         b.sql_text,
         b.command_type,
         a.sample_time
    FROM dba_hist_active_sess_history a
         JOIN dba_hist_sqltext b
            ON a.sql_id = b.sql_id
         JOIN dba_users c
            ON a.user_id = c.user_id
   WHERE     a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
         AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

下面是Oracle 10.2 command type definition的完整列表

1 CREATE TABLE

2 INSERT

3 SELECT

4 CREATE CLUSTER

5 ALTER CLUSTER

6 UPDATE

7 DELETE

8 DROP CLUSTER

9 CREATE INDEX

10 DROP INDEX

11 ALTER INDEX

12 DROP TABLE

13 CREATE SEQUENCE

14 ALTER SEQUENCE

15 ALTER TABLE

16 DROP SEQUENCE

17 GRANT OBJECT

18 REVOKE OBJECT

19 CREATE SYNONYM

20 DROP SYNONYM

21

CREATE VIEW

22

DROP VIEW

23

VALIDATE INDEX

24

CREATE PROCEDURE

25

ALTER PROCEDURE

26

LOCK

27

NO-OP

28

RENAME

29

COMMENT

30

AUDIT OBJECT

31

NOAUDIT OBJECT

32

CREATE DATABASE LINK

33

DROP DATABASE LINK

34

CREATE DATABASE

35

ALTER DATABASE

36

CREATE ROLLBACK SEG

37

ALTER ROLLBACK SEG

38

DROP ROLLBACK SEG

39

CREATE TABLESPACE

40

ALTER TABLESPACE

41

DROP TABLESPACE

42

ALTER SESSION

43

ALTER USER

44

COMMIT

45

ROLLBACK

46

SAVEPOINT

47

PL/SQL EXECUTE

48

SET TRANSACTION

49

ALTER SYSTEM

50

EXPLAIN

51

CREATE USER

52

CREATE ROLE

53

DROP USER

54

DROP ROLE

55

SET ROLE

56

CREATE SCHEMA

57

CREATE CONTROL FILE

59

CREATE TRIGGER

60

ALTER TRIGGER

61

DROP TRIGGER

62

ANALYZE TABLE

63

ANALYZE INDEX

64

ANALYZE CLUSTER

65

CREATE PROFILE

66

DROP PROFILE

67

ALTER PROFILE

68

DROP PROCEDURE

70

ALTER RESOURCE COST

71

CREATE SNAPSHOT LOG

72

ALTER SNAPSHOT LOG

73

DROP SNAPSHOT LOG

74

CREATE SNAPSHOT

75

ALTER SNAPSHOT

76

DROP SNAPSHOT

77

CREATE TYPE

78

DROP TYPE

79

ALTER ROLE

80

ALTER TYPE

81

CREATE TYPE BODY

82

ALTER TYPE BODY

83

DROP TYPE BODY

84

DROP LIBRARY

85

TRUNCATE TABLE

86

TRUNCATE CLUSTER

91

CREATE FUNCTION

92

ALTER FUNCTION

93

DROP FUNCTION

94

CREATE PACKAGE

95

ALTER PACKAGE

96

DROP PACKAGE

97

CREATE PACKAGE BODY

98

ALTER PACKAGE BODY

99

DROP PACKAGE BODY

100

LOGON

101

LOGOFF

102

LOGOFF BY CLEANUP

103

SESSION REC

104

SYSTEM AUDIT

105

SYSTEM NOAUDIT

106

AUDIT DEFAULT

107

NOAUDIT DEFAULT

108

SYSTEM GRANT

109

SYSTEM REVOKE

110

CREATE PUBLIC SYNONYM

111

DROP PUBLIC SYNONYM

112

CREATE PUBLIC DATABASE LINK

113

DROP PUBLIC DATABASE LINK

114

GRANT ROLE

115

REVOKE ROLE

116

EXECUTE PROCEDURE

117

USER COMMENT

118

ENABLE TRIGGER

119

DISABLE TRIGGER

120

ENABLE ALL TRIGGERS

121

DISABLE ALL TRIGGERS

122

NETWORK ERROR

123

EXECUTE TYPE

157

CREATE DIRECTORY

158

DROP DIRECTORY

159

CREATE LIBRARY

160

CREATE JAVA

161

ALTER JAVA

162

DROP JAVA

163

CREATE OPERATOR

164

CREATE INDEXTYPE

165

DROP INDEXTYPE

167

DROP OPERATOR

168

ASSOCIATE STATISTICS

169

DISASSOCIATE STATISTICS

170

CALL METHOD

171

CREATE SUMMARY

172

ALTER SUMMARY

173

DROP SUMMARY

174

CREATE DIMENSION

175

ALTER DIMENSION

176

DROP DIMENSION

177

CREATE CONTEXT

178

DROP CONTEXT

179

ALTER OUTLINE

180

CREATE OUTLINE

181

DROP OUTLINE

182

UPDATE INDEXES

183

ALTER OPERATOR

end

转载自：http://blog.csdn.net/wzy0623/article/details/9388061

https://blog.csdn.net/chinadm123/article/details/44659373

 

 

 

查看MySQL数据库的SQL语句的执行记录日志

方法1：可以使用processlist查看SQL执行语句，但是有个弊端，就是只能查看正在执行的sql语句，无法查看历史执行的语句。

> use information_schema;

> show processlist;
或者
> select * from information_schema.`PROCESSLIST` where info is not null;
+--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
| ID     | USER | HOST      | DB                 | COMMAND | TIME | STATE                | INFO                                                                  | TIME_MS | STAGE | MAX_STAGE | PROGRESS |
+--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
| 347182 | root | localhost | information_schema | Query   |    0 | Filling schema table | select * from information_schema.`PROCESSLIST` where info is not null |   0.502 |     0 |         0 |    0.000 |
+--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
1 row in set (0.00 sec)

方法2：开启数据库的日志模式，通过日志查看历史执行记录

* 查看当前配置
> show variables like '%log_output%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          |
| general_log_file | /var/log/mariadb/mariadb.log |
+------------------+------------------------------+

* 开启&关闭日志模式（可选择输出到表或文件中）：
> SET GLOBAL log_output = 'FILE';  SET GLOBAL general_log = 'ON';   //日志开启（日志输出到文件）
> SET GLOBAL log_output = 'FILE';  SET GLOBAL general_log = 'OFF';  //日志关闭
或者
> SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'ON';   //日志开启（日志输出到表：mysql.general_log）
> SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'OFF';  //日志关闭

> show variables like '%log_output%'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

> show variables like '%general_log%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/log/mariadb/mariadb.log |
+------------------+------------------------------+

* 查看日志文件中的执行记录：
#tail -f /var/log/mariadb/mariadb.log
......
347189 Query INSERT INTO `table_copy1` (`id`, `col02_str`, `col03_str`, `col04_int`, `col05_integer`, `col06_bigint`) 
 VALUES ( 1,  'aaaa',  'bbbbb',  20,  30,  655360),
        ( 2,  'xxxx',  'yyyy',  21,  333,  65536000),
        ( 3,  'asasdf',  'sdkfjdkf',  55,  900,  2222),
        ( 4,  'test',  'testaaa',  11,  22,  33),
        ( 5,  'test05',  'testssssss',  222,  222,  222),
        ( 6,  'test06',  'aaaa',  111,  111,  222),
        ( 7,  'aaaa',  'bbbb',  22,  22,  22),
        ( 8,  '你好',  'hello',  25,  25,  25),
        ( 9,  'aaa',  'aaa',  11,  11,  11),
        ( 10,  '',  'bbbbddddrrrrssss4444',  null,  null,  null),
347189 Query    commit
......

* 表查询执行记录：
> SELECT * from mysql.general_log ORDER BY event_time DESC;

* 日志查询执行记录
> SELECT * from mysql.general_log ORDER BY event_time DESC;

* 清空表
> truncate table mysql.general_log;  //该表仅支持truncate不支持delete