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.
|
Tips and good resources for all.. Oracle, Big data, Hadoop, Unix, Linux, Cloud, etc.
Sunday, March 8, 2015
What are major difference between Traditional Import-Export and Datapump Import-Export?
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.
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.
Subscribe to:
Comments (Atom)