Data movement/replication strategies

[engineer2100]

Hi all,

No I am not talking about the usage of the Replication tool provided in Oracle. Rather what i am talking about is a general method of moving data using simple PL/SQL code.

I have a situation wherein, the database is somewhat un-normalized in the sense that there are no Referential integrity defined at the database level even when tables are related. The data is inserted/updated/deleted programmatically from the applications that use the database.

Now we are to move data within certain tables basically master data, from one database to another. We have no idea what "all" tables are related looking the database because of the lack of referential integrity. There are many applications that use the database.

I am looking at some tips that can solve this scenario. I know this sounds Weird but cannot help it.

I have a scenario of developing a small system that will allow the user to specify the tables and columns that are related to each other and then another system to read this relationship information and create a dump file that be later be imported on the target database. I know this would require the knowledge of tables in relation....

more ideas appreciated....

Thanx
Engi

 

[MPeccorini]

I would really help you in this, it sounds like a fine exercise for the brain, however, I don´t really understand in which point you want to "replicate" data.

As I understand, you would have some users register in your small system the relationships they "think" are involved in a table and then use that information to export/import all related data.

Can you clarify a bit?

Best regards,

Mauricio Peccorini

 

[SantaMufasa]

Engi,

I agree with Mauricio...this is just a little bit too much of a "wild hare" request. If you do not know how the rows from different tables relate to one another (i.e., no referential integrity), then how can we write a script to magically create proper relationships? Is the user producing joins "on the fly" between tables? If so, then, yes, we can produce a script to dynamically implement the joins, but in the absence of established referential integrity, the user must supply the "rules". Correct?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:50 (28Jun04) UTC (aka "GMT" and "Zulu"), 09:50 (28Jun04) Mountain Time)

 

[carp]

I have to agree with all of the above. As far as I can see, the only way to be certain here is to export/import ALL of the tables!