Check_oracle_health之表空间使用及处理

Check_oracle_health之表空间使用及处理

---

1  查看表空间情况
--查看所有表空间大小

select tablespace_name, sum(bytes) / 1024 / 1024 total_M
  from dba_data_files
 group by tablespace_name;

--已经使用的表空间大小
select tablespace_name, sum(bytes) / 1024 / 1024 used_M
  from dba_free_space
 group by tablespace_name;

--所以使用空间可以这样计算

select a.tablespace_name, total_M, free_M, total_M - free_M used_M
  from (select tablespace_name, sum(bytes) / 1024 / 1024 total_M
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 free_M
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;


2 脚本内命令
SELECT a.tablespace_name   "Tablespace",
       b.status            "Status",
       b.contents          "Type",
       b.extent_management "Extent Mgmt",
       a.bytes             bytes,
       a.maxbytes          bytes_max,
       c.bytes_free        bytes_free
  FROM (
        -- belegter und maximal verfuegbarer platz pro datafile
        -- nach tablespacenamen zusammengefasst
        -- => bytes
        -- => maxbytes
        SELECT a.tablespace_name,
                SUM(a.bytes) / 1024 / 1024 bytes,
                SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes)) / 1024 / 1024 / 1024 maxbytes
          FROM dba_data_files a
         GROUP BY tablespace_name) a,
       sys.dba_tablespaces b,
       (
        -- freier platz pro tablespace
        -- => bytes_free
        SELECT a.tablespace_name, SUM(a.bytes) bytes_free
          FROM dba_free_space a
         GROUP BY tablespace_name) c
 WHERE a.tablespace_name = c.tablespace_name(+)
   AND a.tablespace_name = b.tablespace_name
UNION ALL
SELECT a.tablespace_name "Tablespace",
       b.status "Status",
       b.contents "Type",
       b.extent_management "Extent Mgmt",
       sum(a.bytes_free + a.bytes_used) bytes, -- allocated
       SUM(DECODE(d.autoextensible, 'YES', d.maxbytes, 'NO', d.bytes)) bytes_max,
       SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, 0)) bytes_free
  FROM sys.v_$TEMP_SPACE_HEADER a,
       sys.dba_tablespaces      b,
       sys.v_$Temp_extent_pool  c,
       dba_temp_files           d
 WHERE c.file_id(+) = a.file_id
   and c.tablespace_name(+) = a.tablespace_name
   and d.file_id = a.file_id
   and d.tablespace_name = a.tablespace_name
   and b.tablespace_name = a.tablespace_name
 GROUP BY a.tablespace_name,
          b.status,
          b.contents,
          b.extent_management,
          d.maxbytes
 ORDER BY 1;


处理办法：
方法1：为表空间增加数据文件：

alter tablespace system add datafile '/app/oracle/oradata/testdb/system02.dbf' size 20M;

方法2：是增加表空间原有数据文件尺寸：

alter database datafile '/app/oracle/oradata/testdb/system02.dbf' resize 200M;


创建表空间：
CREATE TABLESPACE 01 DATAFILE
  '/oradata/grp/01_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,
  '/oradata/grp/01_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,
  '/oradata/grp/01_03.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,
  '/oradata/grp/01_04.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,
  '/oradata/grp/01_05.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,
  '/oradata/grp/01_06.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

减小表空间大小：
alter database datafile '/app/oracle/oradata/testdb/system02.dbf' resize 20M;


脚本check_oracle_health 计算表空间的方式是：
如果配置了auoextend on，就会采用maxbytes作为分母进行计算。

SELECT a.tablespace_name,
       SUM(a.bytes)/1024/1024/1024 bytes,
       SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes))/1024/1024/1024 maxbytes
  FROM dba_data_files a
 GROUP BY tablespace_name


      if ($self->{bytes_max} == 0) { 
        $self->{percent_used} =
            ($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100;
        $self->{real_bytes_max} = $self->{bytes};
        $self->{real_bytes_free} = $self->{bytes_free};
      } elsif ($self->{bytes_max} > $self->{bytes}) {
        $self->{percent_used} =
            ($self->{bytes} - $self->{bytes_free}) / $self->{bytes_max} * 100;
        $self->{real_bytes_max} = $self->{bytes_max};    
        $self->{real_bytes_free} = $self->{bytes_free} + ($self->{bytes_max} - $self->{bytes});
      } else {
        # alter tablespace USERS add datafile 'users02.dbf'
        #     size 5M autoextend on next 200K maxsize 6M;
        # bytes = 5M, maxbytes = 6M
        # ..... data arriving...until ORA-01652: unable to extend temp segment
        # bytes = 6M, maxbytes = 6M
              # alter database datafile 5 resize 8M;
        # bytes = 8M, maxbytes = 6M
        $self->{percent_used} =
            ($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100;
        $self->{real_bytes_max} = $self->{bytes};
        $self->{real_bytes_free} = $self->{bytes_free};
      }
    }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1354849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1354849/