An example is as follows:
1) This test is done with the scott.dept table
connect scott/tiger
2) If the dept table do not have primary key, add it using the following command:
alter table dept add constraint pk_dept primary key(deptno);
3) As a primary key exists on master table (dept), default materialized view log is
created with primary key. In case a primary key does not exist on the master table
an error ORA-12014 will be raised.
create materialized view log on dept;
4) Creation of materialized view ( default is primary key):
ORA-23415 will be raised in case the materialized view log do not record the
primary key
create materialized view s_dept refresh fast as select * from dept;
5) Perform insert in materialized view and commit it:
insert into dept (deptno) values (49);
commit;
If you select from the materialized view log you should see a row count of 1.
select count(*) from mlog$_dept;
1 row selected
6) Now we want to perform a reorganization of the master table
The below procedure will devalidate internal triggers which populates
the materialized view log when modification are done on the master table:
execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');
7) Now export the dept table:
exp scott/tiger tables=dept
8) Truncate the table to prepare it for import.
truncate table dept;
9) Import table dept.
imp scott/tiger tables=dept ignore=y
10) Turn on internals triggers.
execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');
11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.
execute dbms_mview.refresh('S_DEPT','f');
12) If the materialized view is a rowid materialized view, then the error
ORA-12034 will appear at refresh time, and the materialized view will need
a complete refresh.
execute dbms_mview.refresh('S_DEPT','c');
Source: Metalink ID 254593.1
Showing posts with label Replication. Show all posts
Showing posts with label Replication. Show all posts
Sunday, March 14, 2010
Tuesday, February 23, 2010
CDC Vs Streams
Oracle CDC is all about capturing changes in DB tables and the changes are stored in special Oracle tables. There are two modes of CDC operation: asynchronous (based on Java) or synchronous (based on DB triggers, more performance overhead).
Oracle Streams sits on top of Oracle CDC and it's a full transport mechanism (over e.g. HTTP) for data synchronization between 2 servers. It's based on Oracle Advanced Queues technology and it's designed for high performance and reliability.
Both Oracle CDC and Streams are generally used for data synchronization between Oracle DB servers... With Oracle CDC, you don't have to use Oracle Streams for, e.g. you could write your own data export routines which create flat files for the purpose of synchronization between 2 DB servers, whereas with Streams you must have a network link between the 2 servers.
Database Change Notification is something else again, it's not used for server-to-server synch but instead more for server notification of resultset changes to clients, mostly in the context of data caches on the client side
Oracle Streams sits on top of Oracle CDC and it's a full transport mechanism (over e.g. HTTP) for data synchronization between 2 servers. It's based on Oracle Advanced Queues technology and it's designed for high performance and reliability.
Both Oracle CDC and Streams are generally used for data synchronization between Oracle DB servers... With Oracle CDC, you don't have to use Oracle Streams for, e.g. you could write your own data export routines which create flat files for the purpose of synchronization between 2 DB servers, whereas with Streams you must have a network link between the 2 servers.
Database Change Notification is something else again, it's not used for server-to-server synch but instead more for server notification of resultset changes to clients, mostly in the context of data caches on the client side
Saturday, January 30, 2010
Snaplog and Snapshot script
-------------SNAPLOG SCRIPT ON SOURCE---------------------
CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH PRIMARY KEY;
---------------FOR ROW ID TABLES-----------
CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH ROWID;
-------------SNAPSHOT SCRIPT ON TARGET----------------
CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH PRIMARY KEY
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;
----FOR ROWID TABLES----
CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH ROWID
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;
CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH PRIMARY KEY;
---------------FOR ROW ID TABLES-----------
CREATE SNAPSHOT LOG ON TABLENAME
INITRANS 2 PCTFREE 1 TABLESPACE NFSNAPLOG NOLOGGING
WITH ROWID;
-------------SNAPSHOT SCRIPT ON TARGET----------------
CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH PRIMARY KEY
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;
----FOR ROWID TABLES----
CREATE SNAPSHOT TABLENAME
PCTFREE 0 TABLESPACE NFSNAPDATA NOLOGGING
USING INDEX
PCTFREE 5 TABLESPACE NFSNAPIDX
REFRESH FAST
START WITH sysdate
NEXT sysdate + 10 / (24 * 60)
WITH ROWID
AS SELECT * FROM NFADMIN.TABLENAME@DBLINK;
Materialized Views in Oracle
A materialized view 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. Materialized views, which store data based on remote tables are also, know as snapshots.
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Rowid Materialized Views
The following statement creates the rowid materialized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;
Materialized view log created.
Subquery Materialized Views
The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)
REFRESH CLAUSE
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The refresh option specifies:
The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed
Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
Refresh Method - FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations
Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.
Summary
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');
PL/SQL procedure successfully completed.
Parameters of Procedure REFRESH
The first parameter to the procedure REFRESH is the name of the materialized view or snapshot, the second parameter specifies the type of refresh.
Type of Refresh Description
F, f Fast Refresh
C, c Complete Refresh
A Always perform complete refresh
? Use the default option
The manual refresh overtakes any previous refresh timing options, which were specified during the creation of the view. It more specifically overrides the 'start with' clause, which is specified with the 'create materialized view' command.
Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, which were defined using the automatic refreshes.
SQL> execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.
Parameters of procedure REFRESH_ALL
The REFRESH_ALL procedure does not accept any parameters.
REFRESH GROUPS - CLUBBING RELATED VIEWS
Oracle provides the means by which you can group related views together. Oracle supplies the DBMS_REFRESH package with the following procedures;
MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group
DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');
my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');
my_group_1 now has three views in its group, mv_market_rate, mv_dealer_rate and mv_borrowing_rate ( the newly added view). All of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure SUBTRACT
Removes a snapshot/materialized view from the already existing refresh group.
SQL> execute DBMS_REFRESH.SUBTRACT(
name => 'my_group_1',
list => 'mv_market_rate');
my_group_1 now has two views in its group, mv_dealer_rate and mv_borrowing_rate. We have removed mv_market_rate from the refresh group, my_group_1.
DBMS_REFRESH - Procedure REFRESH
Manually refreshes the already existing refresh group.
SQL> execute DBMS_REFRESH.REFRESH(
name => 'my_group_1');
DBMS_REFRESH - Procedure CHANGE
The CHANGE procedure is used to change the refresh interval of the refresh group.
SQL> execute DBMS_REFRESH.CHANGE(
name => 'my_group_1',
next_date => NULL,
interval => 'sysdate+1/96');
The views in my_group_1 will now be refreshed at an interval of 15 minutes.
DBMS_REFRESH - Procedure DESTROY
Removes all materialized views from the refresh group and deletes the refresh group.
SQL> execute DBMS_REFRESH.DESTROY(
name => 'my_group_1');
Summary
A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).
For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view.
For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views.
In this article, we shall see how to create a Materialized View and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views.
Primary Key Materialized Views
The following statement creates the primary-key materialized view on the table emp located on a remote database.
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
Note: When you create a materialized view using the FAST option you will need to create a view log on the master tables(s) as shown below:
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Rowid Materialized Views
The following statement creates the rowid materialized view on table emp located on a remote database:
SQL> CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID
AS SELECT * FROM emp@remote_db;
Materialized view log created.
Subquery Materialized Views
The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database:
SQL> CREATE MATERIALIZED VIEW mv_empdept
AS SELECT * FROM emp@remote_db e
WHERE EXISTS
(SELECT * FROM dept@remote_db d
WHERE e.dept_no = d.dept_no)
REFRESH CLAUSE
[refresh [fast|complete|force]
[on demand | commit]
[start with date] [next date]
[with {primary key|rowid}]]
The refresh option specifies:
The refresh method used by Oracle to refresh data in materialized view
Whether the view is primary key based or row-id based
The time and interval at which the view is to be refreshed
Refresh Method - FAST Clause
The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.
Refresh Method - COMPLETE Clause
The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
Refresh Method - FORCE Clause
When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
PRIMARY KEY and ROWID Clause
WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
Rowid materialized views should have a single master table and cannot contain any of the following:
Distinct or aggregate functions
GROUP BY Subqueries , Joins & Set operations
Timing the refresh
The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes
SQL> CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 2
WITH PRIMARY KEY
AS SELECT * FROM emp@remote_db;
Materialized view created.
In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days.
Summary
- Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes.
- Manually Refreshing Materialized Views and Creating Refresh Groups in Oracle
- You can perform manual refreshes in addition to automatic refreshes as explained in my earlier article (Materialized Views). Oracle supplies DBMS_SNAPSHOT and DBMS_MVIEW packages, which we can use to refresh materialized views / snapshots.
SQL> execute DBMS_SNAPSHOT.REFRESH( 'MV_EMP','f');
PL/SQL procedure successfully completed.
Parameters of Procedure REFRESH
The first parameter to the procedure REFRESH is the name of the materialized view or snapshot, the second parameter specifies the type of refresh.
Type of Refresh Description
F, f Fast Refresh
C, c Complete Refresh
A Always perform complete refresh
? Use the default option
The manual refresh overtakes any previous refresh timing options, which were specified during the creation of the view. It more specifically overrides the 'start with' clause, which is specified with the 'create materialized view' command.
Also provided with DBMS_SNAPSHOT is the REFRESH_ALL procedure. This procedure refreshes all materialized views, which were defined using the automatic refreshes.
SQL> execute DBMS_SNAPSHOT.REFRESH_ALL;
PL/SQL procedure successfully completed.
Parameters of procedure REFRESH_ALL
The REFRESH_ALL procedure does not accept any parameters.
REFRESH GROUPS - CLUBBING RELATED VIEWS
Oracle provides the means by which you can group related views together. Oracle supplies the DBMS_REFRESH package with the following procedures;
MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group
DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');
my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');
my_group_1 now has three views in its group, mv_market_rate, mv_dealer_rate and mv_borrowing_rate ( the newly added view). All of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure SUBTRACT
Removes a snapshot/materialized view from the already existing refresh group.
SQL> execute DBMS_REFRESH.SUBTRACT(
name => 'my_group_1',
list => 'mv_market_rate');
my_group_1 now has two views in its group, mv_dealer_rate and mv_borrowing_rate. We have removed mv_market_rate from the refresh group, my_group_1.
DBMS_REFRESH - Procedure REFRESH
Manually refreshes the already existing refresh group.
SQL> execute DBMS_REFRESH.REFRESH(
name => 'my_group_1');
DBMS_REFRESH - Procedure CHANGE
The CHANGE procedure is used to change the refresh interval of the refresh group.
SQL> execute DBMS_REFRESH.CHANGE(
name => 'my_group_1',
next_date => NULL,
interval => 'sysdate+1/96');
The views in my_group_1 will now be refreshed at an interval of 15 minutes.
DBMS_REFRESH - Procedure DESTROY
Removes all materialized views from the refresh group and deletes the refresh group.
SQL> execute DBMS_REFRESH.DESTROY(
name => 'my_group_1');
Summary
- Creating a refresh group helps to club all related views together and thus refreshes them together. Manual refresh gives us an opportunity to override the automatic refresh settings.
- USER_MVIEWS describes all materialized views owned by the current user. Its columns are the same as those in ALL_MVIEWS.
Subscribe to:
Posts (Atom)