Chip

13 积分	0 好友	6 主题

发消息

[升级] 查询未更新的表

1^#

发表于 2013-12-4 11:19:37 | 查看: 3962| 回复: 5

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.

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

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

6^#

发表于 2013-12-9 16:11:48

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

回复只看该作者道具举报

Chip

5^#

发表于 2013-12-9 15:26:34

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

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