查看Oracle数据库SQL执行历史

小灰灰 2022-04-12 04:08 965阅读 0赞

# 查看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

CREATE VIEW

DROP VIEW

VALIDATE INDEX

CREATE PROCEDURE

ALTER PROCEDURE

LOCK

NO-OP

RENAME

COMMENT

AUDIT OBJECT

NOAUDIT OBJECT

CREATE DATABASE LINK

DROP DATABASE LINK

CREATE DATABASE

ALTER DATABASE

CREATE ROLLBACK SEG

ALTER ROLLBACK SEG

DROP ROLLBACK SEG

CREATE TABLESPACE

ALTER TABLESPACE

DROP TABLESPACE

ALTER SESSION

ALTER USER

COMMIT

ROLLBACK

SAVEPOINT

PL/SQL EXECUTE

SET TRANSACTION

ALTER SYSTEM

EXPLAIN

CREATE USER

CREATE ROLE

DROP USER

DROP ROLE

SET ROLE

CREATE SCHEMA

CREATE CONTROL FILE

CREATE TRIGGER

ALTER TRIGGER

DROP TRIGGER

ANALYZE TABLE

ANALYZE INDEX

ANALYZE CLUSTER

CREATE PROFILE

DROP PROFILE

ALTER PROFILE

DROP PROCEDURE

ALTER RESOURCE COST

CREATE SNAPSHOT LOG

ALTER SNAPSHOT LOG

DROP SNAPSHOT LOG

CREATE SNAPSHOT

ALTER SNAPSHOT

DROP SNAPSHOT

CREATE TYPE

DROP TYPE

ALTER ROLE

ALTER TYPE

CREATE TYPE BODY

ALTER TYPE BODY

DROP TYPE BODY

DROP LIBRARY

TRUNCATE TABLE

TRUNCATE CLUSTER

CREATE FUNCTION

ALTER FUNCTION

DROP FUNCTION

CREATE PACKAGE

ALTER PACKAGE

DROP PACKAGE

CREATE PACKAGE BODY

ALTER PACKAGE BODY

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][http_blog.csdn.net_wzy0623_article_details_9388061]

[https://blog.csdn.net/chinadm123/article/details/44659373][https_blog.csdn.net_chinadm123_article_details_44659373]

[chinadm123]: https://me.csdn.net/chinadm123
[http_blog.csdn.net_wzy0623_article_details_9388061]: http://blog.csdn.net/wzy0623/article/details/9388061
[https_blog.csdn.net_chinadm123_article_details_44659373]: https://blog.csdn.net/chinadm123/article/details/44659373