Alert the usage of tablespace

Alert the usage of tablespace

—-Create tablespace for user
create tablespace yw_cux_data  datafile ‘/u02/lsdb/yw_cux_data01.dbf’ size 100m;
create user lsdb_yw identified by ****** default tablespace yw_cux_data temporary  tablespace temp;
grant connect,dba to lsdb_yw;
grant select on DBA_FREE_SPACE to lsdb_yw;
grant select on  DBA_DATA_FILES to lsdb_yw;
grant execute on sys.UTL_TCP to lsdb_yw;
grant execute on sys.UTL_SMTP to lsdb_yw;
grant execute on SYS.UTL_INADDR to lsdb_yw;
grant execute on utl_mail to lsdb_yw

—Create table of email and insert email address of receiver 
conn lsdb_yw/*******
CREATE TABLE TAB_SEND_MAIL
(
  NUM_ID        NUMBER,
  EMAIL_ADDRESS  VARCHAR2(50)
);
insert into TAB_SEND_MAIL values (1,’TEST@163.com’);
commit;

SQL> select comp_name from dba_registry;
 
COMP_NAME
——————————————————————————–
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rules Manager
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
 
17 rows selected
SQL> select global_name,utl_inaddr.get_host_address from global_name;
 
GLOBAL_NAME                                                                      GET_HOST_ADDRESS
——————————————————————————– —
LSDB                                                                             10.12.22.21

—-Run the below scripts to install XML database:
SQL>  @$ORACLE_HOME/rdbms/admin/utlsmtp.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltcp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catqm.sql xdb sysaux temp YES
 
SQL> select comp_name , status from dba_registry;
 COMP_NAME                                                                        STATUS
——————————————————————————– —
Oracle XML Database                                                              VALID
Oracle Database Catalog Views                                                    VALID
Oracle Database Packages and Types                                               VALID

—Grant query permission
grant select on DBA_FREE_SPACE to leosunny;
grant select on  DBA_DATA_FILES to leosunny;

—-If occur the ACL issue please follow the below steps: 
—CreateACL
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>’cux_application_acl.xml’,
        description  => ‘ACL for users of my application.’,
        principal    => ‘HAOHAO’,
        is_grant     => TRUE,
        privilege    => ‘resolve’,
        start_date   => null,
        end_date     => null
  );
commit;
END;
/
—Add resolve of ACL Privileges to user to access network
begin
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => ‘cux_application_acl.xml’,
                                       principal => ‘LEOSUNNY’,
                                       is_grant  => true,
                                       privilege => ‘resolve’);
commit;
end;
/
 
—Add connect of ACL privileges to user 
begin
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => ‘cux_application_acl.xml’,
                                         principal => ‘HAOHAO’,
                                       is_grant  => true,
                                       privilege => ‘connect’);
commit;
end;
/
 
—指派ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => ‘cux_application_acl.xml’,
    host        => ‘10.12.22.23’,
    lower_port  => 25,
    upper_port  => NULL);
  COMMIT;
END;
/
 
 
SQL> select any_path from resource_view where any_path like ‘/sys/acls/%.xml’;
 ANY_PATH
——————————————————————————–
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/cux_application_acl.xml
/sys/acls/ro_all_acl.xml
 
SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
 HOST               LOWER_PORT UPPER_PORT ACL
——————————————————————————– —
10.12.22.23        25         25 /sys/acls/cux_application_acl.xml
 
ACL                                                     PRINCIPAL    PRIVILEGE             IS_GRANT        START_DATE     END_DATE
——————————————————————————– —
/sys/acls/cux_application_acl.xml  SYSADM      connect               true                          
/sys/acls/cux_application_acl.xml  SYSADM      resolve               true                          
 

—-Create stored procedure of sending email
CREATE OR REPLACE PROCEDURE CUX_SEND_MAIL(SUBJECT IN VARCHAR2,
              CONTENTSED IN VARCHAR2
) IS
  EMAIL_SERVER VARCHAR2(30) := ‘127.0.0.1’;
  SENDER_ADDRESS  VARCHAR2(50) := ‘root@test.com’;–sender email
  RECEIVER_ADDRESS  VARCHAR2(30);                 –the variable of receiver
  PORT NUMBER := 25;
  CONN UTL_SMTP.CONNECTION;
  MESG VARCHAR2(4000);
  cursor c_mail is select email_address from TAB_SEND_MAIL;   –the list of receiver

BEGIN
———————–
for c_mail_v in c_mail
  loop
    RECEIVER_ADDRESS:=c_mail_v.email_address;
    CONN:= UTL_SMTP.OPEN_CONNECTION(EMAIL_SERVER,PORT);
    UTL_SMTP.HELO(CONN,EMAIL_SERVER);
    UTL_SMTP.MAIL(CONN,SENDER_ADDRESS);
    UTL_SMTP.RCPT(CONN,RECEIVER_ADDRESS);
    MESG:= ‘CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk’ || UTL_TCP.CRLF ||
    ‘DATE:’ || TO_CHAR(SYSDATE,’YYYYMMDD HH24:MI:SS’) || UTL_TCP.CRLF ||
    ‘FROM:’ || SENDER_ADDRESS || UTL_TCP.CRLF ||
    ‘SUBJECT:’ || SUBJECT || UTL_TCP.CRLF ||
    ‘TO: ‘|| RECEIVER_ADDRESS || UTL_TCP.CRLF ||
    ‘CONTENT-TYPE: TEXT/PLAIN; CHARSET=zhs16gbk’ || UTL_TCP.CRLF ||
    ” || UTL_TCP.CRLF || CONTENTSED || UTL_TCP.CRLF ;
    UTL_SMTP.OPEN_DATA(CONN);
    UTL_SMTP.WRITE_RAW_DATA(CONN,UTL_RAW.CAST_TO_RAW(MESG));
    UTL_SMTP.CLOSE_DATA(CONN);
    UTL_SMTP.QUIT(CONN);
  end loop;
END;

—-Create store procedure of alert tablespace usage 
CREATE OR REPLACE PROCEDURE CUX_TBS_ALERT(v_percent in number default 85)
as
  SUBJECT    varchar2(100);
  CONTENT    varchar2(4000);
  CONTENT1  varchar2(4000);
  p_v2   number;
  p_v1   number;
  p_percent number;
BEGIN
    p_percent:=v_percent;
——根据输入判断百分比
  select count(*) into p_v2 from (select tablespace_name,sum(a.bytes) db from DBA_DATA_FILES a group by tablespace_name) d,
  (select tablespace_name ,sum(b.bytes) fb from DBA_FREE_SPACE b group by tablespace_name) f where
  d.tablespace_name=f.tablespace_name and
  ((d.db-f.fb)/d.db)*100>p_percent;
if p_v2>0 then
    SUBJECT:=’PROD-DB Tablespace Used Space Alert Info’;
  CONTENT1:=’表空间超过预警值’||p_percent||’%’||chr(13)||chr(10)||’表空间名   占用百分比’;
  for it1 in (
   SELECT UPPER(F.TABLESPACE_NAME) name,
         –D.TOT_GROOTTE_MB sized,
         –D.TOT_GROOTTE_MB – F.TOTAL_BYTES used,
         TO_CHAR(ROUND((D.TOT_GROOTTE_MB – F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), ‘99.99’) pec
       –F.TOTAL_BYTES ,
       –F.MAX_BYTES
     FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    and (D.TOT_GROOTTE_MB – F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100>p_percent
    order by pec desc)
  loop
    CONTENT:=it1.name||it1.pec;
  CONTENT1:=CONTENT1||chr(13)||chr(10)||CONTENT;
  end loop;
    cux_send_mail(SUBJECT,CONTENT1); —发送邮件过程
end if;
end;

—-Create to schedule the store procedure :  First running at 8:30 next day,then executed per 2 hours.
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => ‘begin CUX_TBS_ALERT(90); end;’
     ,next_date => to_date(to_char(sysdate+1,’yyyy-mm-dd’)||’ 08:30:00′,’yyyy-mm-dd hh24:mi:ss’)            
     ,interval  => ‘SYSDATE+2/24’  
     ,no_parse  => TRUE
    );
commit;
END;
/

 

http://blog.itpub.net/14710393/viewspace-1074849/

Leave a Reply

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