Oracle 11g中的架构增长

Question

在Oracle中获得模式增长的最佳方法是什么？我尝试使用了许多dba_hist_表，但似乎没有一个表能让我了解特定时间(7天)内模式的大小和增长情况。这些表似乎包含表空间增长信息，而不是@ schema级别。

有人能帮帮忙吗？

尝试了下面的表格，

dba_hist_tablespace_stat、dba_hist_seg_stat、dba_hist_seg_stat_obj、dba_hist_snapshot等。

Answer 1

检查此查询：

SELECT b.tsname tablspc_name ,
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2) avg_incr_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM dba_hist_tbspc_space_usage tsu ,
      dba_hist_tablespace_stat ts ,
      dba_hist_snapshot sp,
      dba_tablespaces dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
GROUP BY b.tsname
ORDER BY b.tsname;

并按所有者分组：

SELECT  ds.owner as owner, 
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2) avg_incr_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM dba_hist_tbspc_space_usage tsu ,
      dba_hist_tablespace_stat ts ,
      dba_hist_snapshot sp,
      dba_tablespaces dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
JOIN dba_segments ds on ds.tablespace_name = b.tsname
GROUP BY ds.owner
ORDER BY ds.owner;