Testing Environment

Source Database: Windows 7 64-bit, Oracle 11g 64-bit

Target Database: RHEL6 64-bit, Oracle 11g 64-bit

1. Check Data base Character Set

select * from nls_database_parameters;
select userenv('language') from dual;

2. Review Transportable Platform Options

SELECT * FROM v$transportable_platform;

The query displays supported platforms with their endian formats.

3. Create Test Tablespace and User

CREATE TABLESPACE trans_test_tbs
DATAFILE 'D:\ORACLE_DATA\trans_tbs.DBF'
SIZE 2G AUTOEXTEND ON;

CREATE USER trans_user IDENTIFIED BY trans_user
DEFAULT TABLESPACE trans_test_tbs;

GRANT CONNECT, RESOURCE, DBA TO trans_user;

4. Verify Tablespace Self-Containment

EXECUTE dbms_tts.transport_set_check('trans_test_tbs', full_check => TRUE);
SELECT * FROM transport_set_violations;

5. Set Tablespace to Read-Only

ALTER TABLESPACE trans_test_tbs READ ONLY;

6. Export Metadata Using Data Pump

expdp \"sys/oracle as sysdba\" \
DUMPFILE=trans_export.dmp \
DIRECTORY=data_pump_dir \
TRANSPORT_TABLESPACES=trans_test_tbs

The export generates a metadata dump file and identifeis the required datafile.

7. Transfer Files to Target

Copy the exported dump file and the tablespace datafile to the target database server.

8. Create Target Schema

CREATE USER trans_user IDENTIFIED BY trans_user;
GRANT CONNECT, RESOURCE, DBA TO trans_user;

9. Import Tablespace on Target

impdp \"sys/oracle as sysdba\" \
DUMPFILE=trans_export.dmp \
DIRECTORY=imp_dir \
TRANSPORT_DATAFILES='/u01/oradata/trans_tbs.DBF' \
REMAP_SCHEMA=trans_user:trans_user

10. Verify Import Status

SELECT tablespace_name, status, logging
FROM dba_tablespaces
WHERE tablespace_name = 'TRANS_TEST_TBS';

The tablespace should initially show as READ ONLY.

11. Set Tablespace to Read-Write

ALTER TABLESPACE trans_test_tbs READ WRITE;

Key Considerations

• Ensure character sets match between source and target databases.
• The target database must not contain a tablespace with the same name.
• Cross-platform transport between Windows and Linux typically works without conversion.
• Always verify tablespace self-containment before transport.
• The source tablespace must be in READ ONLY mode during export to prevent ORA-19722 errors during import.