Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
How to convert user managed copies into RMAN copies Handling Nulls in a Primary and Foreign key relationship

How to transport a tablespace in Oracle 10g

Posted by Zahid on July 21, 2009.

Oracle database 10g supports transportation of tablespaces from one database to other even from one platform to the other. A list of transportable platforms can be seen by querying ( select platform_name from v$transportable_platform ). We will see below how to transport a tablespace from oracle 10.2.0.1 running on Linux 32bit to oracle 10.2.0.4 running on Windows Vista 64 bit.

The tablespace that is being transported should be self contained, which means the objects in the tablespace shouldn't have dependent objects in other tablespaces.

$ sqlplus / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the Partitioning, OLAP and Data Mining options SQL> create tablespace ts_tts 2 datafile '/d01/apps/oradata/oraxpo/ts_tts01.dbf' 3 size 10m autoextend on next 5m 4 extent management local 5 segment space management auto; Tablespace created. SQL> alter user scott quota unlimited on ts_tts; User altered. SQL> conn scott/tiger Connected. SQL> create table p_t1 (name varchar2(30) primary key) 2 tablespace users; Table created. SQL> insert into p_t1 values ('ABC'); 1 row created. SQL> insert into p_t1 values ('XYZ'); 1 row created. SQL> commit; Commit complete. SQL> create table t1 (name varchar2(30)) 2 tablespace ts_tts; Table created. SQL> insert into t1 values ('ABC'); 1 row created. SQL> insert into t1 values ('XYZ'); 1 row created. SQL> commit; Commit complete. SQL> alter table t1 add constraint t1_fk 2 foreign key (name) references p_t1 (name); Table altered. /* We have create parent child relationship, where parent table is in tablespace users and child table is in tablespace ts_tts. We want to transport tablespace ts_tts. Lets check if this tablespace violates any transportation rules. */ SQL> conn / as sysdba Connected. SQL> begin 2 dbms_tts.transport_set_check(ts_list=>'TS_TTS', 3 incl_constraints=>TRUE); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; VIOLATIONS ------------------------------------------------------------- Constraint T1_FK between table SCOTT.P_T1 in tablespace USERS and table SCOTT.T1 in tablespace TS_TTS /* dbms_tts.transport_set_check is complaining that the child table is in ts_tts and parent is in users tablespace. Lets go and fix this. NOTE: The fix is whatever suits you. All you have to do is remove these violations. */ SQL> conn scott/tiger Connected. SQL> alter table p_t1 move tablespace ts_tts; Table altered. SQL> conn / as sysdba Connected. SQL> begin 2 dbms_tts.transport_set_check(ts_list=>'TS_TTS', 3 incl_constraints=>TRUE); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; VIOLATIONS ------------------------------------------------------------ Index SCOTT.SYS_C005398 in tablespace USERS enforces primary constraints of table SCOTT.P_T1 in tablespace TS_TTS /* Check again and this time it says that the index behind the parent's table primary key is still in users tablespace. */ SQL> conn scott/tiger Connected. SQL> alter index SYS_C005398 rebuild tablespace ts_tts; Index altered. SQL> conn / as sysdba Connected. SQL> begin 2 dbms_tts.transport_set_check(ts_list=>'TS_TTS', 3 incl_constraints=>TRUE); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from transport_set_violations; no rows selected /* This time we are good to go with the transport. */ SQL> select a.platform_name , b.endian_format 2 from v$database a , v$transportable_platform b 3 where a.platform_name = b.platform_name; PLATFORM_NAME ENDIAN_FORMAT --------------------------- -------------- Linux IA (32-bit) Little /* This query tells us the platform where our database is running, its endian format. */ SQL> alter tablespace ts_tts read only; Tablespace altered. SQL> exit

Put the tablespace in read only mode and then export the metadata of the tablespace. Not data, just metadata.

$ expdp system/system dumpfile=ts_tts_metadata.dmp transport_tablespaces=ts_tts Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ts_tts_metadata.dmp transport_tablespaces=ts_tts Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ********************************************************************* Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /d01/apps/oracle/rdbms/log/ts_tts_metadata.dmp Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 03:39:40 $

Now come to the target platform and lets have some checks.

Start -> Run -> cmd C:\>sqlplus / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select directory_name , directory_path 2 from dba_directories 3 where directory_name='DATA_PUMP_DIR'; DIRECTORY_NAME DIRECTORY_PATH ---------------- ------------------------------------------- DATA_PUMP_DIR C:\oracle\product\10.2.0\admin\orcl\dpdump\ /* We will put our dump file in this directory to use it with the metadata import. */ SQL> select name from v$datafile; NAME ------------------------------------------------------------ C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 5 rows selected. /* Existing files on the target database . */ SQL> select a.platform_name , b.endian_format 2 from v$database a , v$transportable_platform b 3 where a.platform_name = b.platform_name; PLATFORM_NAME ENDIAN_FORMAT ------------------------------------ -------------- Microsoft Windows x86 64-bit Little /* The platform and the endian format of the target database. NOTE: If the endian format of the target and source database are different then the source file needs to be converted to the target platform format before transporting. */ SQL> exit

In our case the endian format of the source and target is same so we can simply copy the data files for the tablespace from source to target and import the metadata using data pump. I will give it a go with a scenario some other time where target and source endian formats are different.

Copy the data files and the dump file ts_tts_metadata.dmp to the target system and then put the tablespace back into read write mode.

$ sqlplus / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the Partitioning, OLAP and Data Mining options SQL> ALTER TABLESPACE ts_tts READ WRITE; Tablespace altered. SQL>

Come to the target platform and start datapump to import the tablespace metadata into the target database.
Before import make sure that the user who owned the objects in the tablespace in the source database exists here in the target. If this is not acceptable then you will need to remap the schema of the objects to an existing user schema during the import.

I have copied the data file to the location where other data files of this database reside and the dump file to the "C:\oracle\product\10.2.0\admin\orcl\dpdump\" where DATA_PUMP_DIR directory points.

C:\>impdp system/system dumpfile=ts_tts_metadata.dmp directory=data_pump_dir transport_datafiles= 'C:\oracle\product\10.2.0\oradata\orcl\ts_tts01.dbf' [remap_schema=SCOTT:HR] -- remap option will import the -- objects of SCOTT into HR schema. Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=ts_tts_metadata.dmp directory=data_pump_dir transport_datafiles= 'C:\oracle\product\10.2.0\oradata\orcl\ts_tts01.dbf' Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 04:13:32

If you see a message of successfully completed import, the tablespace has been imported. We can check that like this:

C:\>sqlplus scott/scott Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from p_t1; NAME ------------------------------ ABC XYZ SQL> select * from t1; NAME ------------------------------ ABC XYZ SQL> conn / as sysdba Connected. SQL> select tablespace_name , file_name from dba_data_files; TABLESPACE_NAME FILE_NAME --------------- --------------------------------------------------- USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF TS_TTS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TS_TTS01.DBF 6 rows selected.

Sometimes the target and source databases may have a different block size, for example the source database uses 8k block size but the target database uses 16k, in that case the tablespace in the source should be created with a block size of 16 k so that it can later on be transported to a 16k block size database.

See also:
Oracle 10g db_nk_cache_size Parameters