Avatar of pavan27
pavan27Flag for United States of America asked on

schema level export or expdb in oracle


i need some information regarding export /expdp at schema level.  let me explain the senerio.

i have one database. in that i need to take export/expdp of one schema. after taking the export /expdp i have to drop the database.
after droping the database i need to refresh the database from production. upon completion of refresh from prod i have to import the export/expdp dump file in the newly refreshed database. can any one suggest me work around on this.

while taking export or expdp what are the considerations i have to take care.
while importing the dump file on newly created database , do i get any path problems like(dbf path/ control file path/ redolog file path)
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon

I'm unsure of your hesitations on this point.

What's wrong with the following:
- expdp system/password schemas=HR dump_dir=direcotry dump_file=file.dmp
- drop the database
-  create the new database with the appropriate paths
- import the schema using the example above with impdp

newly created database would come with different path. that is the problem i am facing.

-  create the new database with the appropriate paths  are you talking about same old path. or new path?
if it is old path it is not work for me. because we are change old path to new path.

David VanZandt

What type of refresh are you planning?  I infer you mean to shut down production, and copy the datafiles, redo, etc. from the prod host to another, such as QA -- at which level you would have to bring up the copied database with the same ORACLE_SID, and rename the datafiles in the controlfiles.  Also, the schema you want to preserve would be invalidated in this approach.  

You may copy datafiles between like operating systems, and recreating your controlfile is not difficult.  It really helps to ensure you have the text backup from prod, though:  ALTER DATABASE BACKUP CONTROLFILE TO TRACE; then edit your trace file, and execute it as SQL.

Also you may consider keeping the target database by moving the datafiles and altering the database with their new path(s).  Drop the schema(s) that don't belong, perhaps.  You'll have the prod ones to import, along with the saved one.

Dale, above, is on the right track -- I'd add the comment that Datapump allows for tablespaces, and datafiles, to be renamed during the import.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question