Oracle12C新特性_DDL日志
---DDL日志
12C之前的版本，如果想要查询执行过的DDL信息，通常考虑三种方法：
1：logminer 2：审计 3：触发器
12C开始，DDL日志可以通过enable_ddl_logging选项实现
4：enable_ddl_logging
下面将查询DDL日志的四种方法分别进行测试：
---1:DDL日志(logminer)
---查询当前日志组
---本次案例数据库没有启动归档模式，可以直接挖掘在线日志文件
select a.group#, a.status, b.member
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;
---执行DDL操作
SQL> create table t001(id number);
SQL> insert into t001 values(1);
SQL> insert into t001 values(2);
SQL> insert into t001 values(3);
SQL> commit;
SQL> truncate table t001;
SQL> drop table t001;
---执行logminer
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE
(LogFileName=>'/u01/app/oracle/oradata/CHENDB/onlinelog/o1_mf_2_g1jtwccv_.log',Options=>dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> create table logmnr_0128 as select * from v$logmnr_contents;
SQL> exec dbms_logmnr.end_logmnr;
---查询操作日志
SQL> select timestamp, operation, seg_owner, sql_redo
from logmnr_0128
where sql_redo like '%t001%'
or sql_redo like '%CHENTEST%'
order by 2;
---2：DDL日志(审计)
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/chendb/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB, EXTENDED
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL> audit table by chentest;
SQL> create table t006(id number);
SQL> alter table t006 add(a varchar2(30) default 'a');
SQL> truncate table t006;
SQL> drop table t006;
SQL> select os_username,
userhost,
terminal,
timestamp,
owner,
action_name,
obj_name,
sql_text
from dba_audit_trail;
---3：DDL日志(触发器)
DDL触发器来自https://www.nmirage.com/database/298.html
(1)创建审计表
(2)创建ddl触发器
(3)执行SQL语句
SQL> create table t005(id number);
SQL> insert into t005 values(1);
SQL> insert into t005 values(2);
SQL> insert into t005 values(3);
SQL> commit;
SQL> alter table t005 add(t_name varchar2(30) default 'a');
SQL> truncate table t005;
SQL> drop table t005;
(4)查看审计信息
SQL> select * from Audit_DDL_OBJ;
---4 DDL日志(enable_ddl_logging)(12C,18C)
---从12C开始，可以通过enable_ddl_logging选项查询DDL日志
---查询enable_ddl_logging
SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';
NAME VALUE DEFAULT_VALUE
enable_ddl_logging FALSE FALSE
---会话级别启用ENABLE_DDL_LOGGING
SQL> ALTER SESSION SET ENABLE_DDL_LOGGING=TRUE;
---执行DDL日志
SQL> create table t002(id number);
SQL> insert into t002 values(1);
SQL> insert into t002 values(2);
SQL> insert into t002 values(3);
SQL> commit;
SQL> truncate table t002;
SQL> drop table t002;
---DDL日志默认生成到/u01/app/oracle/diag/rdbms/chendb/chendb/log/ddl/log.xml文件
[root@oracle-server001 ddl]# pwd
/u01/app/oracle/diag/rdbms/chendb/chendb/log/ddl
[root@oracle-server001 ddl]# ls
log.xml
---查看DDL日志
[root@oracle-server001 ddl]# cat log.xml
msg_id='kpdbLogDDL:22974:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='oracle-server001' host_addr='192.168.2.225'
pid='99417' version='1' con_uid='2495552238'
con_id='3' con_name='CHENPDB'>
msg_id='kpdbLogDDL:22974:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='oracle-server001' host_addr='192.168.2.225'
pid='99417' con_uid='2495552238' con_id='3'
con_name='CHENPDB'>
msg_id='kpdbLogDDL:22974:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='oracle-server001' host_addr='192.168.2.225'
pid='99417' con_uid='2495552238' con_id='3'
con_name='CHENPDB'>