我有一個版本為 19c (19.0.0.0.0) 的 Oracle 資料庫。即使在表上設定了范圍間隔 N，我也會收到錯誤“SQL 錯誤 [14400][72000]：ORA-14400：插入的磁區鍵未映射到任何磁區”。
我使用以下 DDL：
CREATE TABLE BAISMART.GSWBIF_IFRS_TEST_MARS3035
(
GSWBIF_ID NUMBER,
MODUL CHAR(2 BYTE) NOT NULL,
KDNRH NUMBER(14) NOT NULL,
KTONR NUMBER(10) NOT NULL,
GSREF VARCHAR2(50 BYTE) NOT NULL,
EWBBT NUMBER(18,2) NOT NULL,
EWBWH CHAR(3 BYTE) NOT NULL,
EWBBP NUMBER(18,2) NOT NULL,
EWBWP CHAR(3 BYTE) NOT NULL,
ABSBT NUMBER(18,2) NOT NULL,
ABTYP CHAR(1 BYTE) NOT NULL,
EWBBS NUMBER(18,2) NOT NULL,
EWBWS CHAR(3 BYTE) NOT NULL,
RSTBT NUMBER(18,2) DEFAULT 0 NOT NULL,
RSTWH CHAR(3 BYTE) DEFAULT ' ' NOT NULL,
ABSWH CHAR(3 BYTE) DEFAULT 'EUR' NOT NULL,
DXIFD NUMBER(8),
IFNAM CHAR(10 BYTE) NOT NULL,
BPLO_ID NUMBER(10) NOT NULL,
MAP_RUN_ID_INSERT NUMBER(22) NOT NULL,
MAP_RUN_ID_UPDATE NUMBER(22),
MDANT CHAR(3 BYTE) DEFAULT '100'
)
COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING
TABLESPACE BAIS_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (BPLO_ID) INTERVAL(1)
SUBPARTITION BY LIST (IFNAM, MODUL)
SUBPARTITION TEMPLATE (
SUBPARTITION SYS_SUB_CB_N1_K1 VALUES (('CB ', 'N1'),('CB ', 'K1')),
SUBPARTITION SYS_SUB_CB_EC VALUES (('CB ', 'EC')),
SUBPARTITION SYS_SUB_CB_G1 VALUES (('CB ', 'G1')),
SUBPARTITION SYS_SUB_CB_A1 VALUES (('CB ', 'A1')),
SUBPARTITION SYS_SUB_CB_S1 VALUES (('CB ', 'S1')),
SUBPARTITION SYS_SUB_CB_AK VALUES (('CB ', 'AK')),
SUBPARTITION SYS_SUB_CB_ZU VALUES (('CB ', 'ZU')),
SUBPARTITION SYS_SUB_INGAT_N1_K1 VALUES (('INGAT ', 'N1'),('INGAT ', 'K1')),
SUBPARTITION SYS_SUB_INGAT_G1_EC VALUES (('INGAT ', 'G1'),('INGAT ', 'EC')),
SUBPARTITION SYS_SUB_INGAT_A1 VALUES (('INGAT ', 'A1')),
SUBPARTITION SYS_SUB_INGAT_S1 VALUES (('INGAT ', 'S1')),
SUBPARTITION SYS_SUB_INGAT_AK VALUES (('INGAT ', 'AK')),
SUBPARTITION SYS_SUB_INGAT_ZU VALUES (('INGAT ', 'ZU')),
SUBPARTITION SYS_SUB_LOANIQ_N1_K1 VALUES (('LOANIQ ', 'N1'),('LOANIQ ', 'K1')),
SUBPARTITION SYS_SUB_LOANIQ_G1_EC VALUES (('LOANIQ ', 'G1'),('LOANIQ ', 'EC')),
SUBPARTITION SYS_SUB_LOANIQ_A1 VALUES (('LOANIQ ', 'A1')),
SUBPARTITION SYS_SUB_LOANIQ_S1 VALUES (('LOANIQ ', 'S1')),
SUBPARTITION SYS_SUB_LOANIQ_AK VALUES (('LOANIQ ', 'AK')),
SUBPARTITION SYS_SUB_LOANIQ_ZU VALUES (('LOANIQ ', 'ZU')),
SUBPARTITION SYS_SUB_VORTEXCB_N1_K1 VALUES (('VORTEXCB ', 'N1'),('VORTEXCB ', 'K1')),
SUBPARTITION SYS_SUB_VORTEXCB_G1_EC VALUES (('VORTEXCB ', 'G1'),('VORTEXCB ', 'EC')),
SUBPARTITION SYS_SUB_VORTEXCB_A1 VALUES (('VORTEXCB ', 'A1')),
SUBPARTITION SYS_SUB_VORTEXCB_S1 VALUES (('VORTEXCB ', 'S1')),
SUBPARTITION SYS_SUB_VORTEXCB_AK VALUES (('VORTEXCB ', 'AK')),
SUBPARTITION SYS_SUB_VORTEXCB_ZU VALUES (('VORTEXCB ', 'ZU')),
SUBPARTITION SYS_SUB_CBAT_N1_K1 VALUES (('CBAT ', 'N1'),('CBAT ', 'K1')),
SUBPARTITION SYS_SUB_CBAT_G1 VALUES (('CBAT ', 'G1')),
SUBPARTITION SYS_SUB_CBAT_EC VALUES (('CBAT ', 'EC')),
SUBPARTITION SYS_SUB_CBAT_A1 VALUES (('CBAT ', 'A1')),
SUBPARTITION SYS_SUB_CBAT_S1 VALUES (('CBAT ', 'S1')),
SUBPARTITION SYS_SUB_CBAT_AK VALUES (('CBAT ', 'AK')),
SUBPARTITION SYS_SUB_CBAT_ZU VALUES (('CBAT ', 'ZU')),
SUBPARTITION SYS_SUB_KORDOBA_N1_K1 VALUES (('KORDOBA ', 'N1'),('KORDOBA ', 'K1')),
SUBPARTITION SYS_SUB_KORDOBA_G1_EC VALUES (('KORDOBA ', 'G1'),('KORDOBA ', 'EC')),
SUBPARTITION SYS_SUB_KORDOBA_A1 VALUES (('KORDOBA ', 'A1')),
SUBPARTITION SYS_SUB_KORDOBA_S1 VALUES (('KORDOBA ', 'S1')),
SUBPARTITION SYS_SUB_KORDOBA_AK VALUES (('KORDOBA ', 'AK')),
SUBPARTITION SYS_SUB_KORDOBA_ZU VALUES (('KORDOBA ', 'ZU')),
SUBPARTITION SYS_SUB_EQUENS_WL_N1_K1 VALUES (('EQUENS-WL ', 'N1'),('EQUENS-WL ', 'K1')),
SUBPARTITION SYS_SUB_EQUENS_WL_G1_EC VALUES (('EQUENS-WL ', 'G1'),('EQUENS-WL ', 'EC')),
SUBPARTITION SYS_SUB_EQUENS_WL_A1 VALUES (('EQUENS-WL ', 'A1')),
SUBPARTITION SYS_SUB_EQUENS_WL_S1 VALUES (('EQUENS-WL ', 'S1')),
SUBPARTITION SYS_SUB_EQUENS_WL_AK VALUES (('EQUENS-WL ', 'AK')),
SUBPARTITION SYS_SUB_EQUENS_WL_ZU VALUES (('EQUENS-WL ', 'ZU')),
SUBPARTITION SYS_SUB_PROFILE7_N1_K1 VALUES (('PROFILE7 ', 'N1'),('PROFILE7 ', 'K1')),
SUBPARTITION SYS_SUB_PROFILE7_G1_EC VALUES (('PROFILE7 ', 'G1'),('PROFILE7 ', 'EC')),
SUBPARTITION SYS_SUB_PROFILE7_A1 VALUES (('PROFILE7 ', 'A1')),
SUBPARTITION SYS_SUB_PROFILE7_S1 VALUES (('PROFILE7 ', 'S1')),
SUBPARTITION SYS_SUB_PROFILE7_AK VALUES (('PROFILE7 ', 'AK')),
SUBPARTITION SYS_SUB_PROFILE7_ZU VALUES (('PROFILE7 ', 'ZU')),
SUBPARTITION SYS_SUB_KORDOBA VALUES (
('KORDOBA-BF', 'N1'),
('KORDOBA-BF', 'G1'),
('KORDOBA-BF', 'A1'),
('KORDOBA-BF', 'S1'),
('KORDOBA-BF', 'AK'),
('KORDOBA-BF', 'ZU'),
('KORDOBA-BF', 'K1'),
('KORDOBA-BF', 'EC'),
('KORDOBA-PK', 'N1'),
('KORDOBA-PK', 'G1'),
('KORDOBA-PK', 'A1'),
('KORDOBA-PK', 'S1'),
('KORDOBA-PK', 'AK'),
('KORDOBA-PK', 'ZU'),
('KORDOBA-PK', 'K1'),
('KORDOBA-PK', 'EC'),
('KORDOBA-RK', 'N1'),
('KORDOBA-RK', 'G1'),
('KORDOBA-RK', 'A1'),
('KORDOBA-RK', 'S1'),
('KORDOBA-RK', 'AK'),
('KORDOBA-RK', 'ZU'),
('KORDOBA-RK', 'K1'),
('KORDOBA-RK', 'EC'),
('KORDOBA-GK', 'N1'),
('KORDOBA-GK', 'G1'),
('KORDOBA-GK', 'A1'),
('KORDOBA-GK', 'S1'),
('KORDOBA-GK', 'AK'),
('KORDOBA-GK', 'ZU'),
('KORDOBA-GK', 'K1'),
('KORDOBA-GK', 'EC'),
('KORDOBA-TH', 'N1'),
('KORDOBA-TH', 'G1'),
('KORDOBA-TH', 'A1'),
('KORDOBA-TH', 'S1'),
('KORDOBA-TH', 'AK'),
('KORDOBA-TH', 'ZU'),
('KORDOBA-TH', 'K1'),
('KORDOBA-TH', 'EC')
),
SUBPARTITION SYS_SUB_DEF VALUES (DEFAULT) )
(
PARTITION SYS_P2064940 VALUES LESS THAN (20190431) COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING TABLESPACE BAIS_DATA
PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (MAXSIZE UNLIMITED BUFFER_POOL DEFAULT )
);
ALTER TABLE BAISMART.GSWBIF_IFRS_TEST_MARS3035 ADD (
CONSTRAINT UK_GSWBIF_IFRS_TEST_MARS3035__I
UNIQUE (MODUL, KDNRH, KTONR, GSREF, BPLO_ID, IFNAM, MDANT)
USING INDEX LOCAL
ENABLE VALIDATE);
我做了以下插入：
Insert into BAISMART.GSWBIF_IFRS_TEST_MARS3035
(MODUL, KDNRH, KTONR, GSREF, EWBBT,
EWBWH, EWBBP, EWBWP, ABSBT, ABTYP,
EWBBS, EWBWS, RSTBT, RSTWH, ABSWH,
DXIFD, IFNAM, BPLO_ID, MAP_RUN_ID_INSERT, MDANT)
Values
('ZU', 7701810149, 0, '000301NEARTNALON', 3.31,
'EUR', 0, ' ', 0, ' ',
3.31, 'EUR', 0, ' ', ' ',
20210731, 'CB ', 20210731, 269494, '100');
之后我收到錯誤 ORA-14400。
但是當我將初始磁區的日期移到以后的日期時
PARTITION SYS_P2064940 VALUES LESS THAN (20190431) COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING TABLESPACE BAIS_DATA
PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (MAXSIZE UNLIMITED BUFFER_POOL DEFAULT
有用。
Are there any limites? I have seen that there are some logical limits on partitions number. But on the insert mentioned upper in the question DB creates only one additional partition independently on which date I specify, so that shouldn't be a problem, am I wrong? Maybe there are any other limits that I didn't notice?
uj5u.com熱心網友回復：
（子）磁區的最大數量不適用于（子）磁區的實際數量。該限制適用于基于現有最小值和最大值的理論數量，即考慮了間隙。
正如我在推薦中已經提到的，您永遠不應該將日期值存盤為數字（或字串），這是一個設計缺陷。始終使用正確的DATE或TIMESTAMP資料型別。
20210731 - 20190431 = 20'300- 不是 823 天。您的每個磁區都有 52 個子磁區。20'300 x 52 = 1'055'600超過 1024K - 1 = 1'023'999 個子磁區的限制
除此之外20190431可能代表 4 月 31 日，它不存在。
你看，使用正確的資料型別有幾個原因。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/442081.html
標籤：sql oracle ddl database-partitioning
下一篇：列印帶有逗號分隔符的陣列