📄 createobj.sql
字号:
prompt
prompt Creating table T_EMAIL
prompt ======================
prompt
create table T_EMAIL
(
EMAIL_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
SERVERIP NUMBER,
SERVERPORT NUMBER,
CLIENTIP NUMBER,
CLIENTPORT NUMBER,
CHARSET NUMBER,
SENDER VARCHAR2(64),
PASS VARCHAR2(32),
RECEIVER VARCHAR2(64),
MAILHEAD VARCHAR2(256),
COMPLETEFLAG NUMBER,
BODYLEN NUMBER,
EMLCONTENTLEN NUMBER
)partition by range (EMAIL_SEQ)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_email_seq on T_email(email_seq) LOCAL (partition P_20000101 ) parallel (degree 4);
create index IX_T_EMAIL_CAPTURTIME on T_EMAIL (CAPTURETIME) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_EMAIL_imsi on T_EMAIL(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_EMAIL_packettype on T_EMAIL(packettype) tablespace idx_miss parallel (degree 8) nologging;
prompt
prompt Creating table T_FTP
prompt ====================
prompt
create table T_FTP
(
FTP_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
LOCALIP NUMBER,
REMOTEIP NUMBER,
SRCPORT NUMBER,
DSTPORT NUMBER,
CHARSET NUMBER,
FTPUSER VARCHAR2(64),
PASS VARCHAR2(32),
LOGINTIME NUMBER,
MSG VARCHAR2(1500)
)partition by range (ftp_SEQ)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_ftp_seq on T_FTP(ftp_seq) LOCAL (partition P_20000101 ) parallel (degree 4);
create index IX_T_FTP on T_FTP (CAPTURETIME) tablespace idx_miss;
create index idx_T_FTP_imsi on T_FTP(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_FTP_ftpuser on T_FTP(ftpuser) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_FTP_packettype on T_FTP(packettype) tablespace idx_miss parallel (degree 8) nologging;
prompt
prompt Creating table T_HTTP
prompt =====================
prompt
create table T_HTTP
(
HTTP_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
LOCALIP NUMBER,
REMOTEIP NUMBER,
SRCPORT NUMBER,
DSTPORT NUMBER,
CHARSET NUMBER,
URL VARCHAR2(256),
HEAD VARCHAR2(256),
DEVICE VARCHAR2(128),
ABSTRACT VARCHAR2(255),
COMPLETEFLAG NUMBER,
HTTPCONTENTLENGTH NUMBER
)
partition by range (http_seq)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_http_seq on T_HTTP(http_seq) LOCAL (partition P_20000101 ) parallel (degree 4);
create index IX_T_HTTP_CAPTURTIME on T_HTTP (CAPTURETIME) tablespace idx_miss;
create index idx_t_http_imsi on t_http(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_http_url on t_http(url) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_http_packettype on t_http(packettype) tablespace idx_miss parallel (degree 8) nologging;
prompt
prompt Creating table T_TELNET
prompt =======================
prompt
create table T_TELNET
(
TELNET_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
LOCALIP NUMBER,
REMOTEIP NUMBER,
SRCPORT NUMBER,
DSTPORT NUMBER,
CHARSET NUMBER,
TELNETUSER VARCHAR2(32),
PASS VARCHAR2(32),
LOGINTIME NUMBER,
STATUS NUMBER,
MSG VARCHAR2(1500)
)partition by range (TELNET_SEQ)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_telnet_seq on T_telnet(TELNET_SEQ) LOCAL (partition P_20000101 ) parallel (degree 4);
create index idx_T_TELNET_CAPTURTIME on T_TELNET (CAPTURETIME) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_TELNET_imsi on T_TELNET(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_TELNET_TELNETUSER on T_TELNET(telnetuser) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_TELNET_packettype on T_TELNET(packettype) tablespace idx_miss parallel (degree 8) nologging;
prompt
prompt Creating table T_WAP
prompt ====================
prompt
create table T_WAP
(
WAP_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
LOCALIP NUMBER,
REMOTEIP NUMBER,
SRCPORT NUMBER,
DSTPORT NUMBER,
CHARSET NUMBER,
URL VARCHAR2(256),
HEAD VARCHAR2(256),
DEVICE VARCHAR2(128),
ABSTRACT VARCHAR2(255),
COMPLETEFLAG NUMBER,
WAPCONTENTLENGTH NUMBER
)partition by range (WAP_SEQ)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_wap_seq on T_WAP(WAP_SEQ) LOCAL (partition P_20000101 ) parallel (degree 4);
create index IX_T_WAP_CAPTURTIME on T_WAP (CAPTURETIME) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_WAP_imsi on T_WAP(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_WAP_url on T_WAP(url) tablespace idx_miss parallel (degree 8) nologging;
create index idx_T_WAP_packettype on T_WAP(packettype) tablespace idx_miss parallel (degree 8) nologging;
create table T_VAST_TIME
(
RES_SET_TIME INTEGER,
RES_MIN_TIME INTEGER,
RULE_ID INTEGER,
TNAME VARCHAR2(100)
)tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
cache nologging
;
create table t_vast_rule_set
(
rule_id integer primary key,
rule_name VARCHAR2(20) ,
rule_switch integer
)tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
cache nologging
;
create table t_packettype
(
packettype integer primary key,
packet_name VARCHAR2(20)
)tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
cache nologging
;
create TABLE t_ims (
ims_seq number(20) NOT NULL ,
imsi VARCHAR2(15) NULL ,
msisdn varchar2 (15) NULL ,
vest varchar2 (32) NULL ,
packettype smallint NULL ,
capturetime int NOT NULL ,
localip int NULL ,
remoteip int NULL ,
srcport int NULL ,
dstport int NULL ,
charset smallint NULL ,
localid varchar2 (64) NULL ,
localpwd varchar2 (128) NULL ,
remoteid varchar2 (64) NULL ,
logintime int NULL ,
msgType smallint NULL ,
imversion smallint NULL ,
mddigest varchar2 (128) NULL ,
msg varchar2 (1500) NULL
)partition by range (ims_seq)
(
partition P_20000101 values less than (2000000000000000000)
tablespace miss
pctfree 0
pctused 80
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
)
)cache nologging
;
CREATE unique INDEX idx_p_ims_seq on T_ims(ims_seq) LOCAL (partition P_20000101 ) parallel (degree 4);
create index IX_t_ims_CAPTURTIME on t_ims (CAPTURETIME) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_ims_imsi on t_ims(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_ims_localid on t_ims(localid) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_ims_packettype on t_ims(packettype) tablespace idx_miss parallel (degree 8) nologging;
create table PROCLOG
(
PROCNAME VARCHAR2(100),
TIME DATE,
CODE VARCHAR2(30),
MSG VARCHAR2(500)
);
/********************************************************************************************************************
*add at 2007-1-4 15:54
*
*V2
*begin
********************************************************************************************************************/
create table t_https
(
HTTPS_SEQ NUMBER not null,
IMSI VARCHAR2(15),
MSISDN VARCHAR2(15),
VEST VARCHAR2(32),
PACKETTYPE NUMBER,
CAPTURETIME NUMBER not null,
LOCALIP NUMBER,
REMOTEIP NUMBER,
SRCPORT NUMBER,
DSTPORT NUMBER,
HTTPSCONTENTLENGTH NUMBER
);
CREATE unique INDEX idx_p_https_seq on t_https(httpS_seq) tablespace idx_miss nologging;
create index IX_T_HTTPs_CAPTURTIME on t_https (CAPTURETIME) tablespace idx_miss nologging;
create index idx_t_https_imsi on t_https(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_https_packettype on t_https(packettype) tablespace idx_miss parallel (degree 8) nologging;
create table T_HTTPS_CONTENT
(
HTTPS_SEQ NUMBER not null,
EXTNAME VARCHAR2(8),
DATA BLOB
);
alter table T_HTTPS_CONTENT
add constraint T_HTTPS_CONTENT_PK primary key (HTTPS_SEQ) using index tablespace idx_miss;
create TABLE t_game (
game_seq number(20) NOT NULL ,
imsi VARCHAR2(15) ,
msisdn varchar2 (15) ,
vest varchar2 (32) ,
packettype int ,
capturetime int NOT NULL ,
localip int NULL ,
remoteip int NULL ,
srcport int NULL ,
dstport int NULL ,
charset int NULL ,
gameType varchar2(64) ,
localid varchar2 (64) ,
localpwd varchar2 (128) ,
remoteid1 varchar2 (64) ,
remoteid2 varchar2 (64) ,
remoteid3 varchar2 (64) ,
logintime int NULL ,
msgType int NULL ,
msg varchar2 (1500)
);
CREATE unique INDEX idx_p_game_seq on t_game(game_seq) tablespace idx_miss parallel (degree 8) nologging;
create index IdX_t_game_CAPTURTIME on t_game (CAPTURETIME) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_game_imsi on t_game(imsi) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_game_localid on t_game(localid) tablespace idx_miss parallel (degree 8) nologging;
create index idx_t_game_packettype on t_game(packettype) tablespace idx_miss parallel (degree 8) nologging;
/********************************************************************************************************************
*add at 2007-1-4 15:54
*
*V2
*end
********************************************************************************************************************/
set feedback off
set define off
prompt Truncating T_VAST_TIME...
truncate table T_VAST_TIME;
prompt Truncating T_VAST_RULE_SET...
truncate table T_VAST_RULE_SET;
prompt Loading T_VAST_RULE_SET...
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (12, 'HTTP POST', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (21, 'FTP', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (31, 'TELNET', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (41, 'SMTP', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (42, 'POP3', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (43, 'WEBMAIL', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (51, 'OICQ', 1);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (52, 'MSN', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (53, 'ICQ', 1);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (54, '雅虎通', 1);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (62, 'WAP POST', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (55, '联众在线', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (81, 'HTTPS', 0);
insert into T_VAST_RULE_SET (RULE_ID, RULE_NAME, RULE_SWITCH)
values (91, '联众游戏', 0);
commit;
prompt 12 records loaded
prompt Loading T_VAST_TIME...
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (30, 30, 1, 't_http,t_http_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (30, 30, 2, 't_ftp');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (30, 30, 3, 't_telnet,t_telnet_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (180, 30, 4, 't_email,t_email_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (360, 30, 5, 't_ims,t_ims_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (90, 30, 6, 't_wap,t_wap_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (30, 30, 8, 't_https,t_https_content');
insert into T_VAST_TIME (RES_SET_TIME, RES_MIN_TIME, RULE_ID, TNAME)
values (30, 30, 9, 't_game');
commit;
commit;
prompt 1 records loaded
set feedback on
set define on
prompt Done.
exit;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -