根據Monitor ER圖，創建表（6）
--
--CREATE TABLE AlexMonitor.ProduceLineRealTime(
-- P_L_Real_Time_ID NUMBER(20),
-- PL_ID VARCHAR2(40) NOT NULL,
-- TestTime timestamp(2),
-- PLRT_Input NUMBER(8),
-- PLRT_OutPut NUMBER(8),
-- PRIMARY KEY(PL_ID,TestTime)
-- ）
-- PARTITION BY RANGE (TestTime)
-- (
-- PARTITION PL_20121101 VALUES LESS THAN (to_timestamp('2012-11-02 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_LINE,
-- PARTITION PL_20121102 VALUES LESS THAN (to_timestamp('2012-11-03 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_LINE,
-- PARTITION PL_20121103 VALUES LESS THAN (to_timestamp('2012-11-04 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_LINE,
-- PARTITION PL_20121104 VALUES LESS THAN (to_timestamp('2012-11-05 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_LINE
-- );
--
----增加分区local s索引
--create index Line_index on ProduceLineRealTime(PL_ID) local
--(
-- partition PL_20121101 tablespace TT_LINE,
-- partition PL_20121102 tablespace TT_LINE,
-- partition PL_20121103 tablespace TT_LINE,
-- partition PL_20121104 tablespace TT_LINE
--);
--
--
--CREATE TABLE AlexMonitor.ServerRealTime(
-- Server_Real_Time_ID NUMBER(20),
-- Server_ID VARCHAR2(40) NOT NULL,
-- TestTime timestamp(2) ,
-- TestState VARCHAR2(40),
-- InternetState VARCHAR2(40),
-- FailCount NUMBER(8),
-- primary key(Server_ID,TestTime),
-- FOREIGN KEY(Server_ID) REFERENCES AlexMonitor.Server(Server_ID)
-- )
-- PARTITION BY RANGE (TestTime)
-- (
-- PARTITION SERVER_20121101 VALUES LESS THAN (to_timestamp('2012-11-02 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_SERVER,
-- PARTITION SERVER_20121102 VALUES LESS THAN (to_timestamp('2012-11-03 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_SERVER,
-- PARTITION SERVER_20121103 VALUES LESS THAN (to_timestamp('2012-11-04 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_SERVER,
-- PARTITION SERVER_20121104 VALUES LESS THAN (to_timestamp('2012-11-05 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_SERVER
-- );
--
-- --增加分区local s索引
--create index Server_index on ServerRealTime(Server_ID) local
--(
-- partition SERVER_20121101 tablespace TT_SERVER,
-- partition SERVER_20121102 tablespace TT_SERVER,
-- partition SERVER_20121103 tablespace TT_SERVER,
-- partition SERVER_20121104 tablespace TT_SERVER
--);
--
----
-- CREATE TABLE AlexMonitor.RobotRealTime(
-- R_Real_Time_ID NUMBER(20),
-- Robot_ID VARCHAR2(40) NOT NULL,
-- TestTime timestamp(2),
-- R_Test_State VARCHAR2(40),
-- R_Internet VARCHAR2(40),
-- R_On_Off VARCHAR2(40),
-- Robot_Input NUMBER(8),
-- Robot_Output NUMBER(8),
-- PRIMARY KEY(Robot_ID,TestTime),
-- FOREIGN KEY(Robot_ID) REFERENCES AlexMonitor.RobotManagement(Robot_ID)
-- )
-- PARTITION BY RANGE (TestTime)
-- (
-- PARTITION ROBOT_20121101 VALUES LESS THAN (to_timestamp('2012-11-02 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_ROBOT,
-- PARTITION ROBOT_20121102 VALUES LESS THAN (to_timestamp('2012-11-03 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_ROBOT,
-- PARTITION ROBOT_20121103 VALUES LESS THAN (to_timestamp('2012-11-04 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_ROBOT,
-- PARTITION ROBOT_20121104 VALUES LESS THAN (to_timestamp('2012-11-05 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_ROBOT
-- );
--
-- --增加分区local s索引
-- create index Robot_index on RobotRealTime(Robot_ID) local
--(
-- partition ROBOT_20121101 tablespace TT_ROBOT,
-- partition ROBOT_20121103 tablespace TT_ROBOT,
-- partition ROBOT_20121104 tablespace TT_ROBOT
--);
--
--
CREATE TABLE AlexMonitor.StationRealTime(
Station_Real_Time_ID NUMBER(20) ,
Station_ID VARCHAR2(40) not null,
Testtime timestamp(2),
Station_State VARCHAR2(40),
Fixture_State VARCHAR2(40),
Station_Intenet_State VARCHAR2(40),
Station_Input NUMBER(8),
Station_Output NUMBER(8),
PRIMARY KEY(Station_ID,Testtime),
FOREIGN KEY (Station_ID) REFERENCES AlexMonitor.Station(Station_ID)
)
PARTITION BY RANGE (TestTime)
(
PARTITION STATION_20130118 VALUES LESS THAN (to_timestamp('2013-01-19 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_STATION,
PARTITION STATION_20130119 VALUES LESS THAN (to_timestamp('2013-01-20 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_STATION,
PARTITION STATION_20130120 VALUES LESS THAN (to_timestamp('2013-01-21 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_STATION,
PARTITION STATION_20130121 VALUES LESS THAN (to_timestamp('2013-01-22 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_STATION,
PARTITION STATION_20130122 VALUES LESS THAN (to_timestamp('2013-01-23 00:00:00','yyyy-mm-dd HH24:MI:SS') ) TableSpace TT_STATION
);
--
--增加分区local s索引
-- create index Station_index on StationRealTime(Station_ID) local
--(
-- partition STATION_20121101 tablespace TT_STATION,
-- partition STATION_20121102 tablespace TT_STATION,
-- partition STATION_20121103 tablespace TT_STATION,
-- partition STATION_20121104 tablespace TT_STATION
--);
--
--drop Station_index
--
CREATE TABLE ALEXMONITOR.MESSAGEDATA
(
M_ID NUMBER(30),
AREA VARCHAR2(20 BYTE),
LINENAME VARCHAR2(20 BYTE),
PNP_NAME VARCHAR2(20 BYTE),
STATIONNAME VARCHAR2(20 BYTE),
LOCATIONINDEX NUMBER(2),
SN VARCHAR2(20 BYTE),
RESULT VARCHAR2(20 BYTE),
STATTIME VARCHAR2(20 BYTE),
STOPTIME VARCHAR2(20 BYTE),
SYMPTOM_CODE VARCHAR2(400 BYTE),
SYMPTOM_DESC VARCHAR2(400 BYTE),
TimeNumber number generated always as ( to_number(to_char(to_date(STATTIME,'yyyymmdd HH24:MI:SS'),'D')) ) virtual
)
PARTITION BY LIST (TimeNumber)
(
PARTITION Sun VALUES (1) TableSpace MessageData_Space01 ,
PARTITION Mon VALUES (2) TableSpace MessageData_Space02 ,
PARTITION Tue VALUES (3) TableSpace MessageData_Space03 ,
PARTITION Wed VALUES (4) TableSpace MessageData_Space04 ,
PARTITION Thu VALUES (5) TableSpace MessageData_Space05 ,
PARTITION Fri VALUES (6) TableSpace MessageData_Space06 ,
PARTITION Sat VALUES (7) TableSpace MessageData_Space07
);
-- --增加分区local s索引
--
--create index message_index
--on AlexMonitor.MESSAGEDATA(STOPTIME) local
--(
-- PARTITION index1 TableSpace MessageData_Space01 ,
-- PARTITION index2 TableSpace MessageData_Space02 ,
-- PARTITION index3 TableSpace MessageData_Space03 ,
-- PARTITION index4 TableSpace MessageData_Space04 ,
-- PARTITION index5 TableSpace MessageData_Space05 ,
-- PARTITION index6 TableSpace MessageData_Space06 ,
-- PARTITION index7 TableSpace MessageData_Space07
--)
--
--
-- Create index AlexMonitor.message_SLAS_index
--on AlexMonitor.MESSAGEDATA(STOPTIME,LineName,AREA);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26851211/viewspace-753058/ ，如需转载，请注明出处，否则将追究法律责任。
