Sql – Keeping tables synchronized in Oracle

denormalizationoraclesqlsynchronizationtriggers

We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A are fixed – no changes allowed)

What I want to do is to allow the infrequent DML operations on A to propagate to B, and vice-versa. I started with a pair of triggers to do this, but hit the obvious problem that when the triggers run, the tables are mutating, and an exception is thrown.

Is there a standard way of handling this issue? I've read differing reports on whether or not using dbms_scheduler is the way to go…

Thanks,

Andy

Update:
I've ended up chickening out of the whole issue and ensured that all stored procedures that update A, also update B, and vice versa.

I've marked Quassnoi's answer as accepted, because I'd follow his suggestions if faced with the same issue in the future.

I've marked up JosephStyon's answer, because I briefly got things working by adding two insert/update statement level triggers on tables A and B, then doing his merge procedure using A or B as the master table, depending on which trigger ran (although first I checked that the target table would be changed by the merge, earlying out if not).

Best Solution

I'd create A and B as views over a single normalized (or denormalized) table, and created an INSTEAD OF trigger over these views to handle DML operations.

If the query plans matter, it's better to keep two copies of tables: A_underlying and B_underlying and create the views just like this:

CREATE VIEW A
AS
SELECT  *
FROM    A_underlying

CREATE VIEW B
AS
SELECT  *
FROM    B_underlying

The predicates will be pushed into the views, and the query plans for actual tables and views will be the same.

In INSTEAD OF triggers over both view, you should put the data into both underlying tables.

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Sql – Get list of all tables in Oracle

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view:

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

Best Solution

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Sql – Get list of all tables in Oracle

Related Question