Hello friends In this article, we going to discuss how to import view only using impdp from the old dump file.
How to import view only from export backup
Someone asked me how to restore a single view or all view only using impdp from any dump file. So my suggestion is below.
I created three views in Scott user using the following queries and take export backup using expdp.
How to create view in oracle
SQL> create view xyz as select * from emp; SQL> create view abc as select ename,sal from emp; SQL> create view xxx as select job,deptno from emp;
Take Export backup
Using below command we are going to take export backup of a schemas scott.
expdp schemas=scott directory=data_pump_dir dumpfile=scott.dmp logfile=scott.log
Import view only
Now I’m going to import view only from the above dump file using impdp on production database.
impdp SCHEMAS=SCOTT directory=data_pump_dir dumpfile=SCOTT.dmp include=view sqlfile=abc.txt
The above query we use two parameters includes and sqlfile in include parameter we set view keyword-only which is restore view only and in the second parameter we use sqlfile which helps us to generate a metadata file, with the help of this metadata file we can create view again on production.
How to import a specific view?
Using the below query you can get a specific view on production directly.
impdp SCHEMAS=SCOTT directory=data_pump_dir dumpfile=SCOTT.dmp include=view:\"=\'XYZ\'\" sqlfile=abc.txt
In the above query, we are imported a specific view name is XYZ.
Hope you found this article informative.
Connect with me on: