7 Replies Latest reply: May 3, 2012 3:02 AM by HuaMin Chen RSS

    ORA-12028

    840275
      Hi,

      The master database is 10.2.0.1.0.
      The fast refresh materialized views work well from another 10.2.0.1.0. or 10.2.0.1.0.XE.
      The creation of the same materialized views from 11.2.0.2.0.XE give me ORA-12028 error.

      Is there any way to resolve this?

      Thanks in advance:
      Bianca
        • 1. Re: ORA-12028
          HuaMin Chen
          ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot
          2 t_snap
          3 refresh fast
          4 as
          5 select * from t@ora734.world;
          select * from t@ora734.world
          *
          ERROR at line 5:
          ORA-12028: snapshot type is not supported by master site


          ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot
          2 t_snap
          3 refresh fast WITH ROWID
          4 as
          5 select * from t@ora734.world;

          Materialized view created.

          ops$tkyte@ORA817DEV.US.ORACLE.COM>
          • 2. Re: ORA-12028
            840275
            Thank you very much, but it still doesn't function.
            Neither creating snapshot with rowid, not creating materialized view with rowid from 11XE.
            The same code works well from 10g or 10XE, only at 11XE gives me ORA-12028 error.
            • 3. Re: ORA-12028
              HuaMin Chen
              Show your codes please
              • 4. Re: ORA-12028
                840275
                CREATE MATERIALIZED VIEW MY_SCHEMA.MY_MVIEW
                TABLESPACE MV_TABLESPACE
                PCTUSED 0
                PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                STORAGE (
                INITIAL 14M
                NEXT 1M
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                FREELISTS 1
                FREELIST GROUPS 1
                BUFFER_POOL DEFAULT
                )
                NOCACHE
                LOGGING
                NOCOMPRESS
                NOPARALLEL
                BUILD IMMEDIATE
                REFRESH FAST ON DEMAND
                WITH ROWID
                AS ....

                and comes a normal join containing also the rowids as columns.

                Edited by: 837272 on 30-abr-2012 6:52
                • 5. Re: ORA-12028
                  HuaMin Chen
                  Can you pls show the definitions (DDL) of all the relevant tables involved, to us? Thanks
                  • 6. Re: ORA-12028
                    840275
                    CREATE TABLE MY_10_SHEMA.TABLE1(
                    ID NUMBER(19) NOT NULL,
                    LINEPOSITION NUMBER(19),
                    USABLE NUMBER(1),
                    NAME VARCHAR2(255 CHAR),
                    ISDEFAULTINPRODUCT NUMBER(1),
                    PRODUCT_ID NUMBER(19) NOT NULL,
                    CUSTOMIZABLE_ID NUMBER(19)
                    )
                    TABLESPACE T1
                    PCTUSED 0
                    PCTFREE 10
                    INITRANS 1
                    MAXTRANS 255
                    STORAGE (
                    INITIAL 448K
                    MINEXTENTS 1
                    MAXEXTENTS UNLIMITED
                    PCTINCREASE 0
                    BUFFER_POOL DEFAULT
                    )
                    LOGGING
                    NOCOMPRESS
                    NOCACHE
                    NOPARALLEL
                    MONITORING
                    ENABLE ROW MOVEMENT;
                    • 7. Re: ORA-12028
                      HuaMin Chen
                      As per my interests, it is not a good practice to care about tablespace for the tables. Can you please adjust it first? Thanks