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