From Sparc to Intel
As a preliminary cost saving measure , our "IT" group decided to move from costly Sun Sparc platform to Intel hardware, our many Oracle instances . This move , beside being a technical challenge on the Oracle side provided us with a new method to quickly test VMware performance instances.
It appears that this exact move has been performed numerous times utilizing imp/exp and creating transportable tablespaces.
For a tablespace to be transportable it must be totally self contained.
This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
The TS_LIST parameter accepts a comma separated list of tablespace names:
sys@ORACLE>EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MYTABLESPACE', incl_constraints
PL/SQL procedure successfully completed.
sys@ORACLE>
Now check for violations
sys@ORACLE> select * from transport_set_violations;
no rows selected
sys@ORACLE>
Switch the table space to READ ONLY!
sys@ORACLE> alter tablespace MYTABLESPACE READ ONLY;
tablespace altered.
sys@ORACLE>
Finally we can export
bash% exp USERID='system/password@ORACLE as sysdba' TRANSPORT_TABLESPACE=y
TABLESPACES=MYTABLESPACE
FILE=MYTABLESPACE.dmp
Dont forget to reset your tablespace back to read write
sys@ORACLE>ALTER TABLESPACE MYTABLESPACE READ WRITE;
Tablespace altered.
sys@ORACLE>
Now create users who owned tablespace in MYTABLESPACE
sys@ORACLE> create user marti149 identified by guesswho
User created.
sys@ORACLE>
Now for the cool part!
bash% imp USERID='system/password@ORACLE2 as SYSDBA'
TRANSPORT_TABLESPACE=y
DATAFILES='/path/oradata/ORACLE2/MYTABLESPACE.DBF'
FILE=/tmp/MYTABLESPACE.dmp
Now to read and test
sys@ORACLE> ALTER TABLESPACE MYTABLESPACE READ WRITE;
Tablespace altered.
sys@ORACLE>select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name = 'MYTABLESPACE';
Viola!
TABLESPACE_NAME PLU STATUS
------------------------------------------ ----- ---------
MYTABLESPACE YES ONLINE