Re: partitioning

> *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  

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"