Avatar of AidenA
 asked on

create a copy of oracle schema (in production) for development purposes

Hi, this is probably (hopefully) an easy one but just wasn't obvious to me when I googled it.

I have a couple of schemas in my database orcl which two websites are running off. But, I would like to be able to create a copy of each so that I can use them for test purposes.

One of them is called MT@ORCL the other is called CTC@ORCL.

How should I go about that exactly? And how would I refresh the data in that schema afterwards?

Thanks, Aiden
Oracle DatabaseASP.NETSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)


If the schema size is not too big, then probaby the easy way to do this would be using DataPump utilities impdb and expdb. ( i am assuming you are using oracle version 10g and above)

Here is a good link on how to use this

David VanZandt

Since it goes without saying, then I should probably mention a good DBA avoids duplicating production (PII) data into a less secure environment.  Options include importing into an encrypted tablespace, or taking a sample of the data and obscuring it.  Actual production performance load may be simulated by bringing over your schema statistics, profiles, etc.  See the knowledge base for many earlier sources, or open a new question if you want to pursue it.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck

sorry, back on this now...

so yeah, actually I've used the old imp / exp to create a backup of my tables. datapump is fast it seems.

So, what does this mean exactly? I need to create a new schema, and then export the tables and schemas into a dmp file, and then import from that file into the new schema? Is there no easier way, say to do it all in one go without having to write lots of lines of imp / exp statements? Or, if I don't specify the table name does it dump all tables?
slightwv (䄆 Netminder)

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.
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

well I suppose it can look complicated when you haven't done it before. I thought maybe I had to write a line for each table.

So, I created the new test schema alright, and then did the exp....  statement (btw, that's classic then right? what is the datapump version? well, i guess it's expdp... i can probably figure it out).

Unfortunately, I got an error

SP2-0734: unknown command beginning "exp XX/XX..." - rest of line ignored.

Looks like it didn't recognise, the exp command. I had logged into to my database ORCL as sysdba and was in the SQL> tool, any idea what might be wrong there?

ok wait, was reading that you have to do it from the command line, not from sql*plus
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

great!! that worked perfectly.

Just one thing... tried to do it again just to see if there was any problem with that, and got errors because the objects already existed. So, presumably I have to delete everything before I do it again. Is there an easy way of going about that? Seems there's a bit of code involved going by this post http://arjudba.blogspot.fr/2008/09/dropping-all-objects-in-schema.html

thanks again, Aiden
slightwv (䄆 Netminder)

>>there an easy way of going about that?

drop user newusername cascade;
create user newusername identified by newpassword default tablespace sometablespace temp tablespace temp;