ORA-14030: non-existent partitioning column in CREATE TABLE statement
Hi All,
We are trying to create a partition materialized view and getting a below error.
ORA-14030: non-existent partitioning column in CREATE TABLE statement
Our base tables GL_BALANCES21 and GL_CODE_COMBINATIONS21 is already partitioned by Range interval on Code_combination_id.
The same way we are trying to partition the materialized view
We are getting error.
ORA-14030: non-existent partitioning column in CREATE TABLE statement
In where clause there are 4 tables gl_balances21, gl_code_combinations21, gl_periods, gl_set_of_books.
CREATE MATERIALIZED VIEW apps.BAL_PART
PARTITION BY RANGE ("CODE_COMBINATION_ID")
(PARTITION VALUES LESS THAN (80000),
PARTITION VALUES LESS THAN (160000),
PARTITION VALUES LESS THAN (240000),
PARTITION VALUES LESS THAN (320000),
PARTITION VALUES LESS THAN (400000),
PARTITION VALUES LESS THAN (480000),
PARTITION VALUES LESS THAN (560000),
PARTITION VALUES LESS THAN (640000),
PARTITION VALUES LESS THAN (720000),
PARTITION VALUES LESS THAN (800000),
PARTITION VALUES LESS THAN (880000),
PARTITION VALUES LESS THAN (960000),
PARTITION VALUES LESS THAN (10400000),
PARTITION VALUES LESS THAN (11200000),
PARTITION VALUES LESS THAN (12000000),
PARTITION VALUES LESS THAN (12800000),
PARTITION VALUES LESS THAN (13600000),
PARTITION VALUES LESS THAN (14400000),
PARTITION VALUES LESS THAN (15200000),
PARTITION VALUES LESS THAN (16000000),
PARTITION VALUES LESS THAN (16800000),
PARTITION VALUES LESS THAN (17600000),
PARTITION VALUES LESS THAN (18400000),
PARTITION VALUES LESS THAN (19200000),
PARTITION VALUES LESS THAN (20000000),
PARTITION VALUES LESS THAN (20800000),
PARTITION VALUES LESS THAN (21600000),
PARTITION VALUES LESS THAN (22400000),
PARTITION VALUES LESS THAN (23200000),
PARTITION VALUES LESS THAN (24000000),
PARTITION VALUES LESS THAN (24800000),
PARTITION VALUES LESS THAN (25600000),
PARTITION VALUES LESS THAN (26400000),
PARTITION VALUES LESS THAN (27200000),
PARTITION VALUES LESS THAN (28000000),
PARTITION VALUES LESS THAN (28800000),
PARTITION VALUES LESS THAN (29600000),
PARTITION VALUES LESS THAN (30400000),
PARTITION VALUES LESS THAN (MAXVALUE))
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE as
SELECT GL.GL_CODE_COMBINATIONS21.ROWID C1,
GL.GL_BALANCES21.ROWID C2,
"GL"."GL_BALANCES21"."ACTUAL_FLAG" ,
"GL"."GL_BALANCES21"."CURRENCY_CODE" ,
"GL"."GL_BALANCES21"."PERIOD_NUM" ,
"GL"."GL_BALANCES21"."PERIOD_YEAR" ,
"GL"."GL_BALANCES21"."SET_OF_BOOKS_ID" "SOB_ID",
"GL"."GL_CODE_COMBINATIONS21"."CODE_COMBINATION_ID" "CCID",
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT1" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT10" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT11" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT12" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT13" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT14" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT2" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT3" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT4" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT5" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT6" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT7" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT8" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT9" ,
"GL"."GL_PERIODS"."PERIOD_NAME" ,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal_Cr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Close_Bal_Cr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) Open_Bal_Dr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Close_Bal_Dr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) -
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) -
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) -
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Close_Bal,
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Period_Cr,
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Period_Dr
FROM GL.GL_CODE_COMBINATIONS21,
GL.GL_BALANCES21,
GL.GL_SETS_OF_BOOKS,
GL.GL_PERIODS
WHERE GL.GL_BALANCES21.CODE_COMBINATION_ID =GL.GL_CODE_COMBINATIONS21.CODE_COMBINATION_ID
AND GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID = GL.GL_BALANCES21.SET_OF_BOOKS_ID
AND GL.GL_PERIODS.PERIOD_NUM = GL.GL_BALANCES21.PERIOD_NUM
AND GL.GL_PERIODS.PERIOD_YEAR = GL.GL_BALANCES21.PERIOD_YEAR
AND GL.GL_PERIODS.PERIOD_TYPE = GL.GL_BALANCES21.PERIOD_TYPE
AND GL.GL_PERIODS.PERIOD_NAME = GL.GL_BALANCES21.PERIOD_NAME
AND GL.GL_PERIODS.PERIOD_SET_NAME = GL.GL_SETS_OF_BOOKS.PERIOD_SET_NAME
and gl.GL_CODE_COMBINATIONS21.summary_flag != 'Y'
ERROR at line 54:
ORA-01013: user requested cancel of current operation
I checked the metalink notes saying that Ensure that all columns in the partitioning column list are columns of
the table being created.
Partition is already there on code_combination_id column of gl_balances21 and gl_code_combinations21.
Please suggest.
Thanks