Mirroring tables (reliable data offloading)
All,
Excellent support forums, really great information.
We have local Oracle databases on 70+ manufacturing lines, each consisting of 6 tables to which manufacturing data is continuously written.
We want to centralize this data, adding line specifics to the records in the common repository.
My initial idea was to do the following:
1) create a repository table that mirrors the structure of the local, with one additional field for Line identification. This database would be on our network.
2) write "After Insert" triggers for the tables that duplicates the record in the repository, adding the line id to the INSERT statement.
2a) If the trigger fails, the failed record information would be stored in:
3) create a local table T_OFFLOAD_ERRORS with fields for storing table name and record ID of failed inserts.
4) create a scheduled job that queries the table (3) and attempts to offload the records, deleting them if successful and retaining them if not.
Is there a better way to do this? I generally avoid triggers in favor of scheduled jobs, but the user of this data is adamant that the data is live and wants to minimize lag.
P.S. all databases are 10g...
-brian