1 | REM * Set terminal output and command echoing on; log output of this script. |
---|
2 | REM * |
---|
3 | #set termout on |
---|
4 | #set echo on |
---|
5 | |
---|
6 | spool /usr/oracle/admin/sql/creatables.lst |
---|
7 | |
---|
8 | |
---|
9 | REM * creation des numeros sequences. |
---|
10 | |
---|
11 | create sequence SEQ_DATASET; |
---|
12 | create sequence SEQ_ETHERFILE; |
---|
13 | create sequence SEQ_ETHER_APPLICABLE_SERVICE; |
---|
14 | create sequence SEQ_ETHER_ATTRIBUTE; |
---|
15 | create sequence SEQ_ETHER_CRITERIA; |
---|
16 | create sequence SEQ_ETHER_DATA_RIGHT; |
---|
17 | create sequence SEQ_ETHER_EXPERIMENT; |
---|
18 | create sequence SEQ_ETHER_EXPERIMENT_RIGHT; |
---|
19 | create sequence SEQ_ETHER_FILE; |
---|
20 | create sequence SEQ_ETHER_GROUP; |
---|
21 | create sequence SEQ_ETHER_ORDER; |
---|
22 | create sequence SEQ_ETHER_SHOPPINGLIST; |
---|
23 | create sequence SEQ_ETHER_USER; |
---|
24 | create sequence SEQ_ETHER_SERVICE; |
---|
25 | create sequence SEQ_ETHER_SERVICE_RIGHT; |
---|
26 | create sequence SEQ_ETHER_GROUPUSER; |
---|
27 | create sequence SEQ_ETHER_SERVICE_INTERACTIF; |
---|
28 | |
---|
29 | |
---|
30 | REM * creation tables |
---|
31 | create table ETHER_FILE ( |
---|
32 | ETH_ID NUMBER(8) NOT NULL, |
---|
33 | ETH_ID_EXPERIMENT NUMBER(8) NOT NULL, |
---|
34 | ETH_PARAMETER VARCHAR2(30), |
---|
35 | ETH_SUBPARAMETER VARCHAR2(30), |
---|
36 | ETH_DATASET_VERSION VARCHAR2(15), |
---|
37 | ETH_CEOS_LEVEL VARCHAR2(15), |
---|
38 | ETH_CEOS_SUBLEVEL VARCHAR2(15), |
---|
39 | ETH_ATMOSPHERIC_LEVEL VARCHAR2(30), |
---|
40 | ETH_FIRST_DATE NUMBER(13), |
---|
41 | ETH_LAST_DATE NUMBER(13), |
---|
42 | ETH_LON_MIN NUMBER(6,3), |
---|
43 | ETH_LON_MAX NUMBER(6,3), |
---|
44 | ETH_LAT_MIN NUMBER(6,3), |
---|
45 | ETH_LAT_MAX NUMBER(6,3), |
---|
46 | ETH_ALT_MIN NUMBER(6,3), |
---|
47 | ETH_ALT_MAX NUMBER(6,3), |
---|
48 | ETH_FORMAT VARCHAR2(15), |
---|
49 | ETH_PATH VARCHAR2(256), |
---|
50 | ETH_TAR_FILE VARCHAR2(256), |
---|
51 | ETH_ON_LINE NUMBER(1), |
---|
52 | ETH_SIZE NUMBER(12), |
---|
53 | ETH_ID_DATASET NUMBER(8) |
---|
54 | ) |
---|
55 | PCTFREE 30 |
---|
56 | PCTUSED 50 |
---|
57 | tablespace SYSTEM |
---|
58 | STORAGE (INITIAL 50K |
---|
59 | NEXT 500K |
---|
60 | MAXEXTENTS 100 |
---|
61 | PCTINCREASE 25); |
---|
62 | |
---|
63 | create table ETHER_DATASET ( |
---|
64 | SET_ID NUMBER(8) NOT NULL, |
---|
65 | SET_ID_EXPERIMENT NUMBER(8) NOT NULL, |
---|
66 | SET_PARAMETER VARCHAR2(30), |
---|
67 | SET_SUBPARAMETER VARCHAR2(30), |
---|
68 | SET_CEOS_LEVEL VARCHAR2(15), |
---|
69 | SET_CEOS_SUBLEVEL VARCHAR2(15), |
---|
70 | SET_DATASET_VERSION VARCHAR2(15), |
---|
71 | SET_ATMOSPHERIC_LEVEL VARCHAR2(30), |
---|
72 | SET_FORMAT VARCHAR2(15), |
---|
73 | SET_FIRST_DATE NUMBER(13), |
---|
74 | SET_LAST_DATE NUMBER(13), |
---|
75 | SET_LON_MIN NUMBER(6,3), |
---|
76 | SET_LON_MAX NUMBER(6,3), |
---|
77 | SET_LAT_MIN NUMBER(6,3), |
---|
78 | SET_LAT_MAX NUMBER(6,3), |
---|
79 | SET_UPPER_ALTITUDE NUMBER(6,3), |
---|
80 | SET_LOWER_ALTITUDE NUMBER(6,3), |
---|
81 | SET_SIZE NUMBER(12), |
---|
82 | SET_NB_FILES NUMBER(5) |
---|
83 | ) |
---|
84 | PCTFREE 10 |
---|
85 | PCTUSED 40 |
---|
86 | tablespace SYSTEM |
---|
87 | STORAGE (INITIAL 20K |
---|
88 | NEXT 500K |
---|
89 | MAXEXTENTS 100 |
---|
90 | PCTINCREASE 25); |
---|
91 | |
---|
92 | create table ETHER_ATTRIBUTE ( |
---|
93 | ATT_ID NUMBER(5) NOT NULL, |
---|
94 | ATT_ID_EXPERIMENT NUMBER(5) NOT NULL, |
---|
95 | ATT_CODE NUMBER(2) , |
---|
96 | ATT_NAME VARCHAR2(30), |
---|
97 | ATT_OPERATOR VARCHAR2(10), |
---|
98 | ATT_ORACLE_TYPE VARCHAR2(30), |
---|
99 | ATT_HTML_TYPE NUMBER(1), |
---|
100 | ATT_COLUMN_NAME VARCHAR2(50), |
---|
101 | ATT_USED NUMBER(1) |
---|
102 | ) |
---|
103 | PCTFREE 10 |
---|
104 | PCTUSED 40 |
---|
105 | tablespace SYSTEM |
---|
106 | STORAGE (INITIAL 10K |
---|
107 | NEXT 5K |
---|
108 | MAXEXTENTS 50 |
---|
109 | PCTINCREASE 25); |
---|
110 | |
---|
111 | |
---|
112 | create table ETHER_USER ( |
---|
113 | ID NUMBER(8) NOT NULL, |
---|
114 | LOGIN VARCHAR2(30) NOT NULL, |
---|
115 | NAME VARCHAR2(30), |
---|
116 | FIRST_NAME VARCHAR2(30), |
---|
117 | PASSWORD VARCHAR2(30), |
---|
118 | EMAIL VARCHAR2(50), |
---|
119 | IP_ADDRESS VARCHAR2(15), |
---|
120 | LABORATORY_NAME VARCHAR2(30), |
---|
121 | ADDRESS VARCHAR2(250), |
---|
122 | TELEPHONE VARCHAR2(20), |
---|
123 | REPOSITORY_PATH VARCHAR2(256), |
---|
124 | NETWORK_QUOTA NUMBER(3), |
---|
125 | REPOSITORY_MAX_SIZE NUMBER(3), |
---|
126 | ORDER_LIFETIME NUMBER(3), |
---|
127 | MEDIA_ID NUMBER(3), |
---|
128 | ROLE VARCHAR2(1) |
---|
129 | ) |
---|
130 | PCTFREE 10 |
---|
131 | PCTUSED 40 |
---|
132 | tablespace SYSTEM |
---|
133 | STORAGE (INITIAL 5K |
---|
134 | NEXT 5K |
---|
135 | MAXEXTENTS 5 |
---|
136 | PCTINCREASE 10); |
---|
137 | |
---|
138 | create table ETHER_GROUP ( |
---|
139 | GRP_ID NUMBER(5) NOT NULL, |
---|
140 | GRP_GROUPNAME VARCHAR2(25) |
---|
141 | ) |
---|
142 | PCTFREE 10 |
---|
143 | PCTUSED 40 |
---|
144 | tablespace SYSTEM |
---|
145 | STORAGE (INITIAL 5K |
---|
146 | NEXT 5K |
---|
147 | MAXEXTENTS 5 |
---|
148 | PCTINCREASE 10); |
---|
149 | |
---|
150 | create table ETHER_GROUP_USER ( |
---|
151 | ID NUMBER(8) NOT NULL, |
---|
152 | GROUP_ID NUMBER(8), |
---|
153 | USER_ID NUMBER(8) |
---|
154 | ) |
---|
155 | PCTFREE 10 |
---|
156 | PCTUSED 40 |
---|
157 | tablespace SYSTEM |
---|
158 | STORAGE (INITIAL 5K |
---|
159 | NEXT 5K |
---|
160 | MAXEXTENTS 5 |
---|
161 | PCTINCREASE 10); |
---|
162 | |
---|
163 | create table ETHER_EXPERIMENT ( |
---|
164 | EXP_ID NUMBER(5) NOT NULL, |
---|
165 | EXP_NAME VARCHAR2(50) NOT NULL, |
---|
166 | EXP_MISSION VARCHAR2(30), |
---|
167 | EXP_URLINFO VARCHAR2(50), |
---|
168 | EXP_LON_MIN NUMBER(6,3), |
---|
169 | EXP_LON_MAX NUMBER(6,3), |
---|
170 | EXP_LAT_MIN NUMBER(6,3), |
---|
171 | EXP_LAT_MAX NUMBER(6,3), |
---|
172 | EXP_ALT_MIN NUMBER(6,3), |
---|
173 | EXP_ALT_MAX NUMBER(6,3), |
---|
174 | EXP_FIRST_DATE DATE, |
---|
175 | EXP_LAST_DATE DATE, |
---|
176 | EXP_PUBLIC NUMBER(1), |
---|
177 | EXP_READ VARCHAR2(256), |
---|
178 | EXP_RIGHT VARCHAR2(256) |
---|
179 | ) |
---|
180 | PCTFREE 10 |
---|
181 | PCTUSED 40 |
---|
182 | tablespace SYSTEM |
---|
183 | STORAGE (INITIAL 5K |
---|
184 | NEXT 5K |
---|
185 | MAXEXTENTS 5 |
---|
186 | PCTINCREASE 10); |
---|
187 | |
---|
188 | create table ETHER_EXPERIMENT_RIGHT ( |
---|
189 | ERI_ID NUMBER(5) NOT NULL, |
---|
190 | ERI_EXPERIMENT_ID NUMBER(5), |
---|
191 | ERI_GROUP_ID NUMBER(5) |
---|
192 | ) |
---|
193 | PCTFREE 10 |
---|
194 | PCTUSED 40 |
---|
195 | tablespace SYSTEM |
---|
196 | STORAGE (INITIAL 5K |
---|
197 | NEXT 5K |
---|
198 | MAXEXTENTS 5 |
---|
199 | PCTINCREASE 10); |
---|
200 | |
---|
201 | create table ETHER_SERVICE_RIGHT ( |
---|
202 | SRI_ID NUMBER(8) NOT NULL, |
---|
203 | SRI_SERVICE_ID NUMBER(8), |
---|
204 | SRI_GROUP_ID NUMBER(8) |
---|
205 | ) |
---|
206 | PCTFREE 10 |
---|
207 | PCTUSED 40 |
---|
208 | tablespace SYSTEM |
---|
209 | STORAGE (INITIAL 5K |
---|
210 | NEXT 5K |
---|
211 | MAXEXTENTS 5 |
---|
212 | PCTINCREASE 10); |
---|
213 | |
---|
214 | create table ETHER_DATA_RIGHT ( |
---|
215 | DRI_ID NUMBER(5) NOT NULL, |
---|
216 | DRI_GROUP_ID NUMBER(5), |
---|
217 | DRI_DATASET_ID NUMBER(5), |
---|
218 | DRI_LON_MIN NUMBER(6,3), |
---|
219 | DRI_LON_MAX NUMBER(6,3), |
---|
220 | DRI_LAT_MIN NUMBER(6,3), |
---|
221 | DRI_LAT_MAX NUMBER(6,3), |
---|
222 | DRI_ALT_MIN NUMBER(6,3), |
---|
223 | DRI_ALT_MAX NUMBER(6,3), |
---|
224 | DRI_FIRST_DATE NUMBER(13), |
---|
225 | DRI_LAST_DATE NUMBER(13) |
---|
226 | ) |
---|
227 | PCTFREE 10 |
---|
228 | PCTUSED 40 |
---|
229 | tablespace SYSTEM |
---|
230 | STORAGE (INITIAL 5K |
---|
231 | NEXT 5K |
---|
232 | MAXEXTENTS 5 |
---|
233 | PCTINCREASE 10); |
---|
234 | |
---|
235 | create table ETHER_SHOPPINGLIST ( |
---|
236 | SHO_ID NUMBER(5) NOT NULL, |
---|
237 | SHO_USERID NUMBER(5), |
---|
238 | SHO_NAME VARCHAR2(25), |
---|
239 | SHO_NBFILES NUMBER(9), |
---|
240 | SHO_VOLUME NUMBER(9) |
---|
241 | ) |
---|
242 | PCTFREE 10 |
---|
243 | PCTUSED 40 |
---|
244 | tablespace SYSTEM |
---|
245 | STORAGE (INITIAL 5 |
---|
246 | NEXT 5K |
---|
247 | MAXEXTENTS 10 |
---|
248 | PCTINCREASE 10); |
---|
249 | |
---|
250 | create table ETHER_SHOPPINGLISTCONTENT ( |
---|
251 | SHO_ID NUMBER(5) NOT NULL, |
---|
252 | SHO_IDFILE NUMBER(9), |
---|
253 | SHO_TYPEFILE NUMBER(1) |
---|
254 | ) |
---|
255 | PCTFREE 30 |
---|
256 | PCTUSED 60 |
---|
257 | tablespace SYSTEM |
---|
258 | STORAGE (INITIAL 10K |
---|
259 | NEXT 10K |
---|
260 | MAXEXTENTS 20 |
---|
261 | PCTINCREASE 20); |
---|
262 | |
---|
263 | |
---|
264 | |
---|
265 | create table ETHER_ORDER ( |
---|
266 | ORD_ID NUMBER(5) NOT NULL, |
---|
267 | ORD_USERID NUMBER(5), |
---|
268 | ORD_SHOPPINGLISTID NUMBER(5), |
---|
269 | ORD_MEDIA NUMBER(5), |
---|
270 | ORD_VOLUME NUMBER(9), |
---|
271 | ORD_STATUS NUMBER(5), |
---|
272 | ORD_DATE_START DATE, |
---|
273 | ORD_DATE_FINISH DATE |
---|
274 | ) |
---|
275 | PCTFREE 10 |
---|
276 | PCTUSED 40 |
---|
277 | tablespace SYSTEM |
---|
278 | STORAGE (INITIAL 5K |
---|
279 | NEXT 5K |
---|
280 | MAXEXTENTS 10 |
---|
281 | PCTINCREASE 10); |
---|
282 | |
---|
283 | create table ETHER_SERVICE ( |
---|
284 | SER_ID NUMBER(5) NOT NULL, |
---|
285 | SER_NAME VARCHAR2(25)NOT NULL, |
---|
286 | SER_QUOTA NUMBER(5), |
---|
287 | SER_VERSION VARCHAR2(25), |
---|
288 | SER_STATE VARCHAR2(25), |
---|
289 | SER_CODING VARCHAR2(25), |
---|
290 | SER_DESIGNER VARCHAR2(25), |
---|
291 | SER_INFO VARCHAR2(256), |
---|
292 | SER_DESCRIPTION VARCHAR2(256), |
---|
293 | SER_HOST VARCHAR2(25), |
---|
294 | SER_SOURCE_PATH VARCHAR2(256), |
---|
295 | SER_EXEC_PATH VARCHAR2(256), |
---|
296 | SER_EXEC_NAME VARCHAR2(256), |
---|
297 | SER_PARAM_FILE VARCHAR2(256), |
---|
298 | SER_KEEP_ORIGINAL VARCHAR2(1), |
---|
299 | SER_INTERACTIF VARCHAR2(1), |
---|
300 | SER_ACTIVATION_URL VARCHAR2(256), |
---|
301 | SER_OUT_ORDER VARCHAR2(1), |
---|
302 | SER_PARAMETER_PAGE VARCHAR2(256), |
---|
303 | SER_COEFFICIENT NUMBER(9) |
---|
304 | ) |
---|
305 | PCTFREE 10 |
---|
306 | PCTUSED 40 |
---|
307 | tablespace SYSTEM |
---|
308 | STORAGE (INITIAL 5K |
---|
309 | NEXT 5K |
---|
310 | MAXEXTENTS 10 |
---|
311 | PCTINCREASE 10); |
---|
312 | |
---|
313 | |
---|
314 | create table ETHER_APPLICABLE_SERVICE ( |
---|
315 | ID NUMBER(8) NOT NULL, |
---|
316 | SERVICE_ID NUMBER(8), |
---|
317 | DATASET_ID NUMBER(8) |
---|
318 | ) |
---|
319 | PCTFREE 10 |
---|
320 | PCTUSED 40 |
---|
321 | tablespace SYSTEM |
---|
322 | STORAGE (INITIAL 5K |
---|
323 | NEXT 5K |
---|
324 | MAXEXTENTS 10 |
---|
325 | PCTINCREASE 10); |
---|
326 | |
---|
327 | REM * creation des indexes |
---|
328 | |
---|
329 | create index order_idx on ETHER_ORDER (ORD_ID, ORD_USERID) |
---|
330 | TABLESPACE INDX |
---|
331 | STORAGE (INITIAL 20K |
---|
332 | NEXT 20K |
---|
333 | PCTINCREASE 25) |
---|
334 | PCTFREE 0; |
---|
335 | |
---|
336 | create index shoppinlist_idx on ETHER_SHOPPINGLIST (sho_id, sho_userid) |
---|
337 | TABLESPACE INDX |
---|
338 | STORAGE (INITIAL 20K |
---|
339 | NEXT 20K |
---|
340 | PCTINCREASE 25) |
---|
341 | PCTFREE 0; |
---|
342 | |
---|
343 | create index service_idx on ETHER_SERVICE (ser_id, ser_interactif) |
---|
344 | TABLESPACE INDX |
---|
345 | STORAGE (INITIAL 20K |
---|
346 | NEXT 20K |
---|
347 | PCTINCREASE 25) |
---|
348 | PCTFREE 0; |
---|
349 | |
---|
350 | create index shoppintlistcontent_idx on ETHER_SHOPPINGLISTCONTENT (sho_id) |
---|
351 | TABLESPACE INDX |
---|
352 | STORAGE (INITIAL 20K |
---|
353 | NEXT 20K |
---|
354 | PCTINCREASE 25) |
---|
355 | PCTFREE 0; |
---|
356 | |
---|
357 | create index fileexp_idx on ETHER_FILE (eth_id_experiment) |
---|
358 | TABLESPACE INDX |
---|
359 | STORAGE (INITIAL 20K |
---|
360 | NEXT 20K |
---|
361 | PCTINCREASE 25) |
---|
362 | PCTFREE 0; |
---|
363 | |
---|
364 | create index datasetexp_idx on ETHER_DATASET (set_id_experiment ) |
---|
365 | TABLESPACE INDX |
---|
366 | STORAGE (INITIAL 20K |
---|
367 | NEXT 20K |
---|
368 | PCTINCREASE 25) |
---|
369 | PCTFREE 0; |
---|
370 | |
---|
371 | create index attexpe_idx on ETHER_ATTRIBUTE (ATT_id_experiment) |
---|
372 | TABLESPACE INDX |
---|
373 | STORAGE (INITIAL 20K |
---|
374 | NEXT 20K |
---|
375 | PCTINCREASE 25) |
---|
376 | PCTFREE 0; |
---|
377 | |
---|
378 | create index rightdata_idx on ETHER_DATA_RIGHT (dri_datasetid, dri_group_id) |
---|
379 | TABLESPACE INDX |
---|
380 | STORAGE (INITIAL 20K |
---|
381 | NEXT 20K |
---|
382 | PCTINCREASE 25) |
---|
383 | PCTFREE 0; |
---|
384 | |
---|
385 | |
---|
386 | |
---|
387 | exit |
---|
388 | spoot off |
---|