Home » SQL & PL/SQL » Client Tools » Move from DEV to TEST env. - TOAD or SQL DEVELOPER
Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462532] Thu, 24 June 2010 14:44 Go to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

Hi,

I have to move few tables, procedures, packages from DEV to TEST environment.
Some of these tables are new. so I copied the DDL and ran it in the TEST, and for data - import as INSERT, and ran that .. so that was not a problem. But few tables are updated to have new 4 to 5 columns, and that table has huge data too.. so do i have to DROP and RE-CREATE the table in TEST ?
For procedures, I did the same. Copied the new from DEV over in TEST and compiled.. But somebody told me that, in TOAD, if I compile in DEV, close that connection, connect to TEST, and compile the same procedure ( in TEST ) , TEST env will have the exact same procedure as DEV. Is this possible ?
What is the best/correct way to move tables- with or without data, and procedures from DEV to TEST env.

Thanks.
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462533 is a reply to message #462532] Thu, 24 June 2010 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
assume logged onto TEST

create table TABLE1 AS SELECT TABLE1@DEV
ALTER TABLE TABLE1 add(new_col1 varchar2(31));

[Updated on: Thu, 24 June 2010 14:51]

Report message to a moderator

Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462534 is a reply to message #462532] Thu, 24 June 2010 15:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>What is the best/correct way to move tables- with or without data, and procedures from DEV to TEST env.
Export/Import? (or expdp/impdp)?

Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462555 is a reply to message #462532] Thu, 24 June 2010 22:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rkhatiwala wrote on Thu, 24 June 2010 21:44
What is the best/correct way to move tables- with or without data, and procedures from DEV to TEST env.

The best procedure is to not do that.
TEST should be built/updated/maintained as if it were a production environment.
This means that you should only run production scripts on it and not "move stuff from DEV". After all, who can define what the tested situation is if you just willy-nilly edit stuff on TEST?
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462601 is a reply to message #462555] Fri, 25 June 2010 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as procedures/functions/packages/views/triggers etc go you should have scripts for them in some form of source control.
In which case you would just run the correct version of the script in via sql plus.

For tables it can be more complicated - you should have scripts for them as well in source control, but if you've added columns what you probably want to do is create a script with the approiate alter table commands and run that in.

As for data, if it's static (doesn't change) then you should probably have scripts for that as well - either insert statements, or something that can be used by sqlloader.
Otherwise you are probably looking at import/export as Mahesh suggested.

As Frank says you should treat test as though it is production.
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462603 is a reply to message #462601] Fri, 25 June 2010 04:29 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thinking about it, If you're treating test as prod you probably shouldn't be using import/export at all, so ignore that bit.
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462663 is a reply to message #462601] Fri, 25 June 2010 08:46 Go to previous messageGo to next message
rkhatiwala
Messages: 177
Registered: April 2007
Senior Member
Thank you everybody for help.

Creating scripts does sound like a plan. ALTER TABLE and other code modifications will be easier, and anybody should be able to run that script,and in that we can also log the errors - in txt file or some table. So i think its more practical too. Since this is totally new development, and honestly, this is the first time I am doing it, can anybody give me simple, generalized script , that I can use as an idea..

Is it something like this:
CREATE DATABASE LINK
G11DEV CONNECT TO user IDENTIFIED BY password

then in TEST env,
select * from table1@G11DEV
where .......

How can i use same for UPDATE / INSERT in TEST env ?

Thanks again.
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #462683 is a reply to message #462663] Fri, 25 June 2010 11:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Think again. How are you going to get these changes to PROD?
And how will you be able to reproduce this when creating a second, third, hundredth instance?
Re: Move from DEV to TEST env. - TOAD or SQL DEVELOPER [message #464780 is a reply to message #462683] Fri, 09 July 2010 07:55 Go to previous message
tomharris
Messages: 1
Registered: July 2010
Location: Cambridge, UK
Junior Member
You might want to take a look at Red Gate's Schema Compare for Oracle. It's a new tool built specifically to compare and synchronize schema changes. You can download a free 14-day trial from Red Gate's website

I'd be interested to hear if it works out for the problem you're having

Tom Harris
Red Gate Software Ltd.
Previous Topic: Does Toad 8.5 work with Oracle 10gr database?
Next Topic: Comparison of object structures in different environment
Goto Forum:
  


Current Time: Mon Dec 06 10:58:46 CST 2021