- 浏览: 4368851 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
这几天数据库上的用户表空间疯涨,一天的增长量达到了20~30GB,平时就4~5GB的增长量。问了开发商,他们也说不出个所以然。PS:那些鸟开发商,现在我已经对他们绝望了,什么都不知道。情急之下直接对该表空间进行了扩容,问题总算解决了。事后总想弄个明白,想知道是那张表导致的表空间增长如此之快,以免下次出现同样的问题又是不知所措。
google了一番后发现DBA_HIST_SEG_STAT可以看出对象的使用趋势
构造如下SQL查询出每个时间段内数据库对象的增长量,其中DB_BLOCK_CHANGES_DELTA为块个数
select c.SNAP_ID,to_char(c.END_INTERVAL_TIME,'yyyy-mm-dd') SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
order by DB_BLOCK_CHANGES_DELTA
下面的SQL查询出某个时间段内,数据库对象的变化大小,单位以换算成GB
select a.OBJECT_NAME,
to_char(c.END_INTERVAL_TIME,'yyyy-mm-dd')
SNAP_TIME, sum(b.DB_BLOCK_CHANGES_DELTA)/1024/1024 GB from dba_objects a,(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
group by a.OBJECT_NAME,c.END_INTERVAL_TIME
order by GB
简单介绍下DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT视图内记录了数据库实例启动以来段的变化信息,该视图的数据来自于v$segstat
SNAP_ID
|
NUMBER
|
Unique snapshot ID | |
DBID
|
NUMBER
|
Database ID for the snapshot | |
INSTANCE_NUMBER
|
NUMBER
|
Instance number for the snapshot | |
TS#
|
NUMBER
|
Tablespace number | |
OBJ#
|
NUMBER
|
Dictionary object number | |
DATAOBJ#
|
NUMBER
|
Data object number | |
LOGICAL_READS_TOTAL
|
NUMBER
|
Cumulative value for logical reads | |
LOGICAL_READS_DELTA
|
NUMBER
|
Delta value for logical reads | |
BUFFER_BUSY_WAITS_TOTAL
|
NUMBER
|
Cumulative value for buffer busy waits | |
BUFFER_BUSY_WAITS_DELTA
|
NUMBER
|
Delta value for buffer busy waits | |
DB_BLOCK_CHANGES_TOTAL
|
NUMBER
|
Cumulative value for db block changes | |
DB_BLOCK_CHANGES_DELTA
|
NUMBER
|
Delta value for db block changes | |
PHYSICAL_READS_TOTAL
|
NUMBER
|
Cumulative value for physical reads | |
PHYSICAL_READS_DELTA
|
NUMBER
|
Delta value for physical reads | |
PHYSICAL_WRITES_TOTAL
|
NUMBER
|
Cumulative value for physical writes | |
PHYSICAL_WRITES_DELTA
|
NUMBER
|
Delta value for physical writes | |
PHYSICAL_READS_DIRECT_TOTAL
|
NUMBER
|
Cumulative value for physical reads direct | |
PHYSICAL_READS_DIRECT_DELTA
|
NUMBER
|
Delta value for physical reads direct | |
PHYSICAL_WRITES_DIRECT_TOTAL
|
NUMBER
|
Cumulative value for physical writes direct | |
PHYSICAL_WRITES_DIRECT_DELTA
|
NUMBER
|
Delta value for physical writes direct | |
ITL_WAITS_TOTAL
|
NUMBER
|
Cumulative value for ITL waits | |
ITL_WAITS_DELTA
|
NUMBER
|
Delta value for ITL waits | |
ROW_LOCK_WAITS_TOTAL
|
NUMBER
|
Cumulative value for row lock waits | |
ROW_LOCK_WAITS_DELTA
|
NUMBER
|
Delta value for row lock waits | |
GC_CR_BLOCKS_SERVED_TOTAL
|
NUMBER
|
Cumulative value for global cache CR blocks served | |
GC_CR_BLOCKS_SERVED_DELTA
|
NUMBER
|
Delta value for global cache CR blocks served | |
GC_CU_BLOCKS_SERVED_TOTAL
|
NUMBER
|
Cumulative value for global cache current blocks served | |
GC_CU_BLOCKS_SERVED_DELTA
|
NUMBER
|
Delta value for global cache current blocks served | |
GC_BUFFER_BUSY_TOTAL
|
NUMBER
|
Cumulative value for global cache buffer busy | |
GC_BUFFER_BUSY_DELTA
|
NUMBER
|
Delta value for global cache buffer busy | |
GC_CR_BLOCKS_RECEIVED_TOTAL
|
NUMBER
|
Cumulative value for global cache CR blocks received | |
GC_CR_BLOCKS_RECEIVED_DELTA
|
NUMBER
|
Delta value for global cache CR blocks received | |
GC_CU_BLOCKS_RECEIVED_TOTAL
|
NUMBER
|
Cumulative value for global cache current blocks received | |
GC_CU_BLOCKS_RECEIVED_DELTA
|
NUMBER
|
Delta value for global cache current blocks received | |
SPACE_USED_TOTAL
|
NUMBER
|
Cumulative value for space used | |
SPACE_USED_DELTA
|
NUMBER
|
Delta value for space used | |
SPACE_ALLOCATED_TOTAL
|
NUMBER
|
Cumulative value for space allocated | |
SPACE_ALLOCATED_DELTA
|
NUMBER
|
Delta value for space allocated | |
TABLE_SCANS_TOTAL
|
NUMBER
|
Cumulative value for table scans | |
TABLE_SCANS_DELTA
|
NUMBER
|
Delta value for table scans |
顺便说一句DBA_HIST_TBSPC_SPACE_USAGE视图可以监控表空间的使用趋势
感谢杨帅军的指点
参考至:http://oracle.erkansaka.org/2007/09/how-to-determine-tables-with-highest.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3165.htm
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
-
Oracle 11g、12c大量错误登陆尝试带来的数据库异常
2018-07-16 09:21 1473APPLIES TO: Oracle Database - ... -
如何定位那些SQL产生了大量的redo日志
2018-05-15 14:38 1695在ORACLE数据库的管理、 ... -
When Memory_target Is Set and Swap Size Is Not Big (Doc ID 2356025.1)
2018-03-16 18:13 1127Kkjcre1p: unable to sp ... -
Transparent Hugepage is not getting disabled (Doc ID 2279458.1)
2018-03-16 18:10 779Transparent Hugepage is ... -
Troubleshooting: "log file sync" Waits (文档 ID 1376916.1)
2017-03-09 14:32 1297What is a 'log file sync' wai ... -
log file sync总结
2017-03-09 14:36 3132log file sync等待时间发生在redo log从 ... -
Oracle Log File Sync Wait Event
2017-03-08 18:46 1119The Oracle “log file sync” wai ... -
Tuning ‘log file sync’ Event Waits
2017-03-08 18:41 808Tuning ‘log file sync’ Event ... -
Diagnosing buffer busy waits with the ash_wait_chains.sql script (v0.2)
2017-03-08 16:56 782Diagnosing buffer busy waits w ... -
Advanced Oracle Troubleshooting Guide – Part 11: Complex Wait Chain Signature An
2017-03-08 16:05 1014Here’s a treat for the hard-co ... -
Oracle 10046 SQL TRACE
2017-03-08 15:19 892为什么我们要使用10046 trace? 10046 ... -
Automatic Storage Management
2016-11-03 15:33 806SYSASM Role When Automatic ... -
ASM FAQ
2016-11-03 15:29 672ASM FAQ Oracle Automatic ... -
Oracle ALTER PROFILE语法
2016-10-10 11:36 3337ALTER PROFILE Purpose Use th ... -
Oracle FGA审计
2016-09-20 09:42 1313大家对trigger可能比较熟悉,但Oracle还有一个叫 ... -
Secret.txt
2016-09-14 17:20 0考试登录账号:YAMAC0043865 Chen1988协会网 ... -
Oracle Data Pump Internals
2016-09-13 16:38 724IntroductionOracle Data Pump w ... -
UDEV SCSI Rules Configuration for ASM in Oracle Linux 5, 6 and 7
2016-09-12 16:32 797UDEV SCSI Rules Configuration ... -
Cannot Allocate New Log
2016-02-21 12:28 5364故障报错 Thread 1 cannot allocat ... -
Oracle flashback dropped tablespace(原创)
2015-11-15 22:21 1945Oracle官方并不推荐在数据库物理结构发生改变的情况下进行 ...
相关推荐
oracle变化数据捕获,学习和理解如何在ORACLE数据库跟踪数据变化的教程
使用oracle数据库时修改表结构,创建表空间等等
如何在Oracle数据表中随机抽取部分记录的方法
oracle创建分区表
照着做绝对能用的教程,本人亲自测试过,欢迎下载
ORACLE 数据库中如何统计表记录数的过程说明
oracle查看表历史记录-Oracle查看表操作历史记录并恢复.pdf
有做数据库巡检的同学,资料来啦,填空就可以喽
ORACLE表生成MYSQL建表DDL
数据库备份恢复时,获取oracle表空间脚本,创建用户等
[Oracle]在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引 [Oracle]如何在亿级记录表中创建索引
SqlServer表结构转oracle表结构,注释也会一同带上,界面操作可以对表可以单个选择多选查询等操作,C#开发源码见附件
oracle表分区详解
Oracle数据库监听工具
oracle查询表碎片语句
Oracle数据库删除表中重复记录的方法三则.txt
oracle锁表查询oracle锁表查询oracle锁表查询oracle锁表查询
利用DBMS_SPACE包对Oracle 表碎片进行监控与清理,
根据excel快速生成oracle的表结构,内有可运行的代码,支持自由调配
oracle表空间信息,查看Oracle表空间空闲与已用大小