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 fromdept_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.
[oratest@oracle ~]$ impdp help=y
Import: Release 19.0.0.0.0 - Production on Mon Aug 9 22:52:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.
ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH,
EXTERNAL_TABLE, and INSERT_AS_SELECT.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].
CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT, ENABLE_NETWORK_COMPRESSION,
REJECT_ROWS_WITH_REPL_CHAR, SKIP_CONSTRAINT_ERRORS, CONTINUE_LOAD_ON_FORMAT_ERROR,
TRUST_EXISTING_TABLE_PARTITIONS and VALIDATE_TABLE_DATA.
DIRECTORY
Directory object to be used for dump, log and SQL files.
DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo is suppressed while standard input is read.
ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Import everything from source [YES].
HELP
Display help messages [NO].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of import job to create.
KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].
LOGFILE
Log file name [import.log].
LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
MASTER_ONLY
Import just the master table and then stop the job [NO].
METRICS
Report additional job information to the import log file [NO].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [NO].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file.
PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REMAP_DATAFILE
Redefine data file references in all DDL statements.
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].
SCHEMAS
List of schemas to import.
SERVICE_NAME
Name of an active service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION
Edition to be used for extracting metadata.
SQLFILE
Write all the SQL DDL to a specified file.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to import.
TARGET_EDITION
Edition to be used for loading metadata.
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.
TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_TABLESPACES
List of tablespaces from which metadata is loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name is appended
to the view name.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.
STOP_WORKER
Stops a hung or stuck worker.
TRACE
Set trace/debug flags for the current job.
SQL> create or replace directory dump_dir as '/home/oracle/backup';
Directory created.
[oracle@oracle ~]$ impdp directory=dump_dir dumpfile=india.dmp logfile=india.log
tables=india.apple
Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: test
Password:
The ATTACH parameter is used to attach the interrupted job and status of data pump operation.
[oracle@oracle ~]$ impdp system/oracle attach=SYS_EXPORT_SCHEMA_01
Export: Release 19.0.0.0.0 - Production on Mon Aug 20 22:19:02 2018
Version 19.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM
Remap Table
[oracle@oracle ~]$ impdp DIRECTORY=dumpdir DUMPFILE=india_table.dmp logfile=india_table1.log TABLES=india.apple REMAP_TABLE=india.apple:mango
Username: system
Password:
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "INDIA"."MANGO" 5.609 KB 8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "INDIA"."SYS_IMPORT_TABLE_01" successfully completed at Wed Aug 4 21:49:48 2021 elapsed 0 00:00:19
Remap_schema
[oracle@oracle ~]$ impdp directory=dumpdir dumpfile=india_owner.dmp logfile=india_owner1.log remap_schema=tamil:english
Username: system
Password:
. . imported "ENGLISH"."T1" 5.539 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:24:10 2021 elapsed 0 00:00:17
Remap_Tablespace
[oracle@oracle backup]$ impdp directory=dumpdir dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Username: system
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dumpdir dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:56:25 2021 elapsed 0 00:00:02
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:
Export(exp) and import(imp) are Logical backup and Recovery. When exporting the database objects are dumped to a binary file which can then be imported into another Oracle database.
The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. First, let us see export in detail.
Difference between exp/imp and expdp/impdp
Exp/imp is a Traditional export import,expdp/impdp is a Datapump export
import.
Traditional export import utility is starting with exp/imp,Datapump utility
is starting with expdp/impdp.
Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
exp/imp (Traditional) is using conventional path , expdp /impdb (Datapump) is using Direct path.
Exp (Traditional) is byte mode ,Datapump is block mode.
Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.
Datapump utility we can stop and Restart the Jobs.
Features of Datapump utility
Job Estimation can be Done in Datapump.
Data Remapping can be done using REMAPDATA parameter.
EXCLUDE and INCLUDE parameter allows the fine-grained object selection.
Failed export/import Jobs can be Restarted.
Export and import can be taken over the network using database links even without Generating the dump file using NETWORK_LINK parameter.
CONTENT parameter gives the freedom for what to export with options METADATA ONLY, DATA, BOTH.
You don’t need to specify the BUFFER size in datapump
Job estimated completion time can be monitored from v$session_longops view.
Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you have to compress the dumps using OS utilities.
Data encryption can be done in datapump.
DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.
REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.
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:
