某个时间断内表数据变化量

oracle中怎么知道表在过去的时间段内数据量变化了多少呢？利用timestamp 可以方便的知道这一信息。

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as CAICPROD

 

SQL> SELECT COUNT(1)
  2    FROM test_main as of timestamp to_timestamp('2011-04-06 12:00:00', 'yyyy-mm-dd hh24:mi:ss');

  COUNT(1)
----------
   2396959

SQL> SELECT COUNT(1)
  2    FROM test_main as of timestamp to_timestamp('2011-04-06 11:50:00', 'yyyy-mm-dd hh24:mi:ss')  ;

  COUNT(1)
----------
   2396911
  

 

查询间隔不能太长，否则会报ORA-01555

 

SQL>  SELECT COUNT(1)
  2        FROM prpcmain as of timestamp to_timestamp('2011-04-05 11:50:00', 'yyyy-mm-dd hh24:mi:ss')  ;

 

ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small