Oracle 获取表的变更记录
前言
有的时候需要统计实例中表的变更记录，除了创建触发器统计，也可以直接利用内部的 DBA_TAB_MODIFICATIONS 视图获取变更信息
DBA_TAB_MODIFICATIONS
视图概述
DBA_TAB_MODIFICATIONS 记录了自上次收集表统计信息以来对数据库中所有表的修改操作，包括增删改查。系统后台调用 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 更新，当然也可以手工调用更新信息
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> desc DBA_TAB_MODIFICATIONS
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER
| Column | Datatype | NULL | Description |
|---|---|---|---|
TABLE_OWNER |
VARCHAR2(128) |
Owner of the modified table | |
TABLE_NAME |
VARCHAR2(128) |
Name of the modified table | |
PARTITION_NAME |
VARCHAR2(128) |
Name of the modified partition | |
SUBPARTITION_NAME |
VARCHAR2(128) |
Name of the modified subpartition | |
INSERTS |
NUMBER |
Approximate number of inserts since the last time statistics were gathered |
|
UPDATES |
NUMBER |
Approximate number of updates since the last time statistics were gathered |
|
DELETES |
NUMBER |
Approximate number of deletes since the last time statistics were gathered |
|
TIMESTAMP |
DATE |
Indicates the last time the table was modified | |
TRUNCATED |
VARCHAR2(3) |
Indicates whether the table has been truncated since the last analyze ( YES) or not (NO) |
|
DROP_SEGMENTS |
NUMBER |
Number of partition and subpartition segments dropped since the last analyze |
手动更新 DBA_TAB_MODIFICATIONS 视图的信息：
除了系统后台自动更新信息，也可以手动更新视图信息
1
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
查看表的变更信息
需要注意的是，如果不加条件的话查询结果返回为空
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select count(1) from DBA_TAB_MODIFICATIONS;
COUNT(1)
----------
3280
SQL> select * from DBA_TAB_MODIFICATIONS;
no rows selected
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> select TABLE_OWNER, TABLE_NAME, count(1) from DBA_TAB_MODIFICATIONS group by TABLE_OWNER, TABLE_NAME;
no rows selected
可以按照用户名分组查看每个用户涉及的多少张表变更，在逐步添加条件，如果一开始就确定需要查询表名就可以省略这一步，直接带入表名和用户名查询对应的信息就行：
1
2
3
4
5
6
7
8
9
SQL> select TABLE_OWNER, count(1) from DBA_TAB_MODIFICATIONS group by TABLE_OWNER;
TABLE_OWNER COUNT(1)
------------------------------ ----------
x rows selected.
select TABLE_OWNER, TABLE_NAME, count(1) from DBA_TAB_MODIFICATIONS where TABLE_OWNER='&owner' group by TABLE_OWNER, TABLE_NAME;
select * from DBA_TAB_MODIFICATIONS where TABLE_OWNER='&owner' group by TABLE_OWNER, TABLE_NAME;
也可以按照时间筛选，如果需要导出 csv 的话，可以按照以下语句拼接导出结果
1
2
3
alter session set nls_date_format='yyyy-mm-dd';
select table_owner||','||TABLE_NAME||','|| TIMESTAMP || ',' || INSERTS||','|| UPDATES ||','|| DELETES ||','|| TRUNCATED ||','|| DROP_SEGMENTS from dba_tab_modifications where table_owner = '&owner' order by table_owner,TABLE_NAME,TIMESTAMP;
如果需要更加复杂的分析或者过滤，有的表已经被删除了，在 dba_tab_modifications 视图中记录的是以 ‘BIN’ 前缀的对象名（类似：BIN$HyvQLM+oPHjgY6oLCgoy2A==$0 ），如果没有关闭回收站的话，可以结合回收站获取原始表名
1
2
3
4
5
6
7
8
create table recdml as select * from dba_tab_modifications where table_owner = '&owner';
alter table recdml add source_name VARCHAR2(30) default null ;
update recdml set source_name = (select ORIGINAL_NAME from DBA_RECYCLEBIN where TABLE_NAME = OBJECT_NAME and table_owner = owner);
select 'table_owner,TABLE_NAME,TIMESTAMP,source_name,INSERTS,UPDATES,DELETES,TRUNCATED,DROP_SEGMENTS' from dual ;
select '用户,表名,日期,原始表名,插入记录数,更新记录,删除记录,truncate,drop记录' from dual;
select table_owner||','||TABLE_NAME||','|| TIMESTAMP || ',' || source_name || ',' || INSERTS||','|| UPDATES ||','|| DELETES ||','|| TRUNCATED ||','|| DROP_SEGMENTS from recdml where table_owner = '&owner' and TIMESTAMP > '2024-07-31' order by table_owner,TABLE_NAME,TIMESTAMP;
查看表是否开启 monitor
只有对表开启了 monitor 才会有记录：
1
select MONITORING from dba_tables t where t.table_name='&table_name';
一般默认都会开启的，如果开启了 monitor 但是查询表却没有信息，可以手动刷新 DBA_TAB_MODIFICATIONS 的信息。需要注意如果对应的表执行了统计收集，在 DBA_TAB_MODIFICATIONS 视图上的记录会消失，因为视图记录的是自最近一次统计收集之后表变更信息
关闭/开启 monitor
通过修改隐藏参数 _dml_monitoring_enabled 可以关闭该功能
1
alter system set "_dml_monitoring_enabled"=false scope=memory;
关闭 monitor 之后对表的变更操作记录，就不会在记录在 DBA_TAB_MODIFICATIONS 视图中了
1
2
-- 开启 monitor
alter system set "_dml_monitoring_enabled"=true scope=memory;
总结
-
create table创建的表，插入的数据不会被记录。
-
即使执行了 dml 操作,且没有 commit ，在刷新后
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO，sys.dba_tab_modifications 也会存在记录。 -
收集表统计信息后，该视图里对应的表的变更记录会消失，因为视图中的采集信息是自最近一次统计收集之后的变更信息。
-
通过隐含参数
_dml_monitoring_enabled可以关闭 monitor -
对于已经删除的表名信息可以结合回收站获取
参考
https://mwidlake.wordpress.com/2010/07/02/dba_tab_modifications/