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

slightwv (䄆 Netminder)

export/import

classic or datapump:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm

Pra4444

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

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

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.

AidenA

ASKER

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?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AidenA

ASKER

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?

AidenA

ASKER

ok wait, was reading that you have to do it from the command line, not from sql*plus

AidenA

ASKER

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;