12 Replies Latest reply: Jan 21, 2013 6:38 AM by Carlovski RSS

    materialized view which acts like a session

    Naamas
      Hi all .
      Is there a way to create mview that acts like a session? i'll explain :
      i'm connecting to a session (let's call this session , session 1 )
      sqlplus naama/naama@passwd 
      select emp_id   , emp_name  from emp;
      emp_id      emp_name 
      -----------  -----------------
      1           Naama 
      2           Alice
      3           Alexandra 
      
      update emp set emp_name = 'ROY' where emp_id  = 1 ;
      
      select emp_id   , emp_name  from emp;
      
      emp_id      emp_name 
      -----------  -----------------
      1           ROY 
      2           Alice
      3           Alexandra 
      Because i'm in the same session , when i'm doing an update , without a commit i will still see the data after the update (=ROY and not Naama ) .
      Is there Any mview that i can declare that will be refreshed when i'm making a changes to the base table , means mview that will acts as if it was a select
      inside the same session ,
      Please Your Help
      Naama
        • 1. Re: materialized view which acts like a session
          SomeoneElse
          No idea what you're asking.
          • 2. Re: materialized view which acts like a session
            sb92075
            Naamas wrote:
            Hi all .
            Is there a way to create mview that acts like a session? i'll explain :
            i'm connecting to a session (let's call this session , session 1 )
            sqlplus naama/naama@passwd
            select emp_id , emp_name from emp;
            emp_id emp_name
            ----------- -----------------
            1 naama
            2 Alice
            3
            How do I ask a question on the forums?
            SQL and PL/SQL FAQ
            • 3. Re: materialized view which acts like a session
              Naamas
              It happends by Accident ,Please see the post above .
              • 4. Re: materialized view which acts like a session
                sb92075
                Naamas wrote:
                It happends by Accident ,Please see the post above .
                other sessions can NOT see uncommitted data.
                • 5. Re: materialized view which acts like a session
                  Naamas
                  sorry , for the post that was incompleted , i did edited it but i guess that it wasn't understood so here is the scenario again :

                  Hi all .
                  Is there a way to create mview that acts like a session? i'll explain :
                  i'm connecting to a session (let's call this session , session 1 )
                  sqlplus naama/naama@passwd 
                  select emp_id   , emp_name  from emp;
                  emp_id      emp_name 
                  -----------  -----------------
                  1           Naama 
                  2           Alice
                  3           Alexandra 
                   
                  update emp set emp_name = 'ROY' where emp_id  = 1 ;
                   
                  select emp_id   , emp_name  from emp;
                   
                  emp_id      emp_name 
                  -----------  -----------------
                  1           ROY 
                  2           Alice
                  3           Alexandra 
                  Because i'm in the same session , when i'm doing an update , without a commit i will still see the data after the update (=ROY and not Naama ) .
                  Is there Any mview that i can declare that will be refreshed when i'm making a changes to the base table , means mview that will acts as if it was a select
                  inside the same session ?
                  Any other closer solution will be more than welcome
                  Please Your Help
                  Naama

                  Edited by: Naamas on 08:02 20/01/2013

                  Edited by: Naamas on 08:24 20/01/2013
                  • 6. Re: materialized view which acts like a session
                    Solomon Yakobson
                    Still not clear. Are you looking for a way for session B to see session A uncommitted changes? So if session A updates emp_name from Naama to Roy but not commits the change session B will be able to query some other table/mview and see Roy not Naama? If so, why? This goes compliteley against transactional logic.

                    SY.
                    • 7. Re: materialized view which acts like a session
                      Naamas
                      Hi Solomon
                      Thanks for the response
                      you asked : "Are you looking for a way for session B to see session A uncommitted changes? " - NO
                      I'm looking for a Mview that will see session A uncommitted changes .
                      What is the goal ?
                      Let me explain maybe there is other solution ;
                      i'm trying to implemet an solution for my customer like this ;
                      (The names below are just an example and not the real name of course ),
                      my customers have a db called db1
                      schema : prod
                      tables : employees, jobs etc.the customer doing on this tables a dml and select operations.
                      in my organization i have a db called db2
                      schema : prodcomp
                      i want to create MVIEW AS THE Same name like the base table in my customers db : db1

                      schema : prod
                      Mview called : employees, jobs etc.
                      the goal is that when the customer doing a select in his schema prod on table employees ,
                      it will point on my mview employees in my  schema prodcomp , because they need to use my system, and my mview which use a functions ,
                      so i want that if the customer is doing a dml commands on his base table in his schema ' i have to make sure that my mview ,
                      is refresh dedicated to his changes so if is trying to do an update and i have a constraint on my mview which
                      violates its update i want to give him a message to this . and of course if i'm doing mview refresh on commit i'm not getting what i want to achive ,
                      HOpe this is clear enough ,
                      Please A solution with mview,
                      By the way the customer won;t be able to do any dml commands on my MVIEW
                      Thanks For The Patience
                      Naama
                      • 8. Re: materialized view which acts like a session
                        Toon Koppelaars
                        Yes.

                        Either, use a normal view, instead of a materialized view,

                        Or, execute the appropriate dbms_mview procedure, to refresh your mview in your session, right after the underlying dependent data was changed by your update.
                        • 9. Re: materialized view which acts like a session
                          Naamas
                          Hi Toon ,
                          thanks for your response ,
                          I do not want to use a normal view because i need to create indexes and constraint on the view like in the base table.
                          can you donate me an example of which procedure inside the dbms_mview do i need to use ?
                          important note:
                          I'm about to create something like 15 mview , in all of those view i want that they will refresh , right after the underlying dependent data was changed by an update / insert/ delete and of course , when it changed on commit or rollback.
                          SQL> create user naama
                            2    identified by naama
                            3    default tablespace users
                            4    quota unlimited on users
                            5  / 
                           
                          User created.
                           
                          SQL> grant create session,
                            2        create table,
                            3        create procedure,
                            4        create materialized view
                            5    to naama
                            6  / 
                           
                          Grant succeeded.
                           
                          SQL> create user test
                            2    identified by test
                            3    default tablespace users
                            4    quota unlimited on users
                            5  / 
                           
                          User created.
                           
                          SQL> grant create session,
                            2        create table,
                            3        create materialized view
                            4    to test
                            5  / 
                           
                          Grant succeeded.
                           
                          SQL> connect naama@orcl/naama
                          Connected.
                          SQL> create table users(
                            2                     user_id               NUMBER,
                            3                     user_name             VARCHAR2(15 CHAR),
                            4                     user_password         VARCHAR2(50 CHAR),
                            5                     CONSTRAINT user_pk PRIMARY KEY(user_id)
                            6                    )
                            7  / 
                           
                          Table created.
                           
                          SQL> grant select,
                            2        on commit refresh
                            3    on users
                            4    to test
                            5  / 
                           
                          Grant succeeded.
                           
                          SQL> create or replace
                            2    function get_name(
                            3                      user_name_in IN varchar2
                            4                     )
                            5      return varchar2
                            6           is
                            8          v_name varchar2(10);
                            9      begin
                           10          select  'NAAMA'
                           11            into  v_name
                           12            from  naama.users
                           13            where rownum = 1;
                           14          return v_name;
                           15  end get_name;
                           16  / 
                           
                          Function created.
                           
                          SQL> grant execute
                            2    on get_name
                            3    to test
                            4  / 
                           
                          Grant succeeded.
                           
                          SQL> create materialized view log
                            2    on users
                            3    with rowid
                            4  / 
                           
                          Materialized view log created.
                           
                          SQL> grant all
                            2    on mlog$_users
                            3    to test
                            4  / 
                           
                          Grant succeeded.
                           
                          SQL> connect test@orcl/test
                          Connected.
                          SQL> create materialized view users_mv
                                   BUILD IMMEDIATE
                                   REFRESH FORCE ON DEMAND
                                   WITH rowid 
                                   as
                                   select  user_name,
                                            user_password,
                                            naama.get_name(user_name) first_name
                                      from  naama.users
                          question :
                          If i'm using the dbms_mview , when i'm creting the mview do i need to assign the REFRESH FORCE ON DEMAND . or the dbms_mview solve this issue ?
                          Please you help
                          Thanks In Advanced
                          Naama

                          Edited by: Naamas on 01:05 21/01/2013

                          Edited by: Naamas on 01:44 21/01/2013
                          • 10. Re: materialized view which acts like a session
                            Billy~Verreynne
                            It sounds like you want to see a table with committed data, and the table with uncommitted data that the current session created - in the same session.

                            This can easily be done by creating a consistent read cursor on the table first, and then make the uncommitted changes. The consistent read will not see uncommitted changes from its session - as the read was done prior to the current session making those changes.

                            Work-around - assuming you want to open this read consistent cursor after changes have been made, without seeing that session's uncommitted changes. Create a database "loopback" link to the same schema - i.e. the database link refers to the same server and database and schema. Now select that table via the database link. This creates a brand new session in the same database and schema - one that cannot see uncommitted changes in your current session.

                            However. It also makes little sense. What is the requirement behind this request?
                            • 11. Re: materialized view which acts like a session
                              Naamas
                              Hi billy thanks for the response ,
                              What is the requirement behind this request?
                              I posted it above ' i'll be happy if you take a look there.
                              My goal is that the Mview will be dedicated to every change in the base table whether it's commited or not,
                              I'm updating That we decided that the customer will connect to our databse and schema , so there is no need to do the dblink.
                              The Mview are compound from various function , and we decided to go for this kind of solution.
                              How can i implement you solution , I'm trying to understand HOw can i use it.
                              I also saw something called PARALLEL Attribute of the Materialized View.
                              Can this help me ?
                              my Oracle d.b is
                              -----------------------------
                              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                              THanks
                              Naama

                              Edited by: Naamas on 02:11 21/01/2013
                              • 12. Re: materialized view which acts like a session
                                Carlovski
                                If such a thing were possible, and I cannot see how (And if it were, how would this 'view' handle update conflicts?)
                                it would also have some serious performance impact on the source database.
                                I guess theoretically it may be possible to try and use something like logminer to do this and get the data directly from redo, would be one hell of a job though.

                                Carl