more than 10% has been modified tables

admin

检查表是否超过10%的数据变化

对象级：

1. set serveroutput on
   
2. 
   
3. declare
   
4. mystaleobjs dbms_stats.objecttab;
   
5. begin
   
6. -- check whether there is any stale objects
   
7. dbms_stats.gather_schema_stats(ownname=>'SCOTT', options=>'LIST STALE',objlist=>
   
8. mystaleobjs);
   
9. for i in 1 .. mystaleobjs.count loop
   
10. dbms_output.put_line(mystaleobjs(i).objname);
    
11. end loop;
    
12. end;
    
13. /

复制代码

全库级：

1. set serveroutput on
   
2. 
   
3. DECLARE
   
4. ObjList dbms_stats.ObjectTab;
   
5. BEGIN
   
6. dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
   
7. FOR i in ObjList.FIRST..ObjList.LAST
   
8. LOOP
   
9. dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
   
10. END LOOP;
    
11. END;
    
12. /

复制代码

select:

1. SELECT TABLES.OWNER,
   
2.         TABLES.TABLE_NAME,
   
3.         ROUND((DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
   
4. FROM DBA_TABLES TABLES,
   
5.         DBA_TAB_MODIFICATIONS MODIFICATIONS
   
6. WHERE TABLES.OWNER = MODIFICATIONS.TABLE_OWNER
   
7.         AND TABLES.TABLE_NAME = MODIFICATIONS.TABLE_NAME
   
8.         AND NUM_ROWS > 0
   
9.         AND ROUND((DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
   
10. ORDER BY 3 DESC /

复制代码

admin

统计信息收集历史：

1. SQL> select table_name,STATS_UPDATE_TIME from dba_TAB_STATS_HISTORY where owner='ZHYU' order by 1,2;
   
2. 
   
3. TABLE_NAME                       STATS_UPDATE_TIME
   
4. ------------------------------ ---------------------------------------------------------------------------
   
5. OBJ                               12-DEC-23 10.07.40.997832 AM +08:00
   
6. OBJ                               12-DEC-23 10.40.39.937381 AM +08:00
   
7. T                               07-DEC-23 10.00.08.153359 PM +08:00
   
8. T                               08-DEC-23 10.00.14.859725 PM +08:00
   
9. T                               09-DEC-23 06.00.09.920366 AM +08:00
   
10. T                               12-DEC-23 10.07.41.117909 AM +08:00
    
11. T1                               08-DEC-23 10.00.09.852079 PM +08:00
    
12. T1                               09-DEC-23 06.00.09.945804 AM +08:00
    
13. T1                               12-DEC-23 10.07.41.137112 AM +08:00
    

复制代码