Oracle Spatial and Transportable Tablespaces by Gwen Lazenby

Transportable Tablespaces are a fast way of copying large volumes of data between databases and are therefore ideal to use with the large volumes normally associated with Spatial data. Although it has been possible to use Transportable Tablespaces with Spatial data since Oracle 10g Release 1, there were restrictions on its use, necessitating extra steps to transport Spatial indexes.

 

The good news is that of Oracle Database 11g Release 2, both Spatial Data and Indexes can be copied between databases on the same Endian platforms using standard methods of transportable tablespaces. Transport between databases on different Endian platforms is also supported, but extra steps are still required if Spatial indexes are involved.

 

Let us now look at the steps required in addition to the standard Transportable Tablespace technique, to transport Spatial Data and Indexes in different Oracle Releases.

 

In the sections below, we will be using the example of two tablespaces, SPATIAL_DAT and SPATIAL_IDX, that are to be transported to another database, and user SCOTT owns a table CITIES in SPATIAL_DAT whose Spatial index CITIES_SIDX is in SPATIAL_IDX.

 

Between 11g Release 2 databases on platforms of the same Endianess

 

No extra steps are needed. Use the standard transportable tablespace techniques.

 

Between 11g Release 2 databases on platforms of different Endianess

 

If transporting Spatial Data only and no Spatial Indexes are involved, then NO extra steps are required. Use the standard transportable tablespace techniques.

 

If Spatial Indexes are being transported, then after the transport has been completed all users that own Spatial Indexes in the transported tablespaces must execute the following procedure on the new (target) database.

 

EXEC SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

If in doubt, you can determine if this step is necessary by running the following query as each index owner

 

SELECT DECODE(BITAND(sdo_index_version, 1024), 1024, 'Y', 'N') ENDIAN_FLAG

  FROM user_sdo_index_metadata

  WHERE sdo_index_name = 'index_name';

 

So in our example detailed above, the following query would be run by SCOTT -

 

SELECT DECODE(BITAND(sdo_index_version, 1024), 1024, 'Y', 'N') ENDIAN_FLAG

  FROM user_sdo_index_metadata

  WHERE sdo_index_name = 'CITIES_SIDX';

 

If the result is 'Y', then it will be necessary to run -

 

EXEC SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

No further steps are needed after this.

 

From a pre-11g Release 2 database to an 11g (any release) database

 

If transporting Spatial Data only and no Spatial Indexes are involved, then NO extra steps are required. Use the standard transportable tablespace techniques.

 

If transporting Spatial Indexes then the following steps must be performed before and after the transport-

 

Each user that owns a Spatial Index in one of the tablespaces to be transported must run the following command for every tablespace that is to be transported that contains Spatial Data (not necessarily the same ones that contain the indexes).

 

exec SDO_UTIL.PREPARE_FOR_TTS('tablespace_name')

 

where tablespace_name in the name of the tablespace containing the Spatial tables.

 

Then use normal procedures to transport the tablespaces to the target database.

 

Once the tablespaces are transported, then the same users that performed the steps above, must perform the following -

 

exec SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

Then for each transported index -

 

ALTER INDEX 'index_name' PARAMETERS ('CLEAR_TTS=TRUE');

 

So in our example, detailed above, user SCOTT would run the following command prior to starting the transportable tablespace procedure -

 

exec SDO_UTIL.PREPARE_FOR_TTS('SPATIAL_DAT')

 

After the SPATIAL_DAT and SPATIAL_IDX tablespaces have been transported to the new database, SCOTT would run -

 

exec SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

followed by

 

ALTER INDEX CITIES_SIDX PARAMETERS ('CLEAR_TTS=TRUE');

 

From a 10g Database to a 10g Database

 

If transporting Spatial Data only and no Spatial Indexes are involved, then NO extra steps are required. Use the standard transportable tablespace techniques.

 

If transporting Spatial Indexes then the following steps must be performed before the transport-

 

Each user that owns a Spatial Index in one of the tablespaces to be transported must run the following command for every tablespace that is to be transported that contains the Spatial Data (not necessarily the same ones that contain the indexes).

 

exec SDO_UTIL.PREPARE_FOR_TTS('tablespace_name')

 

where tablespace_name in the name of the tablespace containing the Spatial tables.

 

Then use normal procedures to transport the tablespaces to the target database.

 

Once the tablespaces are transported, then the same users that performed the steps above, must perform the following -

 

exec SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

In our example, detailed above, SCOTT would run the following command before the transportable tablespace procedure is started -

 

exec SDO_UTIL.PREPARE_FOR_TTS('SPATIAL_DAT')

 

After the SPATIAL_DAT and SPATIAL_IDX tablespaces have been transported to the new database, SCOTT would run -

 

exec SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS

 

No further steps are needed.

 

Transporting tablespaces to a database running an earlier release of Oracle

 

In general, it is only possible to transport a tablespace to an earlier release of Oracle if the compatible parameter of the source database is the same or earlier than that of the target database.

 

I have tried to transport a tablespace containing a Spatial Index from an 11.2.0.1 database with compatible set to 10.2.0.0 to a 10.2.0.1 database with compatible set to 10.2.0.1. However, the metadata import failed with messages indicating an incompatibility of the index metadata.

 

After trying other permutation and combinations of database version and compatible parameters, I have come to the conclusion that you currently cannot transport a tablespace containing a Spatial Index to an earlier release of Oracle Database.


Gwen Lazenby

Gwen Lazenby is Principal Training Consultant at Oracle.


She is part of the UK and IE Core Tech Delivery team, teaching database administration, Spatial and Linux courses.


She is also a member of the OU EMEA SME team, with a special responsibility for Oracle Spatial.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Expert trainers from Oracle University share tips and tricks and answer questions that come up in a classroom.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today