Oracle Exadata 11.2.0.4
Наткнулся на bug c MERGE /*+ APPEND */ в interval partitioning table в варианте синтаксиса INTO <table> PARTITION FOR <date>.
Если убрать DIRECT LOAD или PARTITION FOR заменить на (SELECT .. FROM <table> WHERE p_date = <date>), то ошибка не возникает.
Тест прилагается. Оракловые логи в данный момент недоступны. Подскажите, кто знает, что за баг?
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.
104.
105.
106.
107.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> DROP TABLE test_merge PURGE;
Table dropped.
SQL> CREATE TABLE test_merge
2 ( p_date DATE
3 , ID INTEGER NOT NULL
4 )
5 PARTITION BY RANGE (p_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
6 ( PARTITION P19990101 VALUES LESS THAN (TO_DATE('1999-01-01', 'YYYY-MM-DD')))
7 /
Table created.
SQL> INSERT /*+ APPEND */ INTO test_merge
2 VALUES (TO_DATE('20160829','YYYYMMDD'), 0)
3 /
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM test_merge
2 /
P_DATE ID
--------- ----------
29-AUG-16 0
SQL> MERGE /*+ APPEND */
2 INTO test_merge PARTITION FOR (TO_DATE('20160830','YYYYMMDD')) tgt
3 USING (select TO_DATE('20160830','YYYYMMDD') s_date
4 , 1 s_id
5 from dual
6 ) src
7 ON (tgt.id = src.s_id)
8 WHEN NOT MATCHED THEN
9 INSERT (tgt.p_date, tgt.id)
10 VALUES (src.s_date, src.s_id)
11 ;
ERROR:
ORA-03114: not connected to ORACLE
MERGE /*+ APPEND */
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 104253
Session ID: 103 Serial number: 1609
SQL> conn ...
Enter password:
Connected.
SQL>
SQL> -- without DIRECT LOAD - SUCCESS
SQL> MERGE
2 INTO test_merge PARTITION FOR (TO_DATE('20160830','YYYYMMDD')) tgt
3 USING (select TO_DATE('20160830','YYYYMMDD') s_date
4 , 1 s_id
5 from dual
6 ) src
7 ON (tgt.id = src.s_id)
8 WHEN NOT MATCHED THEN
9 INSERT (tgt.p_date, tgt.id)
10 VALUES (src.s_date, src.s_id)
11 ;
1 row merged.
SQL> commit;
Commit complete.
SQL> -- with DIRECT LOAD but without PARTITION FOR - SUCCESS as well
SQL> MERGE /*+ APPEND */
2 INTO (SELECT * FROM test_merge WHERE p_date = TO_DATE('20160831','YYYYMMDD')) tgt
3 USING (select TO_DATE('20160831','YYYYMMDD') s_date
4 , 1 s_id
5 from dual
6 ) src
7 ON (tgt.id = src.s_id)
8 WHEN NOT MATCHED THEN
9 INSERT (tgt.p_date, tgt.id)
10 VALUES (src.s_date, src.s_id)
11 ;
1 row merged.
SQL> commit;
Commit complete.
SQL>
