Skip to main content

Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema:

expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log

impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log

sql query to export and import a schema:

expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log

impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log

Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both.

To export meta data only to get ddl of the schemas:

expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics

To get the DDL in a text file:

impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfile=content120514.dat

You can open the sql.dat file in a text format to see the entire DDL of the schema exported.

To check the DATA_PUMP_DIR path:

To see the DATA_PUMP_DIR location on server, use below sql query

set lines 300
col DIRECTORY_PATH for a50
select * from dba_directories where directory_name='DATA_PUMP_DIR';

To change the directory path:

connect database as sysdba and give following query

CREATE OR REPLACE DIRECTORY test_dir AS '/backup_DB/';

For example:

expdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log

impdp schemas=schema directory=TEST_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log

Comments

  1. GopalWednesday, December 30, 2015 1:16:00 pm

    First, Thanks a lot.
    Nice Article. Was really helpful. Keep it up.

    ReplyDelete
  2. AnonymousWednesday, November 23, 2016 7:23:00 pm

    super thnx

    ReplyDelete
  3. Marco BorgesFriday, June 23, 2017 10:57:00 pm

    Hi my friend.

    I need to export schemmas in Oracle, using the expdp.

    My question:

    I can use the select,exemple.

    select owner, version , database
    from versionsdb
    where database='Model' ** in here model is a schemma model, without data, just estruture tables )

    Answer the select

    owner
    db01 | 12.1.1.0 | model
    db01 | 12.1.2.0 | model



    What I need. I need to generate the backup without me worrying that every time I have another version, I have to go in the script and add a line that informs the new model schema.

    ReplyDelete

Post a Comment

Popular posts from this blog

Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...
9 comments
Read more

krsv_proc_kill: Killing processes (Process by index) message in the alert log

Some times we'll get the following warning messages in the alert log of the database continuously. Alert log message: ============== WARN: ARC2: Terminating ARCH (pid 10111) hung on a disk operation Mon May 05 22:13:32 2014 krsv_proc_kill: Killing 266287972353 processes (Process by index) Mon May 05 22:20:32 2014 ARC2: Detected ARCH process failure ARC2: STARTING ARCH PROCESSES Mon May 05 22:20:32 2014 ARC1 started with pid=21, OS id=12104 ARC1: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE Mon May 05 22:20:41 2014 Deleted Oracle managed file /data/oracle/flash_recovery_area/ORCL/archivelog/2014_05_05/o1_mf_1_29497_9phnw343_.arc           Do not panic when you see the above message.  As long as there are no other side-effects this error can be ignored.  It occurs when disk IO & CPU load are high in the server.  Normally we are getting this warning messages when ever backup runs since disk io will be very high during...
Post a Comment
Read more