Generate sql file from EXPDP/IMPDP

It’s straight forward to generate DDL SQL Script from any specific schema. However, it has nothing to do with expdp.


expdp arvind/password schemas=hr directory=DPUMP dumpfile=HR_2014_08_13.dmp logfile=impsql_HR_2014_08_13.log


while importing you need to specify .sql file name using impdp, it will generate DDL SQL Script for any specific schema.

 [oracle@qpdbdev202 dpump]$ impdp arvind/password directory=DPUMP  dumpfile=HR_2014_08_13.dmp sqlfile=HR_2014_08_13.sql logfile=impsql_HR_2014_08_13.log

Comments

Post a Comment

Popular posts from this blog

chr function and its values - CHR and ASCII values

chr function returns the ascii letter for that integer. We know that there are 255 ascii characters defined. SQL> select chr(65) as CHR from dual; CHR – A Below code print all 255 ascii characters Sample code to check the values : begin   for i in 1..255 loop       dbms_output.put_line( 'CHR('||i||')' ||'=='|| chr(i) );     end loop;     end;     / This output might differ actually based on the chacterset you have choosen while installing you Database. DBMS_OUTPUT :    CHR(1)== CHR(2)== CHR(3)== CHR(4)== CHR(5)== CHR(6)== CHR(7)== CHR(8)== CHR(9)==    CHR(10)== CHR(11)== CHR(12)== CHR(13)== CHR(14)== CHR(15)== CHR(16)== CHR(17)== CHR(18)== CHR(19)== CHR(20)== CHR(21)== CHR(22)== CHR(23)== CHR(24)== CHR(25)== CHR(26)== CHR(27)== CHR(28)== CHR(29)== CHR(30)== CHR(31)== CHR(32)== CHR(33)==! CHR(34)==" CHR(35)==# CHR(36)==$ CHR(37)==% CHR(38)==& CHR(39)==' CHR(40)==( CHR(41)==) CHR(42...
Read more

IMPDP SHOW=Y, sqlfile=test.sql

Most of the legacy mode options will work in the data pump with its corresponding options. In legacy mode (imp utility) we have show=y option to spool the content of the dump file into sql scripts without doing the actual import. The same command can be used in data pump impdp also but it will get replaced with sqlfile option. oracle@Linux01:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Export: Release 11.2.0.4.0 - Production on Wed Feb 3 15:22:00 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=test1.dmp logfile=test1.log tables=TEST Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TA...
Read more

ORACLE FAL_CLIENT and FAL_SERVER explained

FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs. FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role. Sample setup: In Primary site: FAL_SERVER=STANDBY FAL_CLIENT=PRIMARY In Standby site: FAL_SERVER=PRIMARY FAL_CLIENT=STANDBY
Read more