A materialized view stores both the definition of a view and the rows resulting from the execution of the view
BEFORE CREATING MATERIALIZED VIEW WE NEED PROPER TNS , LISTENER COPNFIGURATION AND DB LINK
(HERE -tesdb_db_link )
WE HAVE TWO DIFFERENT DATABASES AND TO SHOW THE STEPS IN EACH DATABASE A DIFFERENTIATION IS SHOW BELOW
DEV DATABASE TESDB DATABASE
| DEV DATABASE | TESDB DATABASE |
|---|
| |
STEP 1 :
SQL> conn / as sysdba
Connected.
SQL> grant create materialized view to u1;
Grant succeeded.
|
| |
STEP 2 : CONNECT AS USER AND CREATE
A MATERIALIZED VIEW
SQL> conn u1/u1
Connected.
SQL> create materialized view mv1 build immediate refresh
force on demand as
select * from tesdb_db_link@dbl;
Materialized view created.
|
| | STEP 3 :
SQL> select * from mv1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
|
STEP 4 :
select * from tesdb_db_link;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
| |
STEP 5 : INSERTING VALUES UDING DB LINK
insert into tesdb_db_link
select * from tesdb_db_link;
8 rows created.
SQL> select * from tesdb_db_link;
ID
----------
1
2
3
4
5
1
2
3
4
5
6
ID
----------
7
8
6
7
8
16 rows selected.
| |
| | STEP 6 : NEW ADDED VALUES ARE NOT
AVAILBLE IN MAIN TABLE.
SQL> select * from mv1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
|
STEP 7 : COMMIT THE TRANSACTION
SQL> commit;
Commit complete.
| |
| |
STEP 8 : EVEN AFTER COMMINTING THE
TRANSACTION , THE VALUES ARE NOT AVAILABLE
SQL> select * from mv1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
|
| | STEP 9 : REFRESH THE MATERIALIZED VIEW
SQL> exec dbms_mview.refresh('MV1');
PL/SQL procedure successfully completed.
SQL> select * from mv1;
ID
----------
1
2
3
4
5
1
2
3
4
5
6
ID
----------
7
8
6
7
8
16 rows selected.
|
| |
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
----- ------- ---------
MV1 TABLE
|
| | STEP 10 : DATABASE LINK
SQL> select * from tesdb_db_link@dbl;
ID
----------
1
2
3
4
5
1
2
3
4
5
6
ID
----------
7
8
6
7
8
16 rows selected.
SQL>
|
| | STEP 11 : Materialized View - information
SQL> select mview_name, query,
refresh_method from user_mviews;
MVIEW_NAME QUERY MV1
--------- ------ ---
select * from tesdb_db_link@dbl
FORCE
|
| | STEP 12 : DROP MATERIALIZED VIEW:
SQL> drop materialized view mv1;
Materialized view dropped.
|
