数据库的空间管理

比眉伴天荒 2022-07-14 16:15 166阅读 0赞

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------