数据库的空间管理

author：skate
time：2010/10/09

---

 

 

 

数据库的空间管理

 

 

在当下数据爆炸时代，数据的增长是惊人的，所以数据的存储空间的管理尤为重要，我们要了解数据的增长趋势，这对于我们管理空间是非常有用的，以oracle为例，看看如何来查看oracle的空间变化趋势。

 

oracle数据库存储空间需要管理的部分：

 

1. 各个表空间（系统表空间，用户表空间），也就是各种数据文件
2. archivelog的合理存储也是非常重要的，如果归档满会引起数据库hang住

 

 

思路：通过记录各个数据文件不同时刻值来跟踪数据库的空间变化

 

create or replace procedure pro_dba_spacemanage is
  v_num        number(10);
  v_rownum     number(10);

/*******************************************************
author：skate
time  ：2010/10/09
功能：监控数据库的各个表空间和归档日志的空间使用情况，便于空间的维护
说明：需要创建两个临时表dba_spacemanage_t,dba_spacemanage_t1用于存放最近一次监控记录
      和当前监控记录；表dba_spacemanage监控历史信息. 序列SEQ_DBA_SPACEMANAGE做主键
      可以通过os的cron或是oracle定时任务来定期收集
********************************************************/
begin

  execute immediate 'truncate table dba_spacemanage_t';

  --确定空间使用表是否有数据，如果没有数据要初始化
  select count(1) into v_num from dba_spacemanage;

  --初始化数据
  if v_num = 0 then
    insert into dba_spacemanage_t
      select * from dba_spacemanage_t1;

  end if;

  --确定有多少表空间，第一个1是因为我们还要监控archvielog，第二个1是用于后面返回行数方便
  select count(1) + 1 + 1 into v_rownum from v$tablespace;

  execute immediate 'truncate table dba_spacemanage_t1';

  ---表dba_spacemanage_t用于记录最近一次监控的记录
  insert into dba_spacemanage_t
    select *
      from (select * from dba_spacemanage sp order by sp.type desc)
     where rownum < v_rownum;

  ---表dba_spacemanage_t1用于记录当前监控的记录
  insert into dba_spacemanage_t1
    SELECT 1,
           d.tablespace_name "Name",
           sysdate,
           TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,'99999999.999') "used",
           TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
           to_char(nvl(a.bytes, 0)),
           '0',
           TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),'990.00') "Used %",
           (select nvl(max(type), 0) + 1 from dba_spacemanage)
      FROM sys.dba_tablespaces d,
           (select tablespace_name, sum(bytes) bytes
              from dba_data_files
             group by tablespace_name) a,
           (select tablespace_name, sum(bytes) bytes
              from dba_free_space
             group by tablespace_name) f
     WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT (d.extent_management like 'LOCAL' AND
            d.contents like 'TEMPORARY')
    UNION ALL
    SELECT  1,
           d.tablespace_name "Name",
           sysdate,
           TO_CHAR(NVL(a.bytes - NVL(t.bytes, 0), 0) / 1024 / 1024,'99999999.999') "used",
           TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999990.900') "Size (M)",
           to_char(nvl(a.bytes, 0)),
           '0',
           TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %",
           (select nvl(max(type), 0) + 1 from dba_spacemanage)
      FROM sys.dba_tablespaces d,
           (select tablespace_name, sum(bytes) bytes
              from dba_temp_files
             group by tablespace_name) a,
           (select tablespace_name, sum(bytes_cached) bytes
              from v$temp_extent_pool
             group by tablespace_name) t
     WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = t.tablespace_name(+)
       AND d.extent_management like 'LOCAL'
       AND d.contents like 'TEMPORARY'
    union all
    SELECT  1,
           'archivelog' "Name",
           sysdate "Date",
           to_char(a.cur_size) "used(M)",
           '0' "total(M)",
           to_char(a.cur_size - pri_size) "space_diff(M)",
           to_char((a.cur_size - pri_size) / pri_size) "space_rate",
           '0' ,
           (select nvl(max(type), 0) + 1 from dba_spacemanage) "type"
      FROM (SELECT sum(v.BLOCKS * v.BLOCK_SIZE / 1024 / 1024) cur_size
              FROM v$archived_log v
             WHERE v.DEST_ID = 1
               and trunc(v.FIRST_TIME, 'dd') = trunc(sysdate - 1, 'dd')) a,
           (SELECT sum(v1.BLOCKS * v1.BLOCK_SIZE / 1024 / 1024) pri_size
              FROM v$archived_log v1
             WHERE v1.DEST_ID = 1
               and trunc(v1.FIRST_TIME, 'dd') = trunc(sysdate - 2, 'dd')) b;

