1.
-- Create table
2.
create table GPS_POSITION
3.
(
4.
ID NVARCHAR2(32) not null,
5.
CARDNO NVARCHAR2(32),
6.
GPSTIME DATE,
7.
LONGITUDE NUMBER,
8.
LATITUDE NUMBER,
9.
E_W NVARCHAR2(8),
10.
N_S NVARCHAR2(8),
11.
SPEED NVARCHAR2(16),
12.
GPSSPEED NVARCHAR2(32),
13.
DIRECTION NVARCHAR2(16),
14.
MILEAGE NVARCHAR2(16),
15.
MILEAGE1 NVARCHAR2(16),
16.
HIGHT NVARCHAR2(16),
17.
TEMPERATURE NVARCHAR2(16),
18.
OIL NVARCHAR2(16),
19.
PASSENGER NVARCHAR2(16),
20.
S0 NVARCHAR2(2),
21.
S1 NVARCHAR2(2),
22.
S2 NVARCHAR2(2),
23.
S3 NVARCHAR2(2),
24.
S4 NVARCHAR2(2),
25.
A0 NVARCHAR2(2),
26.
A1 NVARCHAR2(2),
27.
A2 NVARCHAR2(2),
28.
A3 NVARCHAR2(2),
29.
A4 NVARCHAR2(2),
30.
RUNTIME NVARCHAR2(32),
31.
A_V NVARCHAR2(8),
32.
CREATEDATE DATE default sysdate,
33.
GPSID NUMBER,
34.
NUM NUMBER,
35.
WAY NUMBER,
36.
TYPE NUMBER,
37.
WAYSENTDATE NUMBER,
38.
RECEIVEDDATE NUMBER,
39.
B0 NVARCHAR2(2),
40.
B1 NVARCHAR2(2),
41.
B2 NVARCHAR2(2),
42.
B3 NVARCHAR2(2),
43.
B4 NVARCHAR2(2),
44.
B5 NVARCHAR2(2),
45.
B6 NVARCHAR2(2),
46.
CITYID NVARCHAR2(20),
47.
DATASTATE NUMBER,
48.
GPSLOCK NVARCHAR2(2),
49.
GPSPOWER NVARCHAR2(2),
50.
ADDRESS NVARCHAR2(200)
51.
)
52.
partition by range (GPSTIME)
53.
subpartition by list (CITYID)
54.
(
55.
partition P201010 values less than (TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
56.
tablespace bayonet
57.
pctfree 10
58.
initrans 1
59.
maxtrans 255
60.
storage
61.
(
62.
initial 2
63.
minextents 1
64.
maxextents unlimited
65.
)
66.
(
67.
subpartition P201010_P600000 values ('600000') tablespace USERS,
68.
subpartition P201010_P600001 values ('600001') tablespace USERS,
69.
subpartition P201010_P600002 values ('600002') tablespace USERS,
70.
subpartition P201010_P600003 values ('600003') tablespace USERS,
71.
subpartition P201010_P600004 values ('600004') tablespace USERS,
72.
subpartition P201010_P600005 values ('600005') tablespace USERS,
73.
subpartition P201010_P600006 values ('600006') tablespace USERS,
74.
subpartition P201010_P600007 values ('600007') tablespace USERS,
75.
subpartition P201010_P600008 values ('600008') tablespace USERS,
76.
subpartition P201010_P600009 values ('600009') tablespace USERS,
77.
subpartition P201010_P600010 values ('600010') tablespace USERS,
78.
subpartition P201010_P600011 values ('600011') tablespace USERS,
79.
subpartition P201010_P600012 values ('600012') tablespace USERS,
80.
subpartition P201010_P600013 values ('600013') tablespace USERS,
81.
subpartition P201010_P600014 values ('600014') tablespace USERS,
82.
subpartition P201010_P600015 values ('600015') tablespace USERS,
83.
subpartition P201010_P600016 values ('600016') tablespace USERS,
84.
subpartition P201010_P600017 values ('600017') tablespace USERS,
85.
subpartition P201010_P600018 values ('600018') tablespace USERS,
86.
subpartition P201010_P600019 values ('600019') tablespace USERS,
87.
subpartition P201010_P600020 values ('600020') tablespace USERS
88.
),
89.
partition P201112 values less than (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
90.
tablespace bayonet
91.
pctfree 10
92.
initrans 1
93.
maxtrans 255
94.
(
95.
subpartition P201112_P600000 values ('600000') tablespace USERS,
96.
subpartition P201112_P600001 values ('600001') tablespace USERS,
97.
subpartition P201112_P600002 values ('600002') tablespace USERS,
98.
subpartition P201112_P600003 values ('600003') tablespace USERS,
99.
subpartition P201112_P600004 values ('600004') tablespace USERS,
100.
subpartition P201112_P600005 values ('600005') tablespace USERS,
101.
subpartition P201112_P600006 values ('600006') tablespace USERS,
102.
subpartition P201112_P600007 values ('600007') tablespace USERS,
103.
subpartition P201112_P600008 values ('600008') tablespace USERS,
104.
subpartition P201112_P600009 values ('600009') tablespace USERS,
105.
subpartition P201112_P600010 values ('600010') tablespace USERS,
106.
subpartition P201112_P600011 values ('600011') tablespace USERS,
107.
subpartition P201112_P600012 values ('600012') tablespace USERS,
108.
subpartition P201112_P600013 values ('600013') tablespace USERS,
109.
subpartition P201112_P600014 values ('600014') tablespace USERS,
110.
subpartition P201112_P600015 values ('600015') tablespace USERS,
111.
subpartition P201112_P600016 values ('600016') tablespace USERS,
112.
subpartition P201112_P600017 values ('600017') tablespace USERS,
113.
subpartition P201112_P600018 values ('600018') tablespace USERS,
114.
subpartition P201112_P600019 values ('600019') tablespace USERS,
115.
subpartition P201112_P600020 values ('600020') tablespace USERS,
116.
subpartition P201112_P600021 values ('600021') tablespace USERS,
117.
subpartition P201112_P600022 values ('600022') tablespace USERS,
118.
subpartition P201112_P600023 values ('600023') tablespace USERS,
119.
subpartition P201112_PSUB_DEF values (default) tablespace USERS
120.
),
121.
partition P201201 values less than (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
122.
tablespace bayonet
123.
pctfree 10
124.
initrans 1
125.
maxtrans 255
126.
(
127.
subpartition P201201_P600000 values ('600000') tablespace USERS,
128.
subpartition P201201_P600001 values ('600001') tablespace USERS,
129.
subpartition P201201_P600002 values ('600002') tablespace USERS,
130.
subpartition P201201_P600003 values ('600003') tablespace USERS,
131.
subpartition P201201_P600004 values ('600004') tablespace USERS,
132.
subpartition P201201_P600005 values ('600005') tablespace USERS,
133.
subpartition P201201_P600006 values ('600006') tablespace USERS,
134.
subpartition P201201_P600007 values ('600007') tablespace USERS,
135.
subpartition P201201_P600008 values ('600008') tablespace USERS,
136.
subpartition P201201_P600009 values ('600009') tablespace USERS,
137.
subpartition P201201_P600010 values ('600010') tablespace USERS,
138.
subpartition P201201_P600011 values ('600011') tablespace USERS,
139.
subpartition P201201_P600012 values ('600012') tablespace USERS,
140.
subpartition P201201_P600013 values ('600013') tablespace USERS,
141.
subpartition P201201_P600014 values ('600014') tablespace USERS,
142.
subpartition P201201_P600015 values ('600015') tablespace USERS,
143.
subpartition P201201_P600016 values ('600016') tablespace USERS,
144.
subpartition P201201_P600017 values ('600017') tablespace USERS,
145.
subpartition P201201_P600018 values ('600018') tablespace USERS,
146.
subpartition P201201_P600019 values ('600019') tablespace USERS,
147.
subpartition P201201_P600020 values ('600020') tablespace USERS,
148.
subpartition P201201_P600021 values ('600021') tablespace USERS,
149.
subpartition P201201_P600022 values ('600022') tablespace USERS,
150.
subpartition P201201_P600023 values ('600023') tablespace USERS,
151.
subpartition P201201_PSUB_DEF values (default) tablespace USERS
152.
)
153.
);