|
15.09.2020, 17:12
#39998911
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
Рейтинг:
0
/ 0
|
|
|
|
Только что нарвался в 18C/19C. Если в child table есть XMLTYPE:
Тест кейс:
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.
52.
53.
54.
55.
56.
57.
58.
SELECT BANNER
FROM V$VERSION
/
CREATE TABLE SUBMISSION(
SUBM_ID INTEGER NOT NULL,
CRT_DT TIMESTAMP(6) NOT NULL
)
PARTITION BY RANGE(CRT_DT)
(
PARTITION SEP2020 VALUES LESS THAN (TIMESTAMP' 2020-10-01 00:00:00')
)
/
ALTER TABLE SUBMISSION
ADD CONSTRAINT PK_SUBMISSION
PRIMARY KEY(SUBM_ID)
/
CREATE TABLE ALERT_RULE(
ALERT_ID INTEGER NOT NULL,
SUBM_ID INTEGER NOT NULL,
ALERT_DESC XMLTYPE
)
/
ALTER TABLE ALERT_RULE
ADD CONSTRAINT PK_ALERT_RULE
PRIMARY KEY(ALERT_ID)
/
ALTER TABLE ALERT_RULE
ADD CONSTRAINT FK1_ALERT_RULE
FOREIGN KEY(SUBM_ID)
REFERENCES SUBMISSION(SUBM_ID)
/
ALTER TABLE ALERT_RULE
MODIFY
PARTITION BY REFERENCE(FK1_ALERT_RULE)
/
DROP TABLE ALERT_RULE PURGE
/
CREATE TABLE ALERT_RULE(
ALERT_ID INTEGER NOT NULL,
SUBM_ID INTEGER NOT NULL
)
/
ALTER TABLE ALERT_RULE
ADD CONSTRAINT PK_ALERT_RULE
PRIMARY KEY(ALERT_ID)
/
ALTER TABLE ALERT_RULE
ADD CONSTRAINT FK1_ALERT_RULE
FOREIGN KEY(SUBM_ID)
REFERENCES SUBMISSION(SUBM_ID)
/
ALTER TABLE ALERT_RULE
MODIFY
PARTITION BY REFERENCE(FK1_ALERT_RULE)
/
ALTER TABLE ALERT_RULE
ADD ALERT_DESC XMLTYPE
/
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.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
SQL> SELECT BANNER
2 FROM V$VERSION
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
SQL> CREATE TABLE SUBMISSION(
2 SUBM_ID INTEGER NOT NULL,
3 CRT_DT TIMESTAMP(6) NOT NULL
4 )
5 PARTITION BY RANGE(CRT_DT)
6 (
7 PARTITION SEP2020 VALUES LESS THAN (TIMESTAMP' 2020-10-01 00:00:00')
8 )
9 /
Table created.
SQL> ALTER TABLE SUBMISSION
2 ADD CONSTRAINT PK_SUBMISSION
3 PRIMARY KEY(SUBM_ID)
4 /
Table altered.
SQL> CREATE TABLE ALERT_RULE(
2 ALERT_ID INTEGER NOT NULL,
3 SUBM_ID INTEGER NOT NULL,
4 ALERT_DESC XMLTYPE
5 )
6 /
Table created.
SQL> ALTER TABLE ALERT_RULE
2 ADD CONSTRAINT PK_ALERT_RULE
3 PRIMARY KEY(ALERT_ID)
4 /
Table altered.
SQL> ALTER TABLE ALERT_RULE
2 ADD CONSTRAINT FK1_ALERT_RULE
3 FOREIGN KEY(SUBM_ID)
4 REFERENCES SUBMISSION(SUBM_ID)
5 /
Table altered.
SQL> ALTER TABLE ALERT_RULE
2 MODIFY
3 PARTITION BY REFERENCE(FK1_ALERT_RULE)
4 /
ALTER TABLE ALERT_RULE
*
ERROR at line 1:
ORA-14427: table does not support modification to a partitioned state DDL
SQL> -- По частям работает
SQL> DROP TABLE ALERT_RULE PURGE
2 /
Table dropped.
SQL> CREATE TABLE ALERT_RULE(
2 ALERT_ID INTEGER NOT NULL,
3 SUBM_ID INTEGER NOT NULL
4 )
5 /
Table created.
SQL> ALTER TABLE ALERT_RULE
2 ADD CONSTRAINT PK_ALERT_RULE
3 PRIMARY KEY(ALERT_ID)
4 /
Table altered.
SQL> ALTER TABLE ALERT_RULE
2 ADD CONSTRAINT FK1_ALERT_RULE
3 FOREIGN KEY(SUBM_ID)
4 REFERENCES SUBMISSION(SUBM_ID)
5 /
Table altered.
SQL> ALTER TABLE ALERT_RULE
2 MODIFY
3 PARTITION BY REFERENCE(FK1_ALERT_RULE)
4 /
Table altered.
SQL> ALTER TABLE ALERT_RULE
2 ADD ALERT_DESC XMLTYPE
3 /
Table altered.
SQL>
By reference "в одном флаконе" работает как надо:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SQL> DROP TABLE ALERT_RULE PURGE
2 /
Table dropped.
SQL> CREATE TABLE ALERT_RULE(
2 ALERT_ID INTEGER NOT NULL,
3 SUBM_ID INTEGER NOT NULL,
4 ALERT_DESC XMLTYPE,
5 CONSTRAINT FK1_ALERT_RULE
6 FOREIGN KEY(SUBM_ID)
7 REFERENCES SUBMISSION(SUBM_ID)
8 )
9 PARTITION BY REFERENCE(FK1_ALERT_RULE)
10 /
Table created.
SQL>
SY. |
|
|
