Re: partitioning

On Tue, 21 Oct 2003 14:55:04 GMT, Brian Peasland <dba_at_remove_spam.peasland.com> wrote:

>> *THEN* put these "historycal data" tablespace in Read Only Mode and
>
>Yes, after you've moved the partition to the correct tablespace, put the
>tablespace in READ ONLY mode.
>
>> *THEN* sharing these tablespace between two Oracle instance (one for
>> production,one for testing/integration) ?
>
>Two instances cannot "share" the same tablespace unless you are using
>Real Application Clusters. But read on...
>
>> And how can I accomplish this last deal, maybe using DB links ?
>
>You can, however, access the other instances data across a database
>link. It may be semantics, but this is not 'sharing the tablespace'.
>Rather, this is one user in one instance querying another instance for
>some data. To me, "sharing the tablespace" means that both instances
>have some sort of control the over tablespace. With a database link, one
>instance controls the tablespace and the other instance asks the first
>instance for data. It is up to the first instance to decide where that
>data is. The second instance has no control over that tablespace.
>
>To set up a database link, do the following in the second instance:
>
>CREATE DATABASE LINK first_instance CONNECT TO username IDENTIFIED BY
>password USING 'tnsalias';
>
>The username and password are the username/password in the first
>instance. You'll have to set up a TNS alias in your TNSNAMES.ORA file as
>well. You can then query a table in the first instance as follows:
>
>SELECT * FROM table_name_at_first_instance;
>
>
>HTH,
>Brian
>

Regarding the TNSNAMES entry, there is a small but critical point that is often overlooked by those who haven't done this before. Assume DB1 has a link defined to DB2, so that users of DB1 can query a table in DB2. In this case, DB1 itself becomes the client to DB2, so that the TNS entry for DB2 needs to be in the TNSNAMES file that is used by DB1. This may or may not be (and is probably not) the TNSNAMES file used by the end user.

Rule #1: TNSNAMES is used by clients, not servers. Rule #2: Severs may act as clients to other servers. Received on Tue Oct 21 2003 - 10:48:55 CDT