Ai
1 Star 1 Fork 0

冯际成/Oracle_SQL_Scripts

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
dba_table_modification.sql 2.01 KB
一键复制 编辑 原始数据 按行查看 历史
Abdul mohammed 提交于 2016-02-06 08:58 +08:00 . Fri Feb 5 18:58:16 CST 2016
prompt run exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO (if want up to date info)
prompt info is held in memory and flushed to dba_tab_modifications at certain interval
prompt info is delete when stats are gathered for that segment
prompt look at header of the file for more info
prompt
/*
This information is initially held in memory and only later pushed into
DBA_TAB_MODIFICATIONS and so you may not see the latest information.
Under oracle 9 this information is flushed down every 15 minutes I believe,
under 10.1 it is 3 hours and under 10.2 onwards the information is only flushed
down when stats are gathered against the segment OR you manually flush the
information down to the database.
When statistics are gathered on a segment, any corresponding rows in D
BA_TAB_MODIFOCATIONS is deleted, not updated to zeros, and is recreated only when relevent
inserts,updates, deletes or truncates occur on the segment.
*/
select table_name,PARTITION_NAME,inserts,updates,deletes,truncated,timestamp
from dba_tab_modifications
where table_owner=upper('&table_owner') and table_name= upper('&table_name');
/*
-- how to use it to get a very fast count of rows in a VERY large table
select dbta.owner||'.'||dbta.table_name tab_name
,dbta.num_rows anlyzd_rows
,to_char(dbta.last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_anlzd
,nvl(dbta.num_rows,0)+nvl(dtm.inserts,0)
-nvl(dtm.deletes,0) tot_rows
,nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs
,(nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0))
/greatest(nvl(dbta.num_rows,0),1) pct_c
,dtm.truncated trn
from dba_tables dbta
-- replace below with all_tab_modifications if you need
left outer join sys.dba_tab_modifications dtm
on dbta.owner = dtm.table_owner
and dbta.table_name = dtm.table_name
and dtm.partition_name is null
where dbta.table_name ='&table_name'
and dbta.owner ='&table_owner';
*/
Loading...
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
1
https://gitee.com/sapall/Oracle_SQL_Scripts.git
git@gitee.com:sapall/Oracle_SQL_Scripts.git
sapall
Oracle_SQL_Scripts
Oracle_SQL_Scripts
master

搜索帮助