best way to update schemas

[vr76413]

Hello all..

I have a Schema 'ABC'in Test Environment where we load the data, test it and move that data to production DB under same schema name...

Now when ABC Schema in TEST database gets new data that includes new tables, new views, update some tables, insert new records into some tables ...etc.

so when i need to move only the updated,new records and new tables or views , what is the best way ?..

Obviously, EXP and IMP is not going to work since IMP doesn't update the existing records..

So can anyone guide me in this process. Any help is appreciated.

Thanks
Ron

 

[alexhu]

I must be missing something here....

You have some data
You load it into your test database
You test it
Its OK so you want to load it into production

Why not use the same method you used to load it into test ?

Alex

 

[vr76413]

Well data into test is comming from various sources like Access, SQL Server. ....and due to some firewall settings and stuff like that, i cant connect PROD db to any of these.

Thanks

 

[sem]

You may create some staging tables in test database to store semi-raw data (extracted, but not transformed). Regards, Dima

 

[THoey]

Can't you create a database link between the two instances and then use that? You could use:

CREATE TABLE tblNew AS
SELECT * FROM tblNew@Test

If this table has a lot of data in it, the manual suggests using the SQL*Plus COPY command. The format for that is:

COPY FROM
[RemoteUsername/RemotePassword@ConnectString]
[ToUsername/ToPassword@ConnectString]
{append|create|insert|replace}
TableName
using subquery;

This will alow you to use the SET COPYCOMMIT and the SET ARRAYSIZE to break the load into chunks. An example of this is:

SET COPYCOMMIT 1
SET ARRAYSIZE 1000

COPY FROM talbot/ledger@HQ -
CREATE worker -
USING -
SELECT * from WORKER

The dashes at the end of the lines are required. BTW, this info comes from the Oracle 8i Complete Reference... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...