You can use the TRANSPORTABLE TABLESPACES feature to copy a set of tablespaces from one oracle database to another.
Note:
This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process. If this is undesirable, you can use the transportable tablespaces from backup feature.
The tablespaces being transported can be either dictionary managed or locally managed. Starting with oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size.
Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use an export/import utility to transfer only the metadata of the tablespace objects to the new database.
Note:
The transportable tablespaces feature supports both data pump and the original import and export utilities, imp and exp, with one caveat: you must use imp and exp for tablespaces containing xmltypes.
The transportable tablespace feature is useful in a number of scenarios, including:
There are two ways to transport a tablespace:
To run the transport tablespaces wizard:
1. Log in to enterprise manager with a user that has the exp_full_database
2. Click the maintenance link to go to the maintenance tab.
3. Under the heading move database files, click transport tablespaces.
About transporting tablespaces across platforms:
Starting with oracle database 10g, you can transport tablespaces across platforms. This functionality can be used to:
Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the v$transportable_platform view to see the platforms that are supported, and to determine each platform’s endian format (byte ordering). The following query displays the platforms that support cross-platform tablespace transport:
Sql> column platform_name format a32 Sql> select * from v$transportable_platform;
Platform_id platform_name endian_format
1 solaris[tm] oe (32-bit) big
2 solaris[tm] oe (64-bit) big
7 microsoft windows ia (32-bit) little
10 linux ia (32-bit) little
6 aix-based systems (64-bit) big
3 hp-ux (64-bit) big
5 hp tru64 unix little
4 hp-ux ia (64-bit) big
11 linux ia (64-bit) little
15 hp open vms little
8 microsoft windows ia (64-bit) little
9 ibm zseries based linux big
13 linux 64-bit for amd little
16 apple mac os big
12 microsoft windows 64-bit for amd little
17 solaris operating system (x86) little
16 rows selected.
If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, the datafile header must identify the platform to which it belongs. In an oracle database with compatibility set to 10.0.0 or later, you can accomplish this by making the datafile read/write at least once.
Checkout Oracle DBA Interview Questions
Be aware of the following limitations as you plan to transport tablespaces:
The following query returns a list of tablespaces that contain xmltypes:
Select distinct p.tablespace_name from dba_tablespaces p, Dba_xml_tables x, dba_users u, all_all_tables t where T.table_name=x.table_name and t.tablespace_name=p.tablespace_name And x.owner=u.username
Transporting tablespaces with xmltypes has the following limitations:
Additional limitations include the following:
Advanced queues:
Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
System tablespace objects :
You cannot transport the system tablespace or objects owned by the user sys. Some examples of such objects are pl/sql, java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.
Opaque types:
Types whose interpretation is application-specific and opaque to the database (such as raw, bfile, and the anytypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.
Floating-point numbers:
Binary_float and binary_double types are transportable using data pump but not the original export utility, exp.
When you create a transportable tablespace set, oracle database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with oracle database 10g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database.
The following table shows the minimum compatibility requirements of the source and target tablespace in various scenarios. The source and target database need not have the same compatibility setting.
Table minimum compatibility requirements
Name | Dates | |
---|---|---|
Oracle DBA Training | Oct 15 to Oct 30 | View Details |
Oracle DBA Training | Oct 19 to Nov 03 | View Details |
Oracle DBA Training | Oct 22 to Nov 06 | View Details |
Oracle DBA Training | Oct 26 to Nov 10 | View Details |
Technical Content Writer