The Theory:
Separate databases can share the same read only datafiles on disk. The datafiles must be accessible by all databases.
The Method:
This will be implemented using transportable tablespaces.
Note: The Shared tablespace must remain read-only in all databases mounting it.
The Way:
-The Shared read only Tablespace is already belongs to a database (ORCL).
-Make the tablespace read only:
SQL> ALTER TABLESPACE USERS READ ONLY;
-Export tablespace metadata using expdp utility:
export ORACLE_SID=ORCL
expdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_tablespaces=USERS logfile=ts_export.log
-Import tablespace metadata to the destination database using impdp utility:
export ORACLE_SID=FOO
impdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_datafiles=/ORA11G/ORADATA/ORCL/USERS01.DBF logfile=ts_import.log
*I suppose that directory “data_pump_dir” have the same path on both databases (ORCL &FOO).
Note: Dropping the read only tablespace in some databases will not modify the datafiles for the tablespace.Thus, the drop operation does not corrupt the tablespace,
Sure you will not use –including contents and datafiles– clause to delete the shared tablespace on one database or this will delete the tablespace datafiles on the Filesystem.
Warning:
Switching the read-only shared tablespace to read-write mode will lead to database corruption if it’s mounted in more than one database.