SELECT current_scn - ORACLEAGENT BLOG

Oracle Materialized View

A materialized view in Oracle is a database object that contains the results of a 
query.They are local copies of data located remotely, or are used to create summary 
tables based on aggregations of a table’s data.

A materialized view can query tables, views, and other materialized views.
A materialized view, or snapshot as they were previously known, is a table segment 
whose contents are periodically refreshed based on a query, either against a local 
or remote table.

Syntax

CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE] AS SELECT …;

Methods

BUILD

IMMEDIATE : The materialized view is populated immediately.This option is default one.
DEFERRED : The materialized view is populated on the first requested refresh.

Refresh Types

FAST: A fast refresh is attempted only there is a change in the base table. If materialized view logs are not present against the source tables in advance, the creation fails. To maintain the history of change in the base table, it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in the source database in the same schema as the master table. Refresh fast will perform refresh according to the changes that occurred in the master table.
COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.

ON COMMIT : The refresh is triggered by a committed data change in one of the 
dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.

GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.

Creating Database link to point remote database 

SQL> CREATE DATABASE LINK PROD_LINK CONNECT TO ph IDENTIFIED BY ph USING 'orcl';

Database link created.

COMPLETE REFRESH

Creating Materialized View

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.

SQL> select * from emp;

        NO NAME                     SALARY
---------- -------------------- ----------
         1 apple                     20000
         2 orange                    23000
         3 mango                     32000

Table Data present in Remote database

SQL> insert into emp values (6,'ice',30000);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAA          1 apple                     20000
AAAR36AABAAAbURAAB          2 orange                    23000
AAAR36AABAAAbURAAC          3 mango                     32000

Manually using complete refresh using the DBMS_VIEW package

SQL>  execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAD          1 apple                     20000
AAAR36AABAAAbURAAE          2 orange                    23000
AAAR36AABAAAbURAAF          3 mango                     32000
AAAR36AABAAAbURAAG          6 ice                       30000

DEFERRED

Initially drop the existing materialized view

SQL> drop MATERIALIZED VIEW dept_mv;

Materialized view dropped.

Materialized view creation using DEFERRED option

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.


SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

QL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR39AABAAAbURAAA          1 apple                     20000
AAAR39AABAAAbURAAB          2 orange                    23000
AAAR39AABAAAbURAAC          3 mango                     32000
AAAR39AABAAAbURAAD          6 ice                       30000

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

FLASHBACK CONCEPTS

FLASHBACK VERSION QUERY

FLASHBACK DROP TABLE

FLASHBACK QUERY

FLASHBACK TABLE

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

FLASHBACK QUERY Concepts

FLASHBACK QUERY

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality.

SQL> create table flashback_query_test  (id  number(10));

Table created.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')FROM v$database;

    CURRENT_SCN               TO_CHAR(SYSTIMESTAMP
 --------------            -------------------------
   4933950                     2021-07-20 00:02:36

SQL> INSERT INTO flashback_query_test (id) VALUES (1);

1 row created.

SQL> commit;
  
Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN      TO_CHAR(SYSTIMESTAMP
---------------        --- ---------------------
    4934857                     2021-07-20 00:10:15

SQL> SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

SOL> SELECT COUNT(*) FROM   flashback_query_test AS OF SCN 4933950;

 COUNT(*)
----------
     1

SOL> SELECT COUNT(*) FROM   flashback_query_test AS OF SCN 4934857 ;

 COUNT(*)
----------
      0

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

FLASHBACK TABLE

FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error.

The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

SQL> CREATE TABLE flashback_table_test (id  NUMBER(10));

Table created.

SQL> ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

Table altered.




SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993733

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN          TO_CHAR(SYSTIMESTAMP
-----------         -------------------------
    4993746           2021-07-20 23:18:30

SQL> INSERT INTO flashback_table_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993843

SQL>FLASHBACK TABLE flashback_table_test TO SCN 4993733;

Flashback complete.

SQL> SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
      0

SQL> FLASHBACK TABLE flashback_table_test TO SCN 4993843;
  
Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

SQL> FLASHBACK TABLE flashback_table_test TO TIMESTAMP 
TO_TIMESTAMP(' 2021-07-20 23:18:30', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
     0

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Tag Archives: SELECT current_scn

Oracle Materialized View

Syntax

Methods

BUILD

Refresh Types

COMPLETE REFRESH

Creating Materialized View

Table Data present in Remote database

Manually using complete refresh using the DBMS_VIEW package

DEFERRED

Initially drop the existing materialized view

Materialized view creation using DEFERRED option

FLASHBACK QUERY

FLASHBACK TABLE