Home » RDBMS Server » Server Utilities » Refresh a Schema - Data Only (10gR2)
icon5.gif  Refresh a Schema - Data Only [message #344082] Thu, 28 August 2008 13:58 Go to next message
klabu
Messages: 4
Registered: October 2006
Location: DC
Junior Member
10gR2
I want to refresh a shema - data only

If I'm to use expdp to export "scott" from Production and use impdp to import to "scott" on Test....

What is the best approach on the import ?

1) Should I drop all tables under "scott" or truncate them ?
2) What about table constraints/Ref. Integrities...how do I deal with them ?

thanks

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344085 is a reply to message #344082] Thu, 28 August 2008 14:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>1) Should I drop all tables under "scott" or truncate them ?
DROP USER SCOTT CASCADE;

>2) What about table constraints/Ref. Integrities...how do I deal with them ?
see above response

[Updated on: Thu, 28 August 2008 14:02] by Moderator

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344092 is a reply to message #344085] Thu, 28 August 2008 14:24 Go to previous messageGo to next message
klabu
Messages: 4
Registered: October 2006
Location: DC
Junior Member
Hi can you explain why it is better to drop the user and deal with the consequences in stead of other alternatives ?
And how else (other than dropping "scott") would you do this ?

thanks

[Updated on: Thu, 28 August 2008 14:24]

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344100 is a reply to message #344082] Thu, 28 August 2008 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>deal with the consequences
EXACTLY what are the consequences that need to be addressed from DROP USER CASCADE?
I contend that are no "consequences" from DROP USER CASCADE.

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344110 is a reply to message #344082] Thu, 28 August 2008 15:42 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
klabu wrote on Thu, 28 August 2008 14:58
10gR2
I want to refresh a shema - data only
.
.
.
1) Should I drop all tables under "scott" or truncate them ?



If you were "refreshing data only" you would not drop tables.
TRUNCating will leave all your indexes in place, making an import very slow.
DROPping the schema will invalidate all objects relying on any object in the SCOTT schema.
So it's up to you to decide which one will be easier and/or cause less headaches.

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344133 is a reply to message #344110] Thu, 28 August 2008 17:09 Go to previous messageGo to next message
klabu
Messages: 4
Registered: October 2006
Location: DC
Junior Member
joy_division wrote on Thu, 28 August 2008 16:42

If you were "refreshing data only" you would not drop tables.
TRUNCating will leave all your indexes in place, making an import very slow.
DROPping the schema will invalidate all objects relying on any object in the SCOTT schema.
So it's up to you to decide which one will be easier and/or cause less headaches.


Hi if I take the TRUNCATE route, can you tell me what to do with constraints/RIs in the target schema tables ? (or I don't have to worry about them ???)

thanks

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344135 is a reply to message #344082] Thu, 28 August 2008 17:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Hi if I take the TRUNCATE route, can you tell me what to do with constraints/RIs in the target schema tables ?
The truncates have to be done "in order"; do child tables before parent tables.
Loading new data must be done "in order"; load parent tables before child tables.

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344167 is a reply to message #344082] Fri, 29 August 2008 00:19 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
why are you dont want to drop the schema while refreshing as told by Anacedent.That is the most convenient method to refresh schema from production to development.Use parameter remap_tablespace and remap_schema while importing.

Regards,
Varun Punj,

Report message to a moderator

Re: Refresh a Schema - Data Only [message #344236 is a reply to message #344135] Fri, 29 August 2008 03:03 Go to previous message
klabu
Messages: 4
Registered: October 2006
Location: DC
Junior Member
anacedent wrote on Thu, 28 August 2008 18:13
>
The truncates have to be done "in order"; do child tables before parent tables.
Loading new data must be done "in order"; load parent tables before child tables.


Thanks for the explanation - yes drop/recreate the user is the easiest way of doing this.

Report message to a moderator

Previous Topic: SQL Loader INFILE w/ Date
Next Topic: 9.2
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 13 15:02:17 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.