4、DDL日志在12c R1中,你现在可以将DDL操作写入xml和日志文件中。这对于了解谁在什么时间执行了create或drop命令是十分有用的。要开启这一功能必须对ENABLE_DDL_LOGGING初始参数加以配置。这一参数可以在数据库或会话级加以设置。当此参数为启用状态,所有的DDL命令会记录在$ORACLE_BASE/diag/rdbms/DBNAME/log/ddl 路径下的xml和日志文件中。一个xml中包含DDL命令,IP地址,时间戳等信息。这可以帮助确定在什么时候对用户或表进行了删除亦或是一条DDL语句在何时触发。
--开启DDL日志方法:
ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
--示例:
SQL> show parameter ddl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> show parameter ddl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean TRUE
[oracle@lfdz1 ~]$ cd /u01/app/oracle/diag/rdbms/
[oracle@lfdz1 rdbms]$ ls
llfdb
[oracle@lfdz1 rdbms]$ cd llfdb/llfdb/
[oracle@lfdz1 llfdb]$ ls
alert hm incpkg lck metadata metadata_pv sweep
cdump incident ir log metadata_dgif stage trace
[oracle@lfdz1 llfdb]$ cd log/ddl/
[oracle@lfdz1 ddl]$ ls
SQL> create table test1 as select * from test;
Table created.
[oracle@lfdz1 ddl]$ ls
log.xml
[oracle@lfdz1 ddl]$ tail -f log.xml
<msg time='2016-06-21T14:36:32.546+08:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='lfdz1' host_addr='192.168.10.10'
version='1'>
<txt>create table test1 as select * from test
</txt>
</msg>
注意:
以下的DDL语句可能会记录在xml或日志文件中:
CREATE|ALTER|DROP|TRUNCATE TABLE
DROP USER
CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
--开启DDL日志方法:
ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
--示例:
SQL> show parameter ddl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean FALSE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> show parameter ddl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout integer 0
enable_ddl_logging boolean TRUE
[oracle@lfdz1 ~]$ cd /u01/app/oracle/diag/rdbms/
[oracle@lfdz1 rdbms]$ ls
llfdb
[oracle@lfdz1 rdbms]$ cd llfdb/llfdb/
[oracle@lfdz1 llfdb]$ ls
alert hm incpkg lck metadata metadata_pv sweep
cdump incident ir log metadata_dgif stage trace
[oracle@lfdz1 llfdb]$ cd log/ddl/
[oracle@lfdz1 ddl]$ ls
SQL> create table test1 as select * from test;
Table created.
[oracle@lfdz1 ddl]$ ls
log.xml
[oracle@lfdz1 ddl]$ tail -f log.xml
<msg time='2016-06-21T14:36:32.546+08:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='lfdz1' host_addr='192.168.10.10'
version='1'>
<txt>create table test1 as select * from test
</txt>
</msg>
注意:
以下的DDL语句可能会记录在xml或日志文件中:
CREATE|ALTER|DROP|TRUNCATE TABLE
DROP USER
CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30192548/viewspace-2121500/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30192548/viewspace-2121500/