5 Replies Latest reply: Nov 19, 2012 12:10 PM by User374238 RSS

    Problem creating a Materialized View

    User374238
      Good afternoon, everybody.

      I'm trying to create a materialized view using two or more dblinks.

      Something like this:
      Select * from scott.dep@db_x, scott.emp@db_x;

      When I try to create the materialized view with only one remote table, it works fine, but when I put the second
      table, it starts to give the error ORA-02019.

      Does anyone have a suggestion about what might be causing this error?

      Regards.
        • 1. Re: Problem creating a Materialized View
          967829
          Hi there!

          My first guess would be that there may be some missing access rights from the schema you are using. Check the schema from which you are creating the view and make sure that it has at least "select" access to both of the referenced tables.

          My second guess would be that there may be a table alias that is not recognized. Make sure you are accessing the linked tables using their proper names from their home schema, rather than a synonym.
          • 2. Re: Problem creating a Materialized View
            Sg049
            Not sure why are you getting the errors.join condition missing?

            try this

            1.Try granting select privs on tables on remote site to current site.

            2.Try specifying the columns names instead of '*'

            3.Create a view(Select col1,col2 from scott.dep a, scott.emp b where a.deptno=b.deptno ) on remote site and access it.
            • 3. Re: Problem creating a Materialized View
              John Spencer
              The ORA-02019 error message says "connection description for remote database not found". Are you sure that you are using the same db link for both remote tables as your posting shows?

              What happens when you query the table with the db link that causes the error on its own?

              John
              • 4. Re: Problem creating a Materialized View
                User374238
                I tried all those suggestions, but without success.

                I was able to create a normal view with the dblink, but when I try to create
                a Materialized view, the problem persists.

                I'm using this parameters to create the Materialized View:

                CREATE MATERIALIZED VIEW Billy.Test
                TABLESPACE TS_test_DATA
                PCTUSED 0
                PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                STORAGE (
                INITIAL 64K
                NEXT 1M
                MINEXTENTS 1
                MAXEXTENTS UNLIMITED
                PCTINCREASE 0
                BUFFER_POOL DEFAULT
                FLASH_CACHE DEFAULT
                CELL_FLASH_CACHE DEFAULT
                )
                NOCACHE
                NOLOGGING
                NOCOMPRESS
                NOPARALLEL
                BUILD IMMEDIATE
                REFRESH FORCE
                START WITH TO_DATE('12-nov-2012 12:35:29','dd-mon-yyyy hh24:mi:ss')
                NEXT SYSDATE + 1/24
                WITH PRIMARY KEY

                Does any one have more suggestions?

                Thanks for the help so far.

                Regards...
                • 5. Re: Problem creating a Materialized View
                  User374238
                  Problem solved,

                  I logged in as the owner schema of the view and was able to create it.

                  Thanks for the help.