table historic count

table_rowcount.sql

WITH TableList AS  
(SELECT object_id,           
 OWNER,           
 object_name      
 FROM dba_objects     
 WHERE object_type='TABLE'       
 AND object_name  = UPPER(:TNAME)  ) ,   
 HistTableInfo AS   
 (SELECT TableList.OWNER,           
  OBJECT_NAME                         
  AS TABLE_NAME,           
  TO_CHAR(ANALYZETIME,'YYYY-MM-DD HH24:MI') AS AnalyzeTime,           
  OTH.ROWCNT      
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY OTH,           
  TableList     
  WHERE OTH.OBJ# = TableList.OBJECT_ID  ),   CurrTableInfo AS  
  (SELECT TableList.OWNER,           
   TABLE_NAME,           
   TO_CHAR(LAST_ANALYZED,'YYYY-MM-DD HH24:MI') AS AnalyzeTime,           
   num_rows                                    AS ROWCNT      
   FROM DBA_TABLES,           TableList     
   WHERE DBA_TABLES.TABLE_NAME = TableList.object_name       
   AND DBA_TABLES.OWNER = TableList.owner)
   SELECT * FROM HistTableInfo UNION SELECT * FROM CurrTableInfo ORDER BY 1,2,3;


http://ba6.us/?q=node/231


SELECT ob.owner, ob.object_name, ob.subobject_name,
ob.object_type,obj#, savtime, flags, rowcnt, blkcnt, 
avgrln ,samplesize, analyzetime, cachedblk, cachehit, logicalread  
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob 
WHERE owner=upper('&OWNER') and object_name=upper('&TABLE') and object_type in ('TABLE') and object_id=obj# order by savtime asc;