如何计算或跟踪每月的数据库增长？

Question

使用甲骨文如何每月跟踪数据库的增长？下面是用于使用sql server存储信息的查询之外的查询。有办法用甲骨文复制同样的东西吗？

SELECT 
  t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
sys.tables t

Answer 1

下面的查询使用DBA_HIST_SEG_STAT并基于awr快照及其优先级给出每个快照的数据库增长。您可以将其更改为每月的输出。

    set serveroutput on     
    execute dbms_output.enable(buffer_size => NULL);**


    Declare
          v_BaselineSize  number(20);
          v_CurrentSize   number(20);
          v_TotalGrowth   number(20);
          v_Space     number(20);
          cursor usageHist is
                  select a.snap_id,
                  SNAP_TIME,
                  sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
              from
                  (select SNAP_ID,
                      sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
                  from DBA_HIST_SEG_STAT
                  group by SNAP_ID
                  having sum(SPACE_ALLOCATED_TOTAL) <> 0
                  order by 1 ) a,
                  (select distinct SNAP_ID,
                      to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
                  from DBA_HIST_SNAPSHOT) b
              where a.snap_id=b.snap_id;
      Begin
          select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
          select sum(bytes) into v_CurrentSize from dba_segments;
          v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

          dbms_output.put_line('SNAP_TIME           Database Size(MB)');

          for row in usageHist loop
                  v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
              dbms_output.put_line(row.SNAP_TIME || '           ' || to_char(v_Space) );
          end loop;
      end;
    /

Answer 2

我想SYS.DBA_DATA_FILES表就是你要找的。

我应该补充一点，您可能希望为此使用IDE。蟾蜍有一个很好的显示器，甲骨文的聚光灯也是如此。

即使是Oracle SQL Developer (免费的)也有一个监视工具。

http://nonfunctionaltestingtools.blogspot.com/2012/12/using-sql-developer-to-create-and-view.html转到View -> DBA并创建会话，然后转到Storage表空间，信息将显示在主窗口中。

或者转到View -> DA并创建一个会话，然后转到存储->数据文件，它也会显示详细信息。

Answer 3

执行此操作的一种快速而肮脏的方法是根据数据库文件中的creation_date列进行检查和分组。请查看以下查询：

col growth_gb format 999G999D99
col month     format a20

select to_char(creation_time, 'YYYY MM') month, sum(bytes)/1024/1024/1024 growth_gb
from  v$datafile
where creation_time > SYSDATE - 720
group by to_char(creation_time, 'YYYY MM')
order by 1 desc