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/
