OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » table refresh

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table refresh [message #527109]

Sat, 15 October 2011 02:47

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

Let say i need to refresh 1 table from production to uat. Do you think dropping that table in UAT or truncate that table in UAT will be better?

Truncate will preserve the indexes/comments/grants/sequences/packages/procedures/functions/trigger/database links/materialized views/synonym

but NOT DROP.

Using truncate, you have to FIRST, disable the indexes/ sequences,in- order for the IMP to be successful.

BUT what happen if there are 'things/changes on that table in production you might not know? Lets say, difference tablespace name or something like that, Thus somehow the prod table is difference in uat.

In that case i don't think one can use the truncate method.

Dropping method, you have to spool out the privileges, and got to find out all the objects on the table ( indexes/ packages/ so on and so forth) ? of course doing the necessary back of that table in UAT before actually drop/ truncate the table and then import in the table from production.

Spooling the file is somehow like a backup or to do comparision if something goes wrong?

[Updated on: Sat, 15 October 2011 03:13]

Report message to a moderator

Re: table refresh [message #527130 is a reply to message #527109]

Sat, 15 October 2011 09:30

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Using truncate, you have to FIRST, disable the indexes/ sequences,in- order for the IMP to be successful.

Wrong! You have to only disable primary/unique keys, but just for the truncate statement, you can (and should) reenable them for the import.

Quote:

BUT what happen if there are 'things/changes on that table in production you might not know? Lets say, difference tablespace name or something like that, Thus somehow the prod table is difference in uat.

The solution depends on your version, you didn't care to post us.

Quote:

In that case i don't think one can use the truncate method.

Yes, you can, in any case but if the logical definition of the table (columns, datatypes) are not the same ones.

Regards
Michel

Report message to a moderator

Re: table refresh [message #527132 is a reply to message #527130]

Sat, 15 October 2011 10:01

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

the undecided aspect is using user mode OR table mode.

In the table mode - User-stored procedures, packages,and functions are not export thus when u import in, it will not exist. So in which mode i have to do some work on finding out there exist one on that particular table, first of all right even before blindly just chose user / table mode.

Report message to a moderator

Re: table refresh [message #527135 is a reply to message #527132]

Sat, 15 October 2011 10:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The undecided aspect is your Oracle version.

Regards
Michel

Report message to a moderator

Re: table refresh [message #527136 is a reply to message #527135]

Sat, 15 October 2011 10:25

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

Michel Cadot wrote on Sat, 15 October 2011 23:10

The undecided aspect is your Oracle version.

you mean for example,

export from 10g to 11g?

Report message to a moderator

Re: table refresh [message #527137 is a reply to message #527136]

Sat, 15 October 2011 10:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

Report message to a moderator

Re: table refresh [message #527138 is a reply to message #527136]

Sat, 15 October 2011 10:32

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

Or i am concerning too much. I kept on thinking that there will be alot of check on both table in uat and production. But don't have the experience to know what are some of the factors i should be considering except if i decide to import just the data from production to uat, that particular table

Add NOT NULL columns
- Change the datatype of a column to an incompatible datatype (LONG to NUMBER, for example)
- Change the definition of object types used in a table
Change DEFAULT column values

another things that causes the undecided is that different mode also means some of the object will /will not be exported ....

Report message to a moderator

Re: table refresh [message #527143 is a reply to message #527138]

Sat, 15 October 2011 12:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what problem are you really trying to solve?

Usually new application features require schema changed that need to be tested.
Using only Production schema, precludes testing the new application features.
There is no one size fits all solution for satisfactory & complete application testing.

Report message to a moderator

Re: table refresh [message #527145 is a reply to message #527138]

Sat, 15 October 2011 12:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

The solution depends on your version, you didn't care to post us.

Regards
Michel

Report message to a moderator

Re: table refresh [message #527204 is a reply to message #527145]

Sun, 16 October 2011 21:42

hanner
Messages: 90
Registered: August 2011
Location: at HOME

Member

guess my worries are: what is being exported. Just realise that when i use table- mode to export the data from production and import into uat, the mview log is not being exported from the production. So this are the things that i am quite concern. There are some of the 'options ' available listed in the oracle utitily guide which i dun fully understand. Guess one just have to hands on and try it out which i have done.

Report message to a moderator

Re: table refresh [message #527205 is a reply to message #527204]

Sun, 16 October 2011 23:17

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Sat, 15 October 2011 19:07

Quote:
The solution depends on your version, you didn't care to post us.

Regards
Michel

Report message to a moderator

Previous Topic:	How to remove deleted entry from sys.ts$
Next Topic:	system table space

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:10:25 CDT 2024