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?


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.


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.


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.

  • Production and test are two existing, different Oracle database instances on the same server. The catalog tables are new and do not yet exist (for Oracle). The idea to add a separate schema for them, is for ease of update. On the destination side I think of some truncate tables, followed by an import.
    – bernd_k
    Feb 15 '11 at 22:31

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.


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 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).

  • 2
    I'd at least quibble with the idea that it would be efficient to store the data as flat files read via external tables because it wouldn't be possible to index the data in that case. That may not be important if you're just doing table scans of the tables in the CATALOG schema but it would create massive problems if you're doing single-row lookups. Feb 15 '11 at 20:23
  • Can I create indexes on external tables?
    – bernd_k
    Feb 15 '11 at 20:24
  • Yeah, it does entirely depend on how the data is accessed, I just mention it as a third option for sharing data between databases. Also, depending on the original source of this data, it may save on loading it into the catalogs database. Cutting down the number of instances may save on licensing costs. There are many factors to consider...
    – Gaius
    Feb 15 '11 at 20:39
  • @bernd_k - No, you cannot create indexes on external tables. Feb 15 '11 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.