ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 统计表每日修改量,表记录数

统计表每日修改量,表记录数

原创 Linux操作系统 作者:wei-xh 时间:2010-06-11 17:43:13 0 删除 编辑

SELECT A.TABLE_NAME,
       round(A.修改总量 /
       TO_NUMBER(TO_DATE(TO_CHAR(A.修改结束日期, 'YYYYMMDD'), 'YYYYMMDD') -
                 TO_DATE(TO_CHAR(B.修改开始日期, 'YYYYMMDD'), 'YYYYMMDD') + 1),2)
  FROM (SELECT TABLE_NAME,
               SUM(INSERTS + UPDATES + DELETES) 修改总量,
               TIMESTAMP 修改结束日期
          FROM USER_TAB_MODIFICATIONS
         WHERE TABLE_NAME IN
               ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01',
                'AB02', 'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04',
                'AC05', 'AC06', 'AC43', 'AE03', 'AZ01', 'IC03', 'AC62',
                'AC69', 'AC87', 'AC88', 'AC89', 'AC93', 'AC94', 'AC95',
                'AC96', 'AC97', 'AC98', 'AE50', 'AE55', 'AE56', 'AE57',
                'AE58', 'IC02', 'IC10', 'IC40', 'IC63', 'LB05', 'LC01',
                'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31', 'ZA32',
                'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
                'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47',
                'KC51', 'KC60', 'KF40', 'AB14')
         GROUP BY TABLE_NAME, TIMESTAMP) A,
       (SELECT TABLE_NAME, LAST_ANALYZED 修改开始日期
          FROM USER_TAB_STATISTICS
         WHERE TABLE_NAME IN
               ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01',
                'AB02', 'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04',
                'AC05', 'AC06', 'AC43', 'AE03', 'AZ01', 'IC03', 'AC62',
                'AC69', 'AC87', 'AC88', 'AC89', 'AC93', 'AC94', 'AC95',
                'AC96', 'AC97', 'AC98', 'AE50', 'AE55', 'AE56', 'AE57',
                'AE58', 'IC02', 'IC10', 'IC40', 'IC63', 'LB05', 'LC01',
                'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31', 'ZA32',
                'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
                'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47',
                'KC51', 'KC60', 'KF40', 'AB14')) B
 WHERE A.TABLE_NAME = B.TABLE_NAME
 ORDER BY 1;

SELECT TABLE_NAME, NUM_ROWS
  FROM USER_TAB_STATISTICS
 WHERE TABLE_NAME IN
       ('AA02', 'AA10_TABLE', 'AA52', 'AA53', 'AA54', 'AA55', 'AB01', 'AB02',
        'AB07', 'AB09', 'AB15', 'AC01', 'AC02', 'AC04', 'AC05', 'AC06',
        'AC43', 'AE03', 'AZ01', 'IC03', 'AC62', 'AC69', 'AC87', 'AC88',
        'AC89', 'AC93', 'AC94', 'AC95', 'AC96', 'AC97', 'AC98', 'AE50',
        'AE55', 'AE56', 'AE57', 'AE58', 'IC02', 'IC10', 'IC40', 'IC63',
        'LB05', 'LC01', 'LC10', 'LC30', 'LC31', 'MC01', 'MC02', 'ZA31',
        'ZA32', 'KA02', 'KA03', 'KA04', 'KA52', 'KA53', 'KB01', 'KC04',
        'KC21', 'KC22', 'KC24', 'KC33', 'KC41', 'KC45', 'KC47', 'KC51',
        'KC60', 'KF40', 'AB14')
 ORDER BY TABLE_NAME;

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/22034023/viewspace-665052/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2425305