网站制作网站维护,百度文库个人登录入口,wordpress网站缓存安装,虚拟主机做网站查看数据库历史增长情况
此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。
--不含undo和temp with tmp as
(select rtime, sum(tablespace_usedsize_kb) tablespace_usedsize_kb, sum(tablespace_size_kb) tablespace_size_kb from (select rtime, e.ta… 查看数据库历史增长情况
此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。
--不含undo和temp with tmp as
(select rtime, sum(tablespace_usedsize_kb) tablespace_usedsize_kb, sum(tablespace_size_kb) tablespace_size_kb from (select rtime, e.tablespace_id, (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb, (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb from dba_hist_tbspc_space_usage e, dba_tablespaces f, v$tablespace g where e.tablespace_id g.TS# and f.tablespace_name g.NAME and f.contents not in (TEMPORARY,UNDO)) group by rtime) select tmp.rtime, tablespace_usedsize_kb, tablespace_size_kb, (tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB from tmp, (select max(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2 where t2.rtime tmp.rtime; --含undo和temp with tmp as
(select min(rtime) rtime, sum(tablespace_usedsize_kb) tablespace_usedsize_kb, sum(tablespace_size_kb) tablespace_size_kb from (select rtime, e.tablespace_id, (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb, (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb from dba_hist_tbspc_space_usage e, dba_tablespaces f, v$tablespace g where e.tablespace_id g.TS# and f.tablespace_name g.NAME) group by rtime) select tmp.rtime, tablespace_usedsize_kb, tablespace_size_kb, (tablespace_usedsize_kb - LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB from tmp, (select min(rtime) rtime from tmp group by substr(rtime, 1, 10)) t2 where t2.rtime tmp.rtime ##############################################################
SQL脚本:列出相关段对象在 快照时间内的使用空间的历史变化信息 column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999 select obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME,RRRR-MON-DD) start_day, sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a, dba_hist_snapshot sn, dba_objects obj
where sn.snap_id a.snap_id
and obj.object_id a.obj#
and obj.owner not in (SYS,SYSTEM)
and end_interval_time between to_timestamp(17-FEB-2014,DD-MON-RRRR) and to_timestamp(25-FEB-2014,DD-MON-RRRR)
group by obj.owner, obj.object_name, to_char(sn.BEGIN_INTERVAL_TIME,RRRR-MON-DD)
order by obj.owner, obj.object_name ; select S.SNAP_ID, T.NAME, S.RTIME, (TABLESPACE_USEDSIZE - LAG(TABLESPACE_USEDSIZE, 1, NULL) OVER(ORDER BY S.SNAP_ID)) AS DIFF from V$TABLESPACE T, DBA_HIST_TBSPC_SPACE_USAGE S where T.TS# S.TABLESPACE_ID and T.name TS_TEST01 and RTIME 02/18/20114 23:00:44 ------最近7天数据库增长情况
select sum(space_used_total)/1024/1024/1024 last 7 days db increase - G
from dba_hist_seg_stat s, dba_hist_seg_stat_obj o, dba_hist_snapshot sn
where s.obj# o.obj#
and sn.snap_id s.snap_id
and begin_interval_time sysdate-8
order by begin_interval_time
/
