Posted by I was reading an interesting discussion today about multiple databases each containing large amounts of read-only data. If that read-only data is common, then it would make sense to have a single copy of that data and have both databases share it.
Well, as long as you can isolate that data into its own tablespace, then you can do that easily with Oracle by transporting the metadata between two databases and leaving the files in place.
Here’s an example
Source database
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> create tablespace i_am_on_121 datafile 'C:\oracle\oradata\tts\my_tspace' size 50m;
Tablespace created.
SQL> create table t tablespace i_am_on_121 as select * from dba_objects;
Table created.
SQL> alter tablespace i_am_on_121 read only;
Tablespace altered.
C:\>expdp transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp
Export: Release 12.1.0.2.0 - Production on Fri Apr 14 08:50:24 2017
Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved.
Username: mcdonac/*****
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01": mcdonac/******** transport_tablespaces=i_am_on_121 directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TRANSPORTABLE_01 is:
C:\TEMP\TTS.DMP
******************************************************************************
Datafiles required for transportable tablespace I_AM_ON_121:
C:\ORACLE\ORADATA\TTS\MY_TSPACE
Job "MCDONAC"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Apr 14 08:51:16 2017 elapsed 0 00:00:47
and then we import it into a different database (and this one even is a different version!).
Target database
C:\Users\hamcdc>impdp transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp
Import: Release 12.2.0.1.0 - Production on Fri Apr 14 08:51:28 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Username: mcdonac/*****
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TRANSPORTABLE_01": mcdonac/******** transport_datafiles=C:\oracle\oradata\tts\my_tspace directory=TEMP dumpfile=tts.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> select count(*) from t;
COUNT(*)
----------
92934
SQL> select * from dba_tablespaces
2 where tablespace_name = 'I_AM_ON_121'
3 @pr
==============================
TABLESPACE_NAME : I_AM_ON_121
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : READ ONLY
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : YES
SEGMENT_SPACE_MANAGEMENT : AUTO
DEF_TAB_COMPRESSION : DISABLED
RETENTION : NOT APPLY
BIGFILE : NO
PREDICATE_EVALUATION : HOST
ENCRYPTED : NO
COMPRESS_FOR :
DEF_INMEMORY : DISABLED
DEF_INMEMORY_PRIORITY :
DEF_INMEMORY_DISTRIBUTE :
DEF_INMEMORY_COMPRESSION :
DEF_INMEMORY_DUPLICATE :
SHARED : SHARED
DEF_INDEX_COMPRESSION : DISABLED
INDEX_COMPRESS_FOR :
DEF_CELLMEMORY :
DEF_INMEMORY_SERVICE :
DEF_INMEMORY_SERVICE_NAME :
LOST_WRITE_PROTECT : OFF
CHUNK_TABLESPACE : N
PL/SQL procedure successfully completed.
Learning is not a spectator sport 
Hi Connor,
Great share. But I am more attracted towards @pr script. Can you share it with community, please.
Regards,
Kaushal Ruparel
Sharing self-contained read-only tablespaces using TTS is quite easy, as demonstrated in this post.
The problem is that in many situations the read-only data cannot be isolated into sets of self-contained tablespaces. For those cases, the “Universal “ RO tablespace sharing method can be used.
Please note that the “Universal “method is not supported by Oracle.
Here are details: http://wp.me/p1DHW2-4Y
Reblogged this on desc EMP and commented:
Una alternativa para compartir datos maestros o históricos entre distintas bases de datos Oracle sin replicar los datos.
Hello Connor
what if the tablespace is NOT read-only? Will both databases be able to write into it?
Dear Connor,
Thanks for the great note. This is theoretically true that we can share the tablespaces across dbs when they are self contained and readonly. But I have observed while the plugging in of the tablespaces (xtts import) happens the target dB where we are plugging in puts the tablespaces momentarily into read write mode. I am sure you this happens in 12.1.0.2 in Linux. You can check the target db’s alert log. I assume the impdp was stamping something on the file headers.
Thanks again and I follow many of your write-ups.
Thanks
Raja
Hi Connor,
I am getting “ORA-01537 file already part of database” error. Datafiles are stored in ASM. Am i missing something?
Thanks
Testing this, going from a 12.2 to a 12.2 but the destination DB has compatibility set to 12.1 for other reasons not relevant here.
create tablespace, create table, make it read only… then expdp and try push in the other side we get
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 12.2.0.1.0 not compatible with target version 12.1.0.2.0
Thats fair enough, so I try the export with the version parameter but the export wont work with below error
expdp transport_tablespaces=myts directory=temp dumpfile=transport_tablespace_1.dmp version=12.1.0.2.0
..
..
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
ORA-39001: invalid argument value
ORA-39250: Using version to downgrade a transportable job is not supported
Cant find anything about this anywhere… any ideas? I tried with 12.1 and 12.1.0.2.0
The db setting of “compatible” puts indelible marks into the datafiles, which is why when you upgrade from version “A” to “B”, you leave compatible at “A” to allow downgrade. But once you set compatibility to “B”, those datafiles are now permanently moved to version B of the database.
My understanding of the “version” in datapump is for conventional objects/definitions in the dump file. There is nothing that can re-stamp a datafile header with a previous version. Sorry…