<\/p>\n
\ud1b5\uacc4\uc815\ubcf4 \uac31\uc2e0 \uc2dd\ubcc4<\/p>\n
<\/p>\n
alter table emp monitoring;<\/p>\n
<\/p>\n
statistics_level\uc774 typical \uc774\uc0c1 \uc124\uc815\uc2dc \uc624\ub77c\ud074\uc744 \ud14c\uc774\ube14\uc5d0 \ubc1c\uc0dd\ud558\ub294 dml\uc744 \ubaa8\ub2c8\ud130\ub9c1\ud55c\ub2e4. \uc218\uc9d1\ub41c \ud14c\uc774\ube14\ubcc4 DML\uc740 *_tab_modifications\ubdf0<\/p>\n
\ub97c \ud1b5\ud574 \uc870\ud68c\ud560\uc218 \uc788\uc73c\uba70, insert,update,delete\ub294 \ub9c8\uc9c0\ub9c9 \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\ub41c \uc774\ud6c4\uc758 dml \ubc1c\uc0dd\ub7c9\uc774\ub2e4.<\/p>\n
\uc624\ub77c\ud074\uc740 \ubaa8\ub2c8\ud130\ub9c1 \ub300\uc0c1 \ud14c\uc774\ube14\uc5d0 10%\uc774\uc0c1 \ubcc0\uacbd\uc774 \ubc1c\uc0dd\ud588\uc744\ub54c \ud574\ub2f9 \ud14c\uc774\ube14\uc744 stale \uc0c1\ud0dc(*_tab_statistics \ubdf0\uc5d0\uc11c stale_stats=yes)\ub85c \ubcc0\uacbd<\/p>\n
\ud558\uace0, gather_database_stats \ub610\ub294 gather_schema_stats \ud504\ub85c\uc2dc\uc800\ub97c \ud638\ucd9c\ud558\uba74\uc11c option \uc778\uc790\uc5d0 ‘gather stale’ \ub610\ub294 ‘gather auto’\ub97c<\/p>\n
\uc9c0\uc815\ud558\uba74 stale \uc0c1\ud0dc\uc778 \ud14c\uc774\ube14\uc5d0 \ub300\ud574 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n
10g\ubd80\ud130 \uc870\ud68c \uac00\ub2a5 \ud558\uace0 11g\uc5d0\uc11c\ub294 object\ubcc4 \uc784\uacc4\uce58 \uc124\uc815\uc774 \uac00\ub2a5<\/p>\n
\uc2e4\uc81c 10%\uc774\uc0c1 \ubcc0\uacbd\uc774 \ub418\uc5b4\uc11c *_tab_modifications *_tab_statistics\ubdf0\uc758 stale_stats \uceec\ub7fc\uc5d0 \ubcc0\ud654\uac00 \uc0dd\uae30\uc9c0 \uc54a\ub294\uac83\uc740 \ubcc0\uacbd\ub41c \uacb0\uacfc\ub97c shared_pool\uc5d0 \ubaa8\uc558\ub2e4\uac00 smon\uc774 3\uc2dc\uac04\u00a0 \uc8fc\uae30\ub85c \ub515\uc154\ub108\ub9ac\uc5d0 \ubc18\uc601\ud558\uae30 \ub54c\ubb38\uc774\ub2e4. \uc989\uc2dc \ubc18\uc601\uc744 \ud558\ub824\uba74 dbms_stats.flush_database_monitoring_info \ud504\ub85c\uc2dc\uc800\ub97c \ud638\ucd9c\ud558\uba74 \ub41c\ub2e4.<\/p>\n
\ud14c\uc774\ube14\uc5d0 \ub300\ud55c dml \ubcc0\uacbd\ub7c9\uc744 \ud655\uc778\ud558\ub294 \ubdf0\ub85c \ucd5c\uadfc ANALYZE \uc774\ud6c4 10% \uc774\uc0c1 \ubcc0\uacbd\ubd84\uc5d0 \ub300\ud55c \uae30\ub85d\uc744<\/p>\n
\ub0a8\uae34\ub2e4.\u00a0 v$segment_statistics view \ubcd1\ud589\ud558\uc5ec \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ubcc0\uacbd\ub7c9\uc744 \ucd94\uc815\uc744 \ud560\uc218 \uc788\uc74c.<\/p>\n
10%\uc758 \uc624\ucc28\uc640 \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\uc2dc\uae30\uc5d0 \ub530\ub978 \uc624\ucc28\uac00 \ubc1c\uc0dd\ud560\uc218 \uc788\uc73c\uba70, \ub300\ub7b5\uc801\uc778 \ubcc0\ub3d9\uc744 \ucd94\uc815\ud558\ub294 \uc790\ub8cc\ub85c<\/p>\n
\ud65c\uc6a9\uac00\ub2a5\ud568.<\/p>\n
I am having the same exact problem, tables are getting modified and TIMESTAMP column in *_TAB_MODIFICATIONS is not getting updated, however, UPDATES\/DELETES\/INSERTS columns are.
\nThis is 10gR2. STATISTICS_LEVEL is TYPICAL. I have ran many times dbms_stats.FLUSH_DATABASE_MONITORING_INFO but, only UPDATES\/DELETES\/INSERTS change, TIMESTAMP remains the same. Also, I have one table that is >= 10% modified, but GATHER_STATS_JOB is not choosing it for collecting statistics.<\/p>\n
<blockquote><\/blockquote><\/p>\n
DBA_TAB_MODIFICATIONS View Source<\/p>\n
Oracle 11g’s data dictionary defines the DBA_TAB_MODIFICATIONS view using the following source query:<\/p>\n
select u.name, o.name, null, null,
\nm.inserts, m.updates, m.deletes, m.timestamp,
\ndecode(bitand(m.flags,1),1,’YES’,’NO’),
\nm.drop_segments
\nfrom sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
\nwhere o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
\nunion all
\nselect u.name, o.name, o.subname, null,
\nm.inserts, m.updates, m.deletes, m.timestamp,
\ndecode(bitand(m.flags,1),1,’YES’,’NO’),
\nm.drop_segments
\nfrom sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
\nwhere o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
\nunion all
\nselect u.name, o.name, o2.subname, o.subname,
\nm.inserts, m.updates, m.deletes, m.timestamp,
\ndecode(bitand(m.flags,1),1,’YES’,’NO’),
\nm.drop_segments
\nfrom sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
\nsys.user$ u
\nwhere o.obj# = m.obj# and o.owner# = u.user# and
\no.obj# = tsp.obj# and o2.obj# = tsp.pobj#<\/p>\n
;<\/p>\n
*\uc608\uc81c 2<\/p>\n
Automatic stats(default 10g, manual 9i). Examine status<\/b> begin dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); end; select num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name, inserts, updates, deletes, timestamp, truncated , to_char(perc_updates, ‘FM999,999,999,990.00’) perc_updates from ( select a.* , nvl(decode(num_rows, 0, ‘-1’, 100 * tot_updates \/ num_rows), -1) perc_updates from ( select (select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows , (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed , (inserts + updates + deletes) tot_updates , DBA_TAB_MODIFICATIONS.* from sys.DBA_TAB_MODIFICATIONS ) a ) b where perc_updates > 10; Column description of the DBA_TAB_MODIFICATIONS view:<\/p>\n
TABLE_OWNER<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.TABLE_OWNER: “Owner of modified table”<\/p>\n TABLE_NAME<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.TABLE_NAME: “Modified table”<\/p>\n PARTITION_NAME<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.PARTITION_NAME: “Modified partition”<\/p>\n SUBPARTITION_NAME<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.SUBPARTITION_NAME: “Modified subpartition”<\/p>\n INSERTS<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.INSERTS: “Approximate number of rows inserted since last analyze”<\/p>\n UPDATES<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.UPDATES: “Approximate number of rows updated since last analyze”<\/p>\n DELETES<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.DELETES: “Approximate number of rows deleted since last analyze”<\/p>\n TIMESTAMP<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.TIMESTAMP: “Timestamp of last time this row was modified”<\/p>\n TRUNCATED<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.TRUNCATED: “Was this object truncated since the last analyze?”<\/p>\n DROP_SEGMENTS<\/strong><\/p>\n Description of DBA_TAB_MODIFICATIONS.DROP_SEGMENTS: “Number of (sub)partition segment dropped since the last analyze?”<\/p>\n <\/p>\n <\/p>\n <\uae30\ubcf8\ud3b8> 10% \uc774\uc0c1 \ubcc0\uacbd \ub41c \ud14c\uc774\ube14\ub9cc \ud655\uc778\ud558\ub294 \ucffc\ub9ac<\/p>\n select * <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\uc751\uc6a9\ud3b8> 10% \uc774\uc0c1 \ubcc0\uacbd\ub41c \ud14c\uc774\ube14 \ub9cc \ud1b5\uacc4\uc815\ubcf4 \ub728\uace0 \uc2f6\uc744 \ub54c<\/p>\n select ‘exec DBMS_STATS.GATHER_TABLE_STATS (ownname => ”’||owner||”’ , tabname => ”’||table_name||”’, estimate_percent=> 100 , method_opt => ” FOR ALL COLUMNS SIZE ‘|| <\/p>\n <\/p>\n <\/p>\n \ub610\ub2e4\ub978\u00a0\u00a0\ud14c\uc774\ube14 I\/O \ud655\uc778\ud558\ub294 \ucffc\ub9ac \ub85c\u00a0v$segment_statistics view\ub85c \uc870\ud68c\ud558\ub294 \ucffc\ub9ac\uc785\ub2c8\ub2e4.<\/strong><\/span><\/p>\n \uc774 \ucffc\ub9ac\ub294 dba_tab_modifications\u00a0\ud14c\uc774\ube14\uc5d0\uc11c\u00a0DML\uc5d0 \uc758\ud574 \ubcc0\uacbd\ub41c \uac74\uc218\uc640 select table_owner, m.table_name, num_rows,<\/strong><\/p>\n round(num_rows\/10,0) “NUM_ROWS\/10”,<\/strong><\/p>\n inserts+updates+deletes “to_changed”,<\/strong><\/p>\n INSERTS,UPDATES,DELETES,TIMESTAMP,LAST_ANALYZED,<\/strong><\/p>\n round(LAST_ANALYZED-TIMESTAMP,0) GAP<\/strong><\/p>\n from dba_tab_modifications m, dba_tables t<\/strong><\/p>\n where table_owner not in (‘SYS’)<\/strong><\/p>\n and t.table_name=m.table_name<\/strong><\/p>\n and t.owner=m.table_owner<\/strong><\/p>\n and round(num_rows\/10,0)-(inserts+updates+deletes)<0<\/strong><\/p>\n order by LAST_ANALYZED-TIMESTAMP;<\/strong><\/p>\n<\/div>\n V$\ubdf0\ub294 \uc778\uc2a4\ud134\uc2a4 \uae30\ub3d9\ud6c4 \ubd80\ud130 \ub370\uc774\ud130\uac00 \ucd95\uc801\ub418\ub294 \uac12\uc774\uae30 \ub54c\ubb38\uc5d0,\u00a0\uc815\ud655\ud55c \uac12\uc774\ub77c\uae30 \ubcf4\ub2e4\ub294 \ub300\ub7b5\uc801\uc778 \ucc38\uc870\uc6a9 \uc785\ub2c8\ub2e4.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" \ud1b5\uacc4\uc815\ubcf4 \uac31\uc2e0 \uc2dd\ubcc4 alter table emp monitoring; statistics_level\uc774 typical \uc774\uc0c1 \uc124\uc815\uc2dc \uc624\ub77c\ud074\uc744 \ud14c\uc774\ube14\uc5d0 \ubc1c\uc0dd\ud558\ub294 dml\uc744 \ubaa8\ub2c8\ud130\ub9c1\ud55c\ub2e4. \uc218\uc9d1\ub41c \ud14c\uc774\ube14\ubcc4 DML\uc740 *_tab_modifications\ubdf0 \ub97c \ud1b5\ud574 \uc870\ud68c\ud560\uc218 \uc788\uc73c\uba70, insert,update,delete\ub294 \ub9c8\uc9c0\ub9c9 \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\ub41c \uc774\ud6c4\uc758 dml \ubc1c\uc0dd\ub7c9\uc774\ub2e4. \uc624\ub77c\ud074\uc740 \ubaa8\ub2c8\ud130\ub9c1 \ub300\uc0c1 \ud14c\uc774\ube14\uc5d0 10%\uc774\uc0c1 \ubcc0\uacbd\uc774 \ubc1c\uc0dd\ud588\uc744\ub54c \ud574\ub2f9 \ud14c\uc774\ube14\uc744 stale \uc0c1\ud0dc(*_tab_statistics \ubdf0\uc5d0\uc11c stale_stats=yes)\ub85c \ubcc0\uacbd \ud558\uace0, gather_database_stats \ub610\ub294 gather_schema_stats \ud504\ub85c\uc2dc\uc800\ub97c \ud638\ucd9c\ud558\uba74\uc11c option […]<\/p>\n","protected":false},"author":1,"featured_media":2499,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/digital4-300x241.jpg?fit=300%2C241","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2556"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2556"}],"version-history":[{"count":5,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2556\/revisions"}],"predecessor-version":[{"id":2561,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2556\/revisions\/2561"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2499"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2556"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2556"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2556"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nfrom\u00a0\u00a0 (select a.owner,
\na.table_name
\nfrom\u00a0\u00a0 dba_tables a,
\nsys.dba_tab_modifications b
\nwhere\u00a0 a.owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 b.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 a.table_name=b.table_name
\nand\u00a0\u00a0\u00a0 TRUNCATED = ‘NO’
\nand\u00a0\u00a0\u00a0 b.partition_name is null
\nand\u00a0\u00a0\u00a0 ( TRUNC((INSERTS-DELETES+UPDATES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 TRUNC((INSERTS-DELETES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 num_rows is null)
\ngroup by a.OWNER, a.TABLE_NAME
\nunion
\nselect a.table_owner owner,
\na.table_name
\nfrom\u00a0\u00a0 dba_tab_partitions a,
\nsys.dba_tab_modifications b
\nwhere\u00a0 a.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 b.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 a.table_name=b.table_name
\nand\u00a0\u00a0\u00a0 TRUNCATED = ‘NO’
\nand\u00a0\u00a0\u00a0 b.partition_name = a.partition_name
\nand\u00a0\u00a0\u00a0 ( TRUNC((INSERTS-DELETES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 TRUNC((INSERTS-DELETES+UPDATES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 num_rows is null )
\ngroup by a.TABLE_OWNER, a.TABLE_NAME )
\ngroup by owner, table_name
\norder by owner desc , table_name;<\/p>\n
\ncase
\nwhen owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’) then ‘ 1’
\nelse ‘ 1′
\nend ||”’\u00a0\u00a0 , granularity => ”GLOBAL”, cascade => true , degree => 3 ) ; ‘ scripts
\nfrom\u00a0\u00a0 (select a.owner,
\na.table_name
\nfrom\u00a0\u00a0 dba_tables a,
\nsys.dba_tab_modifications b
\nwhere\u00a0 a.owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 b.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 a.table_name=b.table_name
\nand\u00a0\u00a0\u00a0 TRUNCATED = ‘NO’
\nand\u00a0\u00a0\u00a0 b.partition_name is null
\nand\u00a0\u00a0\u00a0 ( TRUNC((INSERTS-DELETES+UPDATES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 TRUNC((INSERTS-DELETES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 num_rows is null)
\ngroup by a.OWNER, a.TABLE_NAME
\nunion
\nselect a.table_owner owner,
\na.table_name
\nfrom\u00a0\u00a0 dba_tab_partitions a,
\nsys.dba_tab_modifications b
\nwhere\u00a0 a.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 b.table_owner\u00a0 not in (‘SYS’,’SYSTEM’,’SCOTT’,’OUTLN’,’MGMT_VIEW’,’FLOWS_FILES’,’MDSYS’,’WMSYS’,’APPQOSSYS’,’APEX_030200′,’OWBSYS_AUDIT’,’DBSNMP’,’OWBSYS’,’ORDDATA’,’ANONYMOUS’,’EXFSYS’,’XDB’,’ORDSYS’,’CTXSYS’,’ORDPLUGINS’,’SYSMAN’,’OLAPSYS’,’SI_INFORMTN_SCHEMA’,’SH’,’HR’,’BI’,’XS$NULL’,’IX’,’MDDATA’,’ORACLE_OCM’,’DIP’,’PM’,’APEX_PUBLIC_USER’,’SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’OE’)
\nand\u00a0\u00a0\u00a0 a.table_name=b.table_name
\nand\u00a0\u00a0\u00a0 TRUNCATED = ‘NO’
\nand\u00a0\u00a0\u00a0 b.partition_name = a.partition_name
\nand\u00a0\u00a0\u00a0 ( TRUNC((INSERTS-DELETES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 TRUNC((INSERTS-DELETES+UPDATES)\/DECODE(a.num_rows, 0, 1, NULL, 1, a.num_rows)*100, 2) > 10
\nor\u00a0\u00a0\u00a0\u00a0 num_rows is null )
\ngroup by a.TABLE_OWNER, a.TABLE_NAME )
\ngroup by owner, table_name
\norder by owner desc , table_name;<\/p>\n
\ndba_tables\uc758\u00a0num_rows\ub97c \ube44\uad50\ud574\u00a010%\u00a0\uc774\uc0c1 \ubcc0\uacbd\ub41c \ud14c\uc774\ube14 \uc870\ud68c \ucffc\ub9ac\ub85c \ud655\uc815\uce58\ub77c\uace0 \ubcfc \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n