邮件监控平台搭建

上课的实验--邮件监控平台搭建

注册163邮箱，打开SMTP服务

以sys用户登录，创建表空间和用户并赋权

drop user perfstat cascade;
--不关注路径
create tablespace perfdata datafile 'perfdata.dbf' size 100M autoextend on next 32M maxsize 4G;
--【创建用户】
create user perfstat
  identified by perfstat
  default tablespace perfdata
  temporary tablespace temp
  profile DEFAULT
  quota unlimited on perfdata;
--【赋权】
GRANT ALTER SYSTEM TO PERFSTAT;
GRANT AUDIT SYSTEM TO PERFSTAT;
GRANT ALTER SYSTEM TO PERFSTAT;
GRANT AUDIT SYSTEM TO PERFSTAT;
GRANT CREATE SESSION TO PERFSTAT;
GRANT ALTER SESSION TO PERFSTAT;
GRANT RESTRICTED SESSION TO PERFSTAT;
GRANT CREATE TABLESPACE TO PERFSTAT;
GRANT ALTER TABLESPACE TO PERFSTAT;
GRANT MANAGE TABLESPACE TO PERFSTAT;
GRANT DROP TABLESPACE TO PERFSTAT;
GRANT UNLIMITED TABLESPACE TO PERFSTAT;
GRANT CREATE USER TO PERFSTAT;
GRANT BECOME USER TO PERFSTAT;
GRANT ALTER USER TO PERFSTAT;
GRANT DROP USER TO PERFSTAT;
GRANT CREATE ROLLBACK SEGMENT TO PERFSTAT;
GRANT ALTER ROLLBACK SEGMENT TO PERFSTAT;
GRANT DROP ROLLBACK SEGMENT TO PERFSTAT;
GRANT CREATE TABLE TO PERFSTAT;
GRANT CREATE ANY TABLE TO PERFSTAT;
GRANT ALTER ANY TABLE TO PERFSTAT;
GRANT BACKUP ANY TABLE TO PERFSTAT;
GRANT DROP ANY TABLE TO PERFSTAT;
GRANT LOCK ANY TABLE TO PERFSTAT;
GRANT COMMENT ANY TABLE TO PERFSTAT;
GRANT SELECT ANY TABLE TO PERFSTAT;
GRANT INSERT ANY TABLE TO PERFSTAT;
GRANT UPDATE ANY TABLE TO PERFSTAT;
GRANT DELETE ANY TABLE TO PERFSTAT;
GRANT CREATE CLUSTER TO PERFSTAT;
GRANT CREATE ANY CLUSTER TO PERFSTAT;
GRANT ALTER ANY CLUSTER TO PERFSTAT;
GRANT DROP ANY CLUSTER TO PERFSTAT;
GRANT CREATE ANY INDEX TO PERFSTAT;
GRANT ALTER ANY INDEX TO PERFSTAT;
GRANT DROP ANY INDEX TO PERFSTAT;
GRANT CREATE SYNONYM TO PERFSTAT;
GRANT CREATE ANY SYNONYM TO PERFSTAT;
GRANT DROP ANY SYNONYM TO PERFSTAT;
GRANT CREATE PUBLIC SYNONYM TO PERFSTAT;
GRANT DROP PUBLIC SYNONYM TO PERFSTAT;
GRANT CREATE VIEW TO PERFSTAT;
GRANT CREATE ANY VIEW TO PERFSTAT;
GRANT DROP ANY VIEW TO PERFSTAT;
GRANT CREATE SEQUENCE TO PERFSTAT;
GRANT CREATE ANY SEQUENCE TO PERFSTAT;
GRANT ALTER ANY SEQUENCE TO PERFSTAT;
GRANT DROP ANY SEQUENCE TO PERFSTAT;
GRANT SELECT ANY SEQUENCE TO PERFSTAT;
GRANT CREATE DATABASE LINK TO PERFSTAT;
GRANT CREATE PUBLIC DATABASE LINK TO PERFSTAT;
GRANT DROP PUBLIC DATABASE LINK TO PERFSTAT;
GRANT CREATE ROLE TO PERFSTAT;
GRANT DROP ANY ROLE TO PERFSTAT;
GRANT GRANT ANY ROLE TO PERFSTAT;
GRANT ALTER ANY ROLE TO PERFSTAT;
GRANT AUDIT ANY TO PERFSTAT;
GRANT ALTER DATABASE TO PERFSTAT;
GRANT FORCE TRANSACTION TO PERFSTAT;
GRANT FORCE ANY TRANSACTION TO PERFSTAT;
GRANT CREATE PROCEDURE TO PERFSTAT;
GRANT CREATE ANY PROCEDURE TO PERFSTAT;
GRANT ALTER ANY PROCEDURE TO PERFSTAT;
GRANT DROP ANY PROCEDURE TO PERFSTAT;
GRANT EXECUTE ANY PROCEDURE TO PERFSTAT;
GRANT CREATE TRIGGER TO PERFSTAT;
GRANT CREATE ANY TRIGGER TO PERFSTAT;
GRANT ALTER ANY TRIGGER TO PERFSTAT;
GRANT DROP ANY TRIGGER TO PERFSTAT;
GRANT CREATE PROFILE TO PERFSTAT;
GRANT ALTER PROFILE TO PERFSTAT;
GRANT DROP PROFILE TO PERFSTAT;
GRANT ALTER RESOURCE COST TO PERFSTAT;
GRANT ANALYZE ANY TO PERFSTAT;
GRANT GRANT ANY PRIVILEGE TO PERFSTAT;
GRANT CREATE MATERIALIZED VIEW TO PERFSTAT;
GRANT CREATE ANY MATERIALIZED VIEW TO PERFSTAT;
GRANT ALTER ANY MATERIALIZED VIEW TO PERFSTAT;
GRANT DROP ANY MATERIALIZED VIEW TO PERFSTAT;
GRANT CREATE ANY DIRECTORY TO PERFSTAT;
GRANT DROP ANY DIRECTORY TO PERFSTAT;
GRANT CREATE TYPE TO PERFSTAT;
GRANT CREATE ANY TYPE TO PERFSTAT;
GRANT ALTER ANY TYPE TO PERFSTAT;
GRANT DROP ANY TYPE TO PERFSTAT;
GRANT EXECUTE ANY TYPE TO PERFSTAT;
GRANT UNDER ANY TYPE TO PERFSTAT;
GRANT CREATE LIBRARY TO PERFSTAT;
GRANT CREATE ANY LIBRARY TO PERFSTAT;
GRANT ALTER ANY LIBRARY TO PERFSTAT;
GRANT DROP ANY LIBRARY TO PERFSTAT;
GRANT EXECUTE ANY LIBRARY TO PERFSTAT;
GRANT CREATE OPERATOR TO PERFSTAT;
GRANT CREATE ANY OPERATOR TO PERFSTAT;
GRANT DROP ANY OPERATOR TO PERFSTAT;
GRANT EXECUTE ANY OPERATOR TO PERFSTAT;
GRANT CREATE INDEXTYPE TO PERFSTAT;
GRANT CREATE ANY INDEXTYPE TO PERFSTAT;
GRANT ALTER ANY INDEXTYPE TO PERFSTAT;
GRANT DROP ANY INDEXTYPE TO PERFSTAT;
GRANT UNDER ANY VIEW TO PERFSTAT;
GRANT QUERY REWRITE TO PERFSTAT;
GRANT GLOBAL QUERY REWRITE TO PERFSTAT;
GRANT EXECUTE ANY INDEXTYPE TO PERFSTAT;
GRANT UNDER ANY TABLE TO PERFSTAT;
GRANT CREATE DIMENSION TO PERFSTAT;
GRANT CREATE ANY DIMENSION TO PERFSTAT;
GRANT ALTER ANY DIMENSION TO PERFSTAT;
GRANT DROP ANY DIMENSION TO PERFSTAT;
GRANT MANAGE ANY QUEUE TO PERFSTAT;
GRANT ENQUEUE ANY QUEUE TO PERFSTAT;
GRANT DEQUEUE ANY QUEUE TO PERFSTAT;
GRANT CREATE ANY CONTEXT TO PERFSTAT;
GRANT DROP ANY CONTEXT TO PERFSTAT;
GRANT CREATE ANY OUTLINE TO PERFSTAT;
GRANT ALTER ANY OUTLINE TO PERFSTAT;
GRANT DROP ANY OUTLINE TO PERFSTAT;
GRANT ADMINISTER RESOURCE MANAGER TO PERFSTAT;
GRANT ADMINISTER DATABASE TRIGGER TO PERFSTAT;
GRANT MERGE ANY VIEW TO PERFSTAT;
GRANT ON COMMIT REFRESH TO PERFSTAT;
GRANT RESUMABLE TO PERFSTAT;
GRANT SELECT ANY DICTIONARY TO PERFSTAT;
GRANT DEBUG CONNECT SESSION TO PERFSTAT;
GRANT DEBUG ANY PROCEDURE TO PERFSTAT;
GRANT FLASHBACK ANY TABLE TO PERFSTAT;
GRANT GRANT ANY OBJECT PRIVILEGE TO PERFSTAT;
GRANT CREATE EVALUATION CONTEXT TO PERFSTAT;
GRANT CREATE ANY EVALUATION CONTEXT TO PERFSTAT;
GRANT ALTER ANY EVALUATION CONTEXT TO PERFSTAT;
GRANT DROP ANY EVALUATION CONTEXT TO PERFSTAT;
GRANT EXECUTE ANY EVALUATION CONTEXT TO PERFSTAT;
GRANT CREATE RULE SET TO PERFSTAT;
GRANT CREATE ANY RULE SET TO PERFSTAT;
GRANT ALTER ANY RULE SET TO PERFSTAT;
GRANT DROP ANY RULE SET TO PERFSTAT;
GRANT EXECUTE ANY RULE SET TO PERFSTAT;
GRANT EXPORT FULL DATABASE TO PERFSTAT;
GRANT IMPORT FULL DATABASE TO PERFSTAT;
GRANT CREATE RULE TO PERFSTAT;
GRANT CREATE ANY RULE TO PERFSTAT;
GRANT ALTER ANY RULE TO PERFSTAT;
GRANT DROP ANY RULE TO PERFSTAT;
GRANT EXECUTE ANY RULE TO PERFSTAT;
GRANT ANALYZE ANY DICTIONARY TO PERFSTAT;
GRANT ADVISOR TO PERFSTAT;
GRANT CREATE JOB TO PERFSTAT;
GRANT CREATE ANY JOB TO PERFSTAT;
GRANT EXECUTE ANY PROGRAM TO PERFSTAT;
GRANT EXECUTE ANY CLASS TO PERFSTAT;
GRANT MANAGE SCHEDULER TO PERFSTAT;
GRANT SELECT ANY TRANSACTION TO PERFSTAT;
GRANT DROP ANY SQL PROFILE TO PERFSTAT;
GRANT ALTER ANY SQL PROFILE TO PERFSTAT;
GRANT ADMINISTER SQL TUNING SET TO PERFSTAT;
GRANT ADMINISTER ANY SQL TUNING SET TO PERFSTAT;
GRANT CREATE ANY SQL PROFILE TO PERFSTAT;
GRANT MANAGE FILE GROUP TO PERFSTAT;
GRANT MANAGE ANY FILE GROUP TO PERFSTAT;
GRANT READ ANY FILE GROUP TO PERFSTAT;
GRANT CHANGE NOTIFICATION TO PERFSTAT;
GRANT CREATE EXTERNAL JOB TO PERFSTAT;

然后以perfstat这个用户登录

1、创建短信对列表

create table mo_mail_send_table(
seqno number,
subject varchar2(100),
msg clob,
touser varchar2(200),
createtime date default sysdate,
sendFlag  date, --处理标识， 为null标识暂无程序锁定
sendMark  varchar2(1024)  --处理备注
);

create sequence  seq_mo_mail_send_table start with 1 cache 10;
create table mo_mail_send_ing as select  t.*, 0 sid from mo_mail_send_table t where
1=2;  --批量处理临时表
create table mo_mail_send_table_arch as select * from mo_mail_send_table where
1=2; --处理归