Best method to give two Oracle databases on the same server access to common data

Question

With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases?

It is possible to put these tables into a third database, let us call it catalogs, but then the tables within catalogs can't be accessed as efficiently as if the tables where directly in production or test. On the other hand, space is saved, as there is only one copy of the data.

I see the following two options

• add a user and schema for catalog to both database instances. That gives efficient access, but duplicates space requirements and work to maintain the catalogs
• add a third database instance for catalogs and access this via database links. This saves space, but what about performance.

Which of these solution is better?

Edit:

Some further information: Catalog has about 60 tables using less than 1GB tablespace. There are functions to be defined in the katalog schema and indexing is important.

Answer 1

How exotic do you want to get ?

Uwe Hesse demonstrated that it is possible to have read-only tablespaces shared between two independent (non-RAC) database instances. I'd have to be very sure that the disk-space saving was worth it before I'd configure an environment this way.

Answer 2

You could store the catalogs tables in a separate schema on production. test could then have a schema with the same name that uses views that select from the production tables using a database link. This would make production fast because the data is local without requiring extra space.

If you decided that a particular table was too slow to access over the database link or having too great of an impact on production, then you could create a materialized view on test instead of a view. You could create as many or as few of these as necessary to give the best balance of space and performance.

On the other hand, 1GB isn't that much space, so you might be better off importing into both databases even though it would double the space requirements. It would have a speed advantage and would make test more like production.

Answer 3

From a testing standpoint I would not worry about the extra few bucks for the extra data. I would ensure I had a reliable mechanism for updating the data from a common location. Then you can load updates into the testing environment and verify it there. Then load it into production.

The last thing you want to do is load a set of data into production and find out there are problems. I had a client with a feed of financial data that would flip between dollars and cents. Fortunately, it was a batch environment and they could rollback to before the update.

Answer 4

There is a third option - write your common data in CSV format and have them both see it as an external table (as many as you want). This has been proved to be highly efficient.

Tho' personally I would go with your option 2. Remember that altho' you are saving space, you are still using memory for the SGA of the third instance, still adding a little overhead for your DBA, etc. Accessing via the loopback interface (run up a listener on 127.0.0.1) will be so quick you'll barely notice the DBLINK.

If you later need to split any of these out, DBLINK over gigabit Ethernet will also be extremely quick (or NFS share the external directory).