使用TKPROF从跟踪文件中整理SQL

先生成跟踪文件:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

ALTER SESSION SET SQL_TRACE = TRUE;

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);

整理sql

tkprof tracefile outputfile record=filename

使用审计功能记录数据库的所有SQL

首先打开审计功能：

alter system set audit_trail=db_extended scope=spfile;

extended是指记录SQL的语句
重新启动数据库，执行下面的审计功能，记录数据库的所有活动
audit ALTER SEQUENCE;
audit ALTER TABLE;
audit COMMENT TABLE;
audit DATABASE LINK ;
audit EXECUTE PROCEDURE ;
audit GRANT PROCEDURE;
audit GRANT SEQUENCE;
audit GRANT TABLE ;
audit INDEX ;
audit INSERT TABLE ;
audit LOCK TABLE;
audit NOT EXISTS;
audit PROCEDURE;
audit PROFILE ;
audit ROLE;
audit SELECT SEQUENCE ;
audit SELECT TABLE ;
audit SEQUENCE ;
audit SESSION ;
audit SYNONYM;
audit SYSTEM AUDIT ;
audit SYSTEM GRANT ;
audit TABLE ;
audit TABLESPACE ;
audit TRIGGER ;
audit UPDATE TABLE ;
audit USER ;
audit VIEW ;

可在视图DBA_AUDIT_TRAIL或表SYS.AUD$中查询审计记录；
SELECT username, timestamp, action_name FROM dba_audit_trail ;
select * from dba_stmt_audit_opts;

查询审计记录的SQL语句

SELECT sql_text FROM dba_audit_trail ;

可在视图DBA_AUDIT_TRAIL或表SYS.AUD$中查询审计记录；
SELECT username, timestamp, action_name FROM dba_audit_trail WHERE username = 'ZHANGSAN';

可使用exp或expd将审计记录导出备份
可使用下面的SQL命令删除一年前的审计记录：
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -356;

下面的命令全部删除：

 truncate table sys.aud$;

取消审计：

alter system set audit_trail='NONE' SCOPE=SPFILE;

使用存储概要记录数据库的所有SQL