---通过比较表dba_spacemanage_t和dba_spacemanage_t1来计算表dba_spacemanage的内容
  insert into dba_spacemanage
    select seq_dba_spacemanage.nextval,
           t.tbs_name,
           t1.exec_date,
           t1.space_used,
           t1.space_total,
           t1.space_used - t.space_used,
          to_char( (t1.space_used - t.space_used) / t.space_used,'9999999.999'),
           t1.space_usedrate,
           t1.type
      from dba_spacemanage_t t, dba_spacemanage_t1 t1
     where t.tbs_name = t1.tbs_name;

end pro_dba_spacemanage;

 

###创建空间管理历史信息表

create table DBA_SPACEMANAGE
(
  ID             NUMBER(20),
  TBS_NAME       VARCHAR2(50),
  EXEC_DATE      DATE,
  SPACE_USED     VARCHAR2(50),
  SPACE_TOTAL    VARCHAR2(50),
  SPACE_DIFF     VARCHAR2(50),
  SPACE_RATE     VARCHAR2(50),
  SPACE_USEDRATE VARCHAR2(50),
  TYPE           NUMBER(20)
)
tablespace YYPART;
-- Add comments to the columns
comment on column DBA_SPACEMANAGE.ID
  is '主键';
comment on column DBA_SPACEMANAGE.TBS_NAME
  is '表空间的名字或归档日志';
comment on column DBA_SPACEMANAGE.EXEC_DATE
  is '执行监控查询的时间';
comment on column DBA_SPACEMANAGE.SPACE_USED
  is '当前使用空间的大小（M）';
comment on column DBA_SPACEMANAGE.SPACE_TOTAL
  is '当前表空间的总大小（M）';
comment on column DBA_SPACEMANAGE.SPACE_DIFF
  is '每天增长的空间差值大小（M）';
comment on column DBA_SPACEMANAGE.SPACE_RATE
  is '空间增长率';
comment on column DBA_SPACEMANAGE.SPACE_USEDRATE
  is '空间当前使用率';
comment on column DBA_SPACEMANAGE.TYPE
  is '区别每次查询的次数';

 

####创建两个临时表

create table DBA_SPACEMANAGE_T as select * from DBA_SPACEMANAGE;
create table DBA_SPACEMANAGE_T1 as select * from DBA_SPACEMANAGE;

-- Create sequence
create sequence SEQ_DBA_SPACEMANAGE
minvalue 1
maxvalue 999999999999999999999
start with 250
increment by 1
cache 50;

 

 

######在oracle里创建定时任务，当然也可以在os下用cron创建定时任务

begin
  sys.dbms_job.submit(job => :job,
                      what => 'pro_dba_spacemanage;',
                      next_date => to_date('10-10-2010 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(SYSDATE + 1) + 12/24');
  commit;
end;
/

###到此为止，这个procedure会每天中午12点收集一次信息，用如下sql可以知道每次查询空间的使用情况和变化情况

 

     select 'db space total :',
            trunc(t.exec_date, 'dd') "execdate",
            sum(t.space_used) "space_used(M)",
            sum(t.space_diff) "space_diff(M)",
            sum(t.space_rate) "growth rate"
       from dba_spacemanage t
       where t.tbs_name not in ('archivelog','TEMP')
      group by t.type, trunc(t.exec_date, 'dd')

 

可以把这个查询结果放到excel里，制作成图标，就可以更直观看到空间的变化趋势。

 

 

 

 

-----end------