duplicate target database to auxialiary and rename schemas


I have to duplicate target database to auxialiary and rename schémas for application's constraints.
Is it possible?
If yes, how?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
For duplicating a database look at the DUPLICATE command in RMAN.  The documentation is pretty straightforward on how to duplicate a database.

I don't believe there is a way to rename a schema.
bibi92Author Commented:
Thanks. If I duplicate only the tablespaces data and indx because there have the same name on target and auxiliary database, can I change the owner of all objects.
johnsoneSenior Oracle DBACommented:
The only way that I am aware of to change the owner of objects is with exp/imp.

Assuming you are using the data pump versions the parameter is REMAP_SCHEMA.  The doc is here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL927
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
To clarify:
exp/imp (original version) is FROMUSER and TOUSER.

expdp/impdp (datapump versions) has REMAP_SCHEMA.
bibi92Author Commented:
Thanks I have already used datapump but for a db which size is 2Tb, it takes 24hours... Not possible in UAT.
slightwv (䄆 Netminder) Commented:
Sorry but Oracle doesn't allow you to "rename" a schema.

Your UAT database is an exact clone of production?  Seems like a bit of overkill.  normally they are a subset of production and known baseline.

If you don't start testing from a known baseline, how do you know if "differences" are because of data or code?
bibi92Author Commented:
Hello thanks I know these command and it takes very long time (24h):
exp/imp (original version) is FROMUSER and TOUSER.

expdp/impdp (datapump versions) has REMAP_SCHEMA.
slightwv (䄆 Netminder) Commented:
I don't know what to tell you.  There is no "supported" way to rename a schema by altering or updating the database.
bibi92Author Commented:
slightwv (䄆 Netminder) Commented:
I'm aware of that link.

That is why I mentioned no "supported" way.

I wasn't going to post a link to something that leaves you in an unsupported state and can trash a database.
johnsoneSenior Oracle DBACommented:
I would definitely never do that.  You should never be directly updating anything in the catalog.
bibi92Author Commented:
Ok I know only for testing.
slightwv (䄆 Netminder) Commented:
>>Ok I know only for testing.

What will testing on an unsupported configuration tell you?

All it will tell you your code runs with a potentially messed up database.

It won't tell you that the code runs in a supported configuration.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
For testing what exactly?  If it is for testing, why can't you use the actual schemas?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.