This discussion is archived
12 Replies Latest reply: Jan 21, 2013 4:38 AM by Carlovski RSS

materialized view which acts like a session

Naamas Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    No idea what you're asking.
  • 2. Re: materialized view which acts like a session
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It happends by Accident ,Please see the post above .
  • 4. Re: materialized view which acts like a session
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points