Home » RDBMS Server » Server Administration » table refresh
table refresh [message #527109] Sat, 15 October 2011 02:47 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
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
Re: table refresh [message #527132 is a reply to message #527130] Sat, 15 October 2011 10:01 Go to previous messageGo to next message
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.
Re: table refresh [message #527135 is a reply to message #527132] Sat, 15 October 2011 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The undecided aspect is your Oracle version.

Regards
Michel
Re: table refresh [message #527136 is a reply to message #527135] Sat, 15 October 2011 10:25 Go to previous messageGo to next message
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?
Re: table refresh [message #527137 is a reply to message #527136] Sat, 15 October 2011 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
Re: table refresh [message #527138 is a reply to message #527136] Sat, 15 October 2011 10:32 Go to previous messageGo to next message
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 ....
Re: table refresh [message #527143 is a reply to message #527138] Sat, 15 October 2011 12:02 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
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.
Re: table refresh [message #527145 is a reply to message #527138] Sat, 15 October 2011 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59407
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
Re: table refresh [message #527204 is a reply to message #527145] Sun, 16 October 2011 21:42 Go to previous messageGo to next message
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.
Re: table refresh [message #527205 is a reply to message #527204] Sun, 16 October 2011 23:17 Go to previous message
Michel Cadot
Messages: 59407
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

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


Current Time: Tue Oct 21 04:42:46 CDT 2014

Total time taken to generate the page: 0.05800 seconds