TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
检查表是否超过10%的数据变化
对象级:
- set serveroutput on
- declare
- mystaleobjs dbms_stats.objecttab;
- begin
- -- check whether there is any stale objects
- dbms_stats.gather_schema_stats(ownname=>'SCOTT', options=>'LIST STALE',objlist=>
- mystaleobjs);
- for i in 1 .. mystaleobjs.count loop
- dbms_output.put_line(mystaleobjs(i).objname);
- end loop;
- end;
- /
复制代码 全库级:
- set serveroutput on
- DECLARE
- ObjList dbms_stats.ObjectTab;
- BEGIN
- dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
- FOR i in ObjList.FIRST..ObjList.LAST
- LOOP
- dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
- END LOOP;
- END;
- /
复制代码 select:
- SELECT TABLES.OWNER,
- TABLES.TABLE_NAME,
- ROUND((DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
- FROM DBA_TABLES TABLES,
- DBA_TAB_MODIFICATIONS MODIFICATIONS
- WHERE TABLES.OWNER = MODIFICATIONS.TABLE_OWNER
- AND TABLES.TABLE_NAME = MODIFICATIONS.TABLE_NAME
- AND NUM_ROWS > 0
- AND ROUND((DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
- ORDER BY 3 DESC /
复制代码
|
|