4 Replies Latest reply: Feb 25, 2013 12:51 AM by Avadhut RSS

    ORA-14030: non-existent partitioning column in CREATE TABLE statement

    Avadhut
      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