Sunday, March 8, 2015

What are major difference between Traditional Import-Export and Datapump Import-Export?


Traditional EXP/IMP both are separately executing.
Data Pump EXPDP and IMPDP are executed as DATAPUMP jobs. DB server processes access db files and db objects to perform EXP/IMP on respective directory objects of DB. This enforces a security model for DBAs.
Single data stream execution of  Traditional IMP/EXP.
EXPDP/IMPDP can take advantages of server’s parallel process to read or write multiple data streams simultaneously.
EXP/IMP not have very interactive command line mode.
EXPDP/IMPDP have very powerful interactive command line mode, allows user to monitor and control the process.
EXP/IMP cannot have disconnect and reconnect feature. Once started it need to be finish.
DATAPUMP jobs entirely running on server hence we can disconnect, detach and reconnect like, PAUSE and RESUME.
No data transfer between two databases in EXP/IMP.
DATAPUMP has ability to pass data between two databases over a database link, without creating a dump file on disk.
EXP/IMP uses sequential path access method.
DATAPUMP uses Direct Path access method.
EXP/IMP operates on single file.
DATAPUMP operates on group of files.
EXP/IMP can access files of server and client.
DATAPUMP exports/imports files using database directories.
Traditional EXP/IMP supports sequential media like tape drive and others.
DATAPUMP not supports on export/import from/to on Sequential media like tape drives.
EXP/IMP uses Byte by Byte mode.
DUMP Files can be Compressed, Encrypted and uses BLOCK by BLOCK mode.
EXP/IMP not supports for XML Schemas and XML Type data.
DATAPUMP Can support XML schemas and XML Type data.
EXP/IMP works with all oracle versions.
DATAPUMP works with oracle 10g or later versions.


Sunday, October 19, 2014

What are Materialized View & Materialized View Logs?

A materialized view is a database object that contains the results of a query same like a table. They are local copies of data located on remote site. Also, used to create summary tables based on aggregations of a table's data, which store data based on remote tables are also, referred 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 server as read-only data.If we want to update the local copies of data, we have to use the Advanced Replication features in oracle. We can select data from a materialized view same as local table or view.

Materialized Views refer local data from Materialized View Logs (Snapshot Logs), which need to create on Local tables.

CREATE MATERIALIZED VIEW LOG ON emp;

Primary Key Materialized Views which are created with "WITH PRIMARY KEY" clause.

CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE 
NEXT  SYSDATE + 1/48
WITH PRIMARY KEY 
AS SELECT * FROM emp@remote;

Rowid Materialized Views which are created with "ROWID" clause.

CREATE MATERIALIZED VIEW mv_emp_rowid
REFRESH WITH ROWID 
AS SELECT * FROM emp@remote;

Subquery Materialized Views which are created by using subquery.

CREATE MATERIALIZED VIEW  mv_empdept
AS SELECT * FROM emp@remote e
WHERE EXISTS
     (SELECT * FROM dept@remote d
     WHERE e.deptid = d.deptid)

For data warehousing purposes, materialized views are the aggregate views, single-table aggregate views and join views.

CREATE MATERIALIZED VIEW  mv_empdept_dwh
AS SELECT * FROM emp@remote a, dept@remote b
WHERE a.deptid = b.deptid
and a.deptid < 1000;

COMPLETE refreshes by recalculating the defining query of the materialized view.

FAST refreshes by incrementally applying changes to the materialized view as per materialized view logs. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient.

FORCE Attempts a FAST refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST and COMPLETE.