structure changes

[yenni143]

hi all,

we have development and production env.
if we make any table structure changes in one environment and then import data from another env where there is no table structure changes, will there be any problems ?

thanks

[sambavan]

Yes, if the coulums have been rearraged/added/deleted, then sure the import would fail.

Sam

[yenni143]

thanks Sambavan,

whats the solution then? i should have the same structure again in the other env also ? is there no way to test it out without the change in the other env?

[sambavan]

You can do this:

1. export a small table from your production
2. create the modified structure of that same table in your development side with the same table name.
3. now try to import the table that you had exported.

Points to ponder:
1. If you have only added the columns to the table and the addition of them were at the end, then the import from the previous format would go through in one direction, where you get to import the data from the old structure to the new one, not the other way. i.e from the new structure to the old.

2. If you have deleted any columns, then the import would fail on both the direction.

The only way that I could think of is to create the new structure under different name, then copy the old data into the new one using plsql then drop the old table and rename the new one.

Sam

[marist89]

Originally posted by sambavan
Points to ponder:
1. If you have only added the columns to the table and the addition of them were at the end, then the import from the previous format would go through in one direction, where you get to import the data from the old structure to the new one, not the other way. i.e from the new structure to the old.

Somebody previously pointed out that you can add the fields ANYWHERE (not just the end) and imp will be able to handle it.

[sambavan]

Originally posted by marist89
Somebody previously pointed out that you can add the fields ANYWHERE (not just the end) and imp will be able to handle it. [/B]

I didn't try. So on such case, is it doing select into? If that was the case the others should also be possible isn't it?

Sam

[jmodic]

Originally posted by sambavan
I didn't try. So on such case, is it doing select into? If that was the case the others should also be possible isn't it?

Example:
You have TABLE1 with columns COL1, COL2, COL3 in that orther. You export that table. Now let us see some scenarios about imports:

1. You drop TABLE1 and create another one with the same name, same column name and types, but columns being in different column positions (COL3, COL2, COL1 or whatever order you like). When you perform import, the imp will perform simple inserts, constructed from the dmp:
INSERT INTO table1 (col1, col2, col3) VALUES (:x1, :x2, :x3);

See, the column order in inserts will be the same as it was in the original table, but this doesn't prevent inserts to be successull in the new table with different column positions. Column position is irrelevant for imp.

2. You add additional column (COL4) into TABLE1. Imp will stil perform same inserts
INSERT INTO table1 (col1, col2, col3) VALUES (:x1, :x2, :x3);

If COL4 can accept NULL values, the imp will successfully insert exported rows. If new column has NOT NULL constraints, the inserts will fail. New column is or is not relevant for import, depending if it is NULL/NOT NULL.

3. Drop (or rename) one of the original columns. Let's say we drop COL1. Imp will perform
INSERT INTO table1 (col1, col2, col3) VALUES (:x1, :x2, :x3);

and fail because there is no COL1 in the new table. Missing (or renamed) column will make prevent successfull import of the table.

[Edited by jmodic on 09-10-2001 at 05:28 PM]

[sambavan]

Points to ponder:
1. If you have only added the columns to the table and the addition of them were at the end, then the import from the previous format would go through in one direction, where you get to import the data from the old structure to the new one, not the other way. i.e from the new structure to the old.

2. If you have deleted any columns, then the import would fail on both the direction.

O.K it makes sense now for me. I was under the impression colum addtion would have to be at the end, but if it were useing INSERT INTO, then I'm wrong with my assumption on
...the addition of them were at the end,

Thanx for the explanation.

Sam

[ccastaneda]

Hi Yennie..

My work environment is the same of yours.. and many times i have to migrate all the structure changes from my development database to production database.

There are CASE tools that allow migrate the structure of one database in another using a Database Model as interface. One of those tools is ERWin.

You can make a reverse engineering from your development database into a model, and later compare that model to your production database.

ERWin automatically generate ALTER scripts, backup the data of the tables that have changes and tries to restore the data into the new structure. I say "try" because there are cases where this is not possible (sambavan explained clearly), ERWin put this data in other tables for a manual update.

Make this previous proccess guarantizes the success of any export/import between your databases.

Yo can download a trial version form CA site, its a very powerfull tool (It's good but its not cheap..).

Hope it helps

[amar]

Originally posted by jmodic

If COL4 can accept NULL values, the imp will successfully insert exported rows. If new column has NOT NULL constraints, the inserts will fail.

[/B]

......... the only exception being [B] when there is a DEFAULT value associated with the new column [B]. For example, for a table created as follows:

CREATE TABLE TT ( c1 NUMBER, c2 NUMBER, c3 NUMBER DEFAULT 9 NOT NULL)

the following insert would work

INSERT INTO TT (c1,c2) VALUES (:x1, :x2)


Do I make sense or is my reasoning screwed ??

-amar