Basic Replication -- 1 : Introduction

Basic Replication, starting with Read Only Snapshots has been available in Oracle since  V7.   This was doable with the "CREATE SNAPSHOT" command.

In 8i, the term was changed from "Snapshot" to "Materialized View"  and the "CREATE MATERIALIZED VIEW" command was introduced, while "CREATE SNAPSHOT" was still supported.

Just as CREATE SNAPSHOT is still available in 19c,  DBMS_SNAPSHOT.REFRESH is also available.

Not that I recommend that you use CREATE SNAPSHOT and DBMS_SNAPSHOT anymore.  DBAs and Developers should have been using CREATE MATERIALIZED VIEW and DBMS_REFRESH since 8i.

In the next few blog posts (this will be a very short series) I will explore Basic Replication.  Let me know if you want to see it in 11.2 and 12c as well.


EDIT :  As I had only presented a screen-shot earlier, I am pasting in the commands from an SQLPlus session here :

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Aug 18 16:19:21 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter user-name: hemant/hemant@orclpdb1
Last Successful login time: Sun Aug 18 2019 16:17:48 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table source_table as select * from dba_objects;

Table created.

SQL> create snapshot mv_of_source as select * from source_table;

Materialized view created.

SQL> select count(*) from source_table;

  COUNT(*)
----------
     72355

SQL> select count(*) from mv_of_source;

  COUNT(*)
----------
     72355

SQL> insert into source_table select * from source_table;

72355 rows created.

SQL> dbms_snapshot.refresh('MV_OF_SOURCE');
SP2-0734: unknown command beginning "dbms_snaps..." - rest of line ignored.
SQL> execute dbms_snapshot.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> select  count(*) from mv_of_source;

  COUNT(*)
----------
    144710

SQL> select object_id, object_type from user_objects where object_name = 'MV_OF_SOURCE';

 OBJECT_ID OBJECT_TYPE
---------- -----------------------
     73051 TABLE
     73052 MATERIALIZED VIEW

SQL> drop materialized view mv_of_source;

Materialized view dropped.

SQL> select object_id, object_type from user_objects where object_name = 'MV_OF_SOURCE';

no rows selected

SQL>

As you can see, even if I use the "CREATE SNAPSHOT" command, the data dictionary shows that it is a Materialized View.  OBJECT_ID 73051 is the actual table created by Oracle to "store" the rows of the Materialized View while 73052 is the definition of the Materialized View.