Oracle Materialized View

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 VERSION QUERY

FLASHBACK VERSION QUERY

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

Flashback version query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description  VARCHAR2(50));

Table created.

SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

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(SYSTIMESTAM
-----------   -------------------
    4991867    2021-07-20 22:29:52

SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.


SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.

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

CURRENT_SCN  TO_CHAR(SYSTIMESTAM
-----------  -------------------
    4992195  2021-07-20 22:34:30


SQL>  SELECT versions_startscn, versions_starttime,versions_endscn, versions_endtime, 
versions_xid, versions_operation, description from flashback_version_query_test 
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2021-07-20 22:29:52', 'YYYY-MM-DD HH24:MI:SS') 
AND TO_TIMESTAMP('2021-07-20 22:34:30', 'YYYY-MM-DD HH24:MI:SS')WHERE  id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
---------- --------------------- ------------------ -------------- -----------  -----------
 4992172   20-JUL-21 10.33.41  PM  0D0020001B040000   U THREE                                            
4992136    20-JUL-21 10.32.23  PM   0B00050021040000  U TWO
4992172    20-JUL-21 10.33.41  PM                       ONE



SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, 
versions_xid, versions_operation, description FROM  flashback_version_query_test
VERSIONS BETWEEN SCN  4991867 and 4992195 WHERE  id = 1;  

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
 4992172  20-JUL-21 10.33.41 PM                        0D0020001B040000 U THREE
 4992136 20-JUL-21 10.32.23 PM     4992172 20-JUL-21 10.33.41 PM    0B00050021040000 U TWO
                                                       4992136 20-JUL-21 10.32.23 PM                       ONE



SQL>  SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM   
flashback_transaction_query WHERE  xid = HEXTORAW('06000000FA030000');

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

UNDO TABLESPACE

Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.

Undo tablespace check undo tablespace:
SQL> show parameter undo_tablespace;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

Check to undo retention:

SQL> show parameter undo_retention;

NAME                                    TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer            900

Check to undo management:

SQL> show parameter undo_management;

NAME                                     TYPE                   VALUE
------------------------------------ ----------- ------------------------------
undo_management                          string                  AUTO

create new undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORACLEAGENT/undo2.dbf' size 100m;

Tablespace created.

change undo tablespace

SQL> alter system set undo_tablespace='undotbs2' scope=spfile; 
System altered.

SQL> show parameter undo_tablespace;

NAME                                   TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes
 Database mounted.
Database opened.
SQL> show parameter undo_tablespace;

NAME                                   TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string           undotbs2 



change undo_retention

SQL> show parameter undo_retention;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer             900

SQL> alter system set undo_retention=920;
System altered.

SQL> show parameter undo_retention; 

NAME                                    TYPE           VALUE
------------------------------------ ----------- ------------------------------
undo_retention                        integer           920 

check db block size

SQL> show parameter db_block_size;

NAME                                     TYPE              VALUE
------------------------------------ ----------- ------------------------------
db_block_size                            integer           8192

change undo management { auto | manual }

SQL> show parameter undo_management;
 NAME                                  TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_management                       string             MANUAL

SQL> alter system set undo_management='auto' scope=spfile; 
System altered.

SQL> shut immediate; 
Database closed.
Database dismounted. 
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes 
Database mounted.
Database opened.

SQL> show parameter undo_management;

NAME                                   TYPE            VALUE
------------------------------------ ----------- ------------------------------
undo_management                        string         AUTO
Undo stages:
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, 
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;

TABLESPACE                      STATUS    SUM_IN_MB   COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1                        EXPIRED      1399.25     200
UNDOTBS1                        UNEXPIRED    168         21
UNDOTBS2                        EXPIRED      .375        6
UNDOTBS2                        UNEXPIRED     5.4375     42

Active, expired and unexpired transaction space usage in Undo Tablespace:-

ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.

EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.

UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.

Free blocks : At the time of creating new undo tablespace shows empty blocks

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