查询未更新的表

Chip

5000张表的系统，因为开发人员平时在上面修修补补，留下很多垃圾信息需要删除。

现在有这么个需求：找出数据库中，大于100行，且1个月内未更新的表。

尝试了查询DBA_TAB_MODIFICATIONS视图，结果不太准确。该视图只能显示数据量变化大约10%的表。

Why does DBA_TAB_MODIFICATIONS sometimes have no values (文档 ID 762738.1)

The updates to the table *_tab_modifications are related to the volumne of updates for a table.
There is a need of approximatly 10% of datavolumn changes. Just only on single update of the row for example might not lead to fill the *_tab_modifications.

是否有更准确的方法，可以满足我的这个需求？

Liu Maclean(刘相兵

没有原生态的功能， DBA_TAB_MODIFICATIONS 是不准确的


理论上你可以参考


select  scn_to_timestamp( max(ora_rowscn)) from TABNAME;  ==10g以后


但也不保证100%准确

545459983

把最近一个月的归档日志搜集一下，然后日志挖掘。这个思路怎么样？

Liu Maclean(刘相兵

545459983 发表于 2013-12-4 13:32
把最近一个月的归档日志搜集一下，然后日志挖掘。这个思路怎么样？

可以  但 劳民伤财 ，而且10g以后可能挖不到 当没有打开supplemental log

Chip

最后是如下实现的，输出的结果还等待验证。

1. -- 1.查看数据库中的所有用户，确认检查范围
   
2. select * from dba_users where default_tablespace <> 'SYSTEM' and ACCOUNT_STATUS='OPEN';
   
3. 
   
4. -- 2.执行dbms_stats.gather_schema_stats函数，做“非采样”分析
   
5. exec dbms_stats.gather_schema_stats(
   
6.         ownname => 'YW',
   
7.         options => 'GATHER AUTO',
   
8.         estimate_percent => null,
   
9.         method_opt => 'for all columns size repeat',
   
10.         degree =>15)
    
11. 
    
12. --3. 立即刷新状态信息
    
13. exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    
14. --附属操作，检查JOB是否有正常执行结束
    
15. SELECT * FROM dba_optstat_operations ORDER BY start_time DESC;
    
16. OPERATION,                        TARGET,                START_TIME,                                                        END_TIME
    
17. gather_schema_stats,YWUSER2,2013/12/8 23:52:03.481843 +08:00,2013/12/8 23:55:18.434827 +08:00
    
18. 
    
19. --4. 执行条件查询(先找出有变化的，再求其补集)
    
20. SELECT c.OWNER,
    
21.        c.TABLE_NAME,
    
22.        c.NUM_ROWS,
    
23.        d.TIMESTAMP AS last_modification
    
24.   FROM all_tables c, sys.dba_tab_modifications d
    
25. WHERE     c.TABLE_NAME NOT IN (SELECT a.TABLE_NAME
    
26.                                   FROM all_tables a,
    
27.                                        sys.dba_tab_modifications b
    
28.                                  WHERE     a.TABLE_NAME = b.TABLE_NAME
    
29.                                        AND TABLE_OWNER = 'YW'
    
30.                                        AND b.TIMESTAMP > SYSDATE - 50)
    
31.        AND OWNER = 'YW'
    
32.        AND c.TABLE_NAME = d.TABLE_NAME
    
33.        AND c.num_rows > 100;
    
34. 
    
35. --5. 验证结果正确性

复制代码

Liu Maclean(刘相兵

关于 dba_tab_modifications   你需要了解下原理， 就知道 其 提供的数据的不准确程度