OracleDB‎ > ‎

Transfer Tablespace

Oracle move tablespace to another database

check to see if the tablespaces are self-contained

SQL> EXECUTE sys.dbms_tts.transport_set_check('sales01,sales02',true);
PL/SQL procedure successfully completed.
SQL>

You must have the EXECUTE_CATALOG_ROLE role to execute the TRANSPORT_SET_CHECK procedure. The procedure TRANSPORT_SET_CHECK returns no errors, indicating that the two tablespaces in your transportable tablespaces set, sales01 and sales02, are self-contained and therefore, eligible candidates for transporting.

You can further confirm this by querying the transport_set_violation table, which table lists all the partially contained tables in a tablespace and any references between objects belonging to different tablespaces.

SQL> SELECT * FROM sys.transport_set_violation
no rows selected
SQL>


Now all tablespaces that are to be transfered need to be set to read-only mode. (this is not necessary but its good practive)

SQL> ALTER TABLESPACE sales01 READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE sales02 READ ONLY;
Tablespace altered.
SQL>

Now we have to use the data Pump Export utility to generate the data dictionary metadata for the two tablespaces, sales01 and sales02 then physically copy all the data fiels in the two tablespaces and the export dumpfile to a directory that the target database can access.


Exporting the Dictionary Metadata for the Tablespaces:

[finance] $ expdp oe/oe DIRECTORY=dpump_dir1 DUMPFILE=sales.dmp
           TRANSPORT_TABLESPACES=sales01,sales02 INCLUDE=triggers,constraint,grant
Export: Release 10.2.0.0.0 - 64bit on Sunday, 29 May, 2005 14:34
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.0.0 –
64bit Beta With the Partitioning, OLAP and Data Mining options
Starting "oe"."SYS_EXPORT_TRANSPORTABLE_01": oe/********
transport_tablespaces=sales01,sales02
include=triggers,constraint,grant directory=dpump_dir1 dumpfile=sales.dmp
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OBJECT_GRANT
Master table "OE"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OE.SYS_EXPORT_TRANSPORTABLE_01 is:
 /u01/app/oracle/dba/sales.dmp
Job "OE"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:36
oracle@finance.netbsa.org   [/u01/app/oracle]
[finance] $

Before you can start importing the export dump file to the target database, make sure that the block size of the tablespace is the same as the standard block size of the target tablespace. If it isn’t, the target database must have a nonstandard block size specified in its init.ora file of the same size as the block size of the tablespace you want to export.



Importing:

C:\>impdp system/sammyy1 dumpfile=sales.dmp TRANSPORT_DATAFILES='sales01_01.dbf', \
'sales02_01.dbf' directory=dpump_dir1
Import: Release 10.2.0.0.0 - Beta on Saturday, 21 May, 2005 16:15
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.0.0 - Beta
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********
dumpfile=sales.dmp TRANSPORT_DATAFILES='sales01_01.dbf',
'sales02_01.dbf' directory=dpump_dir1
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OBJECT_GRANT
...
C:\>



Note: you can transfer tablespaces between platform as long as their Endian formats are the same.(they both should be little endian or big endian)

Run the following query to find out what endian format you have:

SQL> SELECT t.endian_format
  2 FROM v$transportable_platform t, v$database d
  4* WHERE t.platform_name = d.platform_name;
ENDIAN_FORMAT
---------------------
Little
SQL>

You can convert tablespace endian format in rman with CONVERT TABLESPACE command or if you want to transfer the datafile first then convert you can convert the datafile by running the CONVERT DATAFILE command in rman


Comments