Scripts: Monitor Tablespace space growth

Posted on by admin

这个脚本可以监控tablespace 空间增长趋势,并且邮件通知到相关domain– 根据TOAD SPACE MANAGEMENT 修改

创建schema,tablespace,table:

create tablespace toad '+ARCH/data/tbs_tst01_00.dbf' size 50G autoextend off;

create user toad identified by toad account unlock;

create user TOAD
  default tablespace toad
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on toad;
-- Grant/Revoke object privileges 
grant select on sys.DBA_DATA_FILES to TOAD;
grant select on sys.DBA_FREE_SPACE to TOAD;
grant select on sys.DBA_TABLESPACES to TOAD;
grant select on sys.V_$FILESTAT to TOAD;
-- Grant/Revoke role privileges 
grant connect to TOAD;
grant resource to TOAD;
-- Grant/Revoke system privileges 
grant create any synonym to TOAD;
grant create public synonym to TOAD;
grant create session to TOAD;
grant select any dictionary to TOAD;
grant select any table to TOAD;
grant unlimited tablespace to TOAD;




-- Create table
create table TOAD_TABLESPACES
(
  tablespace_name VARCHAR2(30) not null,
  mon_date        DATE not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_TABLESPACES
  add constraint TOAD_TABLESPACES_PK primary key (TABLESPACE_NAME, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_TABLESPACES to PUBLIC;


-- Create table
create table TOAD_DATA_FILES
(
  file_id         NUMBER not null,
  mon_date        DATE not null,
  tablespace_name VARCHAR2(30) not null,
  file_name       VARCHAR2(257) not null,
  bytes           NUMBER
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_FK foreign key (TABLESPACE_NAME, MON_DATE)
  references TOAD_TABLESPACES (TABLESPACE_NAME, MON_DATE) on delete cascade;
-- Create/Recreate indexes 
create unique index TOAD_DATA_FILES_UK on TOAD_DATA_FILES (TABLESPACE_NAME, FILE_NAME, MON_DATE)
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_DATA_FILES to PUBLIC;


-- Create table
create table TOAD_FILESTAT
(
  file_id   NUMBER not null,
  mon_date  DATE not null,
  phyrds    CHAR(18) not null,
  phywrts   CHAR(18) not null,
  phyblkrd  CHAR(18) not null,
  phyblkwrt CHAR(18) not null,
  readtim   CHAR(18) not null,
  writetim  CHAR(18) not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FILESTAT to PUBLIC;

-- Create table
create table TOAD_FREE_SPACE
(
  file_id  NUMBER not null,
  mon_date DATE not null,
  bytes    NUMBER not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FREE_SPACE to PUBLIC;


-- Create table
create table TOAD_REF
(
  name  VARCHAR2(30),
  value NUMBER
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_REF to PUBLIC;

创建PACKAGE BODY DBMS_JOB：

CREATE OR REPLACE PACKAGE TOAD_SPACEMAN IS
  PROCEDURE CAPTURE; 
  PROCEDURE RESET; 
END; 



CREATE OR REPLACE PACKAGE BODY TOAD_SPACEMAN IS
PROCEDURE RESET AS 
BEGIN 
  /* DELETE DATA FROM ALL TABLES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES; 
  COMMIT; 

  /* LOAD REAL DATA FOR TODAY */ 
  TOAD.TOAD_SPACEMAN.CAPTURE; 

  /* LOAD DUMMY DATA FOR 60 DAYS */ 
  FOR I IN 1..60 LOOP 
    INSERT INTO TOAD.TOAD_TABLESPACES 
    SELECT TABLESPACE_NAME, TRUNC(MON_DATE-I) 
    FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_DATA_FILES 
    SELECT FILE_ID, TRUNC(MON_DATE-I), TABLESPACE_NAME, FILE_NAME, ROUND(BYTES*(1-I/100)) 
    FROM TOAD.TOAD_DATA_FILES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FREE_SPACE 
    SELECT FILE_ID, TRUNC(MON_DATE-I), ROUND(BYTES*(1+I/100)) 
    FROM TOAD.TOAD_FREE_SPACE WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FILESTAT 
    SELECT FILE_ID, TRUNC(MON_DATE-I), PHYRDS, PHYWRTS, 
    PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM TOAD.TOAD_FILESTAT WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

  END LOOP; 

END RESET; 

PROCEDURE CAPTURE AS 

  V_MON_DATE    DATE    := NULL; 
  V_ROW_SIZE    INTEGER := 100000; 
  NumDaysToRemove NUMBER; 
  CURSOR History_Cur IS 
    SELECT Value 
      FROM TOAD.TOAD_REF 
     WHERE Name = 'Space Manager History Limit'; 

BEGIN 
  /* SAVE TODAYS DATE */ 
  V_MON_DATE := TRUNC(SYSDATE); 
  /* RETRIEVE THE NUMBER OF DAYS TO REMOVE */ 
  OPEN History_Cur; 
  FETCH History_Cur INTO NumDaysToRemove; 

  /* DELETE DATA OLDER THAN USER SPECIFIED DAYS */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE < V_MON_DATE - NumDaysToRemove; 
  COMMIT; 

  /* DELETE PRE-EXISTING DATA FOR CURRENT DAY */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = V_MON_DATE; 
  COMMIT; 
  /* DELETE DATA FOR NONEXISTENT TABLESPACES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES X 
  WHERE NOT EXISTS (SELECT 1 FROM DBA_TABLESPACES D 
  WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME); 
  COMMIT; 

  --/* DELETE DATA FOR NONEXISTENT DATA FILES */ 
  --DELETE FROM TOAD_DATA_FILES X 
  -- WHERE NOT EXISTS (SELECT 1 FROM DBA_DATA_FILES D 
  --                    WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME 
  --                      AND X.FILE_NAME       = D.FILE_NAME); 
  --COMMIT; 

  /* INSERT NEW TABLESPACES FOUND */ 
  INSERT INTO TOAD.TOAD_TABLESPACES 
  SELECT TABLESPACE_NAME, V_MON_DATE 
    FROM DBA_TABLESPACES; 
  COMMIT; 

  /* INSERT NEW DATA FILES FOUND */ 
  INSERT INTO TOAD.TOAD_DATA_FILES 
  SELECT FILE_ID, V_MON_DATE, TABLESPACE_NAME, FILE_NAME, BYTES 
    FROM DBA_DATA_FILES; 
  COMMIT; 

  /* COLLECT CURRENT FREE SPACE DATA */ 
  INSERT INTO TOAD.TOAD_FREE_SPACE 
  SELECT FILE_ID, V_MON_DATE, SUM(BYTES) 
    FROM DBA_FREE_SPACE 
   GROUP BY FILE_ID, TRUNC(SYSDATE); 
  COMMIT; 

  /* COLLECT CURRENT IO RATE DATA */ 
  INSERT INTO TOAD.TOAD_FILESTAT 
  SELECT FILE#, V_MON_DATE, PHYRDS, PHYWRTS, 
         PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM V$FILESTAT; 
  COMMIT; 

END CAPTURE; 
END TOAD_SPACEMAN; 


begin
  sys.dbms_job.submit(job => :job,
                      what => 'BEGIN 
  TOAD.TOAD_SPACEMAN.CAPTURE; 
END;',
                      next_date => to_date('11-01-2013', 'dd-mm-yyyy'),
                      interval => 'TRUNC(SYSDATE+1)');
  commit;
end;
/

修改监控脚本,监控tablespace趋势并MAIL

0 17 * * * sh /home/oracle/tools/tablespace.sh >> /home/oracle/tools/tablespace.log 2>&1


-bash-3.2$ more  /home/oracle/tools/tablespace.sh
#!/bin/bash
. /home/oracle/.profile

sqlplus / as sysdba <<eof
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125
spool tablespace.csv
@/home/oracle/tools/tablespace.sql

spool off
exit

eof


(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' lihang@xxx.com
(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' dba@xxx.com

-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ more /home/oracle/tools/tablespace.sql
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125

select 'TABLESPACE_NAME,MAX,MEGS_ALLOC,MEGS_USED,MEGS_FREE,MAX_MEGS_FREE,PCT_USED,PCT_FREE,LAST7DAYS_USED_MEGS,7DAYSLATER_REMAIN_MEGS' from dual union all
select  a.tablespace_name
        ||','|| round(maxbytes/1048576) --Max
        ||','|| round(a.bytes_alloc / 1024 / 1024) --megs_alloc
        ||','|| round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) --megs_used
        ||','|| round(nvl(b.bytes_free, 0) / 1024 / 1024) --megs_free
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))) --Max_megs_free
        ||','|| (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100)) --Pct_used
        ||','|| round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) --Pct_Free
        ||','|| c.last7days_used_megs
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))-c.last7days_used_megs) --"7DAYSLATER_REMAIN_MEGS"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b,
        (select x.tablespace_name,(x.Megs-y.Megs) last7days_used_megs
from
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 1)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
)x,
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 8)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
) y
where x.tablespace_name=y.tablespace_name
) c
where a.tablespace_name = b.tablespace_name (+)
  and a.tablespace_name = c.tablespace_name (+)
;

同理 可以使用 dba_hist_tbspc_space_usage 实现以上功能 不过貌似dba_hist_tbspc_space_usage要license.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top