|
oracle partition by month
#39322017
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
помогите написать скрипт для создание таблицы с партициями по полю "data" по месяцам. следущий SQL ругается на ORA-14013: duplicate partition name
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
-- Create table
create table test
(
data DATE not null,
active VARCHAR2(1) default 'Y'
)
PARTITION BY RANGE (data)
(
-- 2016
PARTITION p_test_jan VALUES LESS THAN (TO_DATE('01-01-2016', 'DD-MM-YYYY')) tablespace test_JAN nologging,
PARTITION p_test_feb VALUES LESS THAN (TO_DATE('01-02-2016', 'DD-MM-YYYY')) tablespace test_FEB nologging,
PARTITION p_test_mar VALUES LESS THAN (TO_DATE('01-03-2016', 'DD-MM-YYYY')) tablespace test_MAR nologging,
PARTITION p_test_apr VALUES LESS THAN (TO_DATE('01-04-2016', 'DD-MM-YYYY')) tablespace test_APR nologging,
PARTITION p_test_may VALUES LESS THAN (TO_DATE('01-05-2016', 'DD-MM-YYYY')) tablespace test_MAY nologging,
PARTITION p_test_jun VALUES LESS THAN (TO_DATE('01-06-2016', 'DD-MM-YYYY')) tablespace test_JUN nologging,
PARTITION p_test_jul VALUES LESS THAN (TO_DATE('01-07-2016', 'DD-MM-YYYY')) tablespace test_JUL nologging,
PARTITION p_test_aug VALUES LESS THAN (TO_DATE('01-08-2016', 'DD-MM-YYYY')) tablespace test_AUG nologging,
PARTITION p_test_sep VALUES LESS THAN (TO_DATE('01-09-2016', 'DD-MM-YYYY')) tablespace test_SEP nologging,
PARTITION p_test_oct VALUES LESS THAN (TO_DATE('01-10-2016', 'DD-MM-YYYY')) tablespace test_OCT nologging,
PARTITION p_test_nov VALUES LESS THAN (TO_DATE('01-11-2016', 'DD-MM-YYYY')) tablespace test_NOV nologging,
PARTITION p_test_dec VALUES LESS THAN (TO_DATE('01-12-2016', 'DD-MM-YYYY')) tablespace test_DEC nologging,
-- 2017
PARTITION p_test_jan VALUES LESS THAN (TO_DATE('01-01-2017', 'DD-MM-YYYY')) tablespace test_JAN nologging,
PARTITION p_test_feb VALUES LESS THAN (TO_DATE('01-02-2017', 'DD-MM-YYYY')) tablespace test_FEB nologging,
PARTITION p_test_mar VALUES LESS THAN (TO_DATE('01-03-2017', 'DD-MM-YYYY')) tablespace test_MAR nologging,
PARTITION p_test_apr VALUES LESS THAN (TO_DATE('01-04-2017', 'DD-MM-YYYY')) tablespace test_APR nologging,
PARTITION p_test_may VALUES LESS THAN (TO_DATE('01-05-2017', 'DD-MM-YYYY')) tablespace test_MAY nologging,
PARTITION p_test_jun VALUES LESS THAN (TO_DATE('01-06-2017', 'DD-MM-YYYY')) tablespace test_JUN nologging,
PARTITION p_test_jul VALUES LESS THAN (TO_DATE('01-07-2017', 'DD-MM-YYYY')) tablespace test_JUL nologging,
PARTITION p_test_aug VALUES LESS THAN (TO_DATE('01-08-2017', 'DD-MM-YYYY')) tablespace test_AUG nologging,
PARTITION p_test_sep VALUES LESS THAN (TO_DATE('01-09-2017', 'DD-MM-YYYY')) tablespace test_SEP nologging,
PARTITION p_test_oct VALUES LESS THAN (TO_DATE('01-10-2017', 'DD-MM-YYYY')) tablespace test_OCT nologging,
PARTITION p_test_nov VALUES LESS THAN (TO_DATE('01-11-2017', 'DD-MM-YYYY')) tablespace test_NOV nologging,
PARTITION p_test_dec VALUES LESS THAN (TO_DATE('01-12-2017', 'DD-MM-YYYY')) tablespace test_DEC nologging,
-- 2018
PARTITION p_test_jan VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY')) tablespace test_JAN nologging,
PARTITION p_test_feb VALUES LESS THAN (TO_DATE('01-02-2018', 'DD-MM-YYYY')) tablespace test_FEB nologging,
PARTITION p_test_mar VALUES LESS THAN (TO_DATE('01-03-2018', 'DD-MM-YYYY')) tablespace test_MAR nologging,
PARTITION p_test_apr VALUES LESS THAN (TO_DATE('01-04-2018', 'DD-MM-YYYY')) tablespace test_APR nologging,
PARTITION p_test_may VALUES LESS THAN (TO_DATE('01-05-2018', 'DD-MM-YYYY')) tablespace test_MAY nologging,
PARTITION p_test_jun VALUES LESS THAN (TO_DATE('01-06-2018', 'DD-MM-YYYY')) tablespace test_JUN nologging,
PARTITION p_test_jul VALUES LESS THAN (TO_DATE('01-07-2018', 'DD-MM-YYYY')) tablespace test_JUL nologging,
PARTITION p_test_aug VALUES LESS THAN (TO_DATE('01-08-2018', 'DD-MM-YYYY')) tablespace test_AUG nologging,
PARTITION p_test_sep VALUES LESS THAN (TO_DATE('01-09-2018', 'DD-MM-YYYY')) tablespace test_SEP nologging,
PARTITION p_test_oct VALUES LESS THAN (TO_DATE('01-10-2018', 'DD-MM-YYYY')) tablespace test_OCT nologging,
PARTITION p_test_nov VALUES LESS THAN (TO_DATE('01-11-2018', 'DD-MM-YYYY')) tablespace test_NOV nologging,
PARTITION p_test_dec VALUES LESS THAN (TO_DATE('01-12-2018', 'DD-MM-YYYY')) tablespace test_DEC nologging
);
|
|
|
