An Analysis of the tablespace movement method in Oracle databases

Database movement is also a common phenomenon. After all, there are various reasons to force us to move. So how can we move faster? See the following.

When using an Oracle database, data in the database is often moved from one database to another. If the migrated data volume is not large, we can use EXPORT/IMPORT to EXPORT/IMPORT data. However, if the amount of data to be moved is very large, the data movement using this method is very slow. In Oracle 11g version, the table space can be moved. by moving the tablespace, the data migration speed can be greatly reduced and the work efficiency can be improved.

Pay attention to the following points when moving the table space:

(1) The source database and target database must be on the same hardware platform. For example, we can move tablespaces between Oracle databases on the Sun Solaris platform or between Oracle databases on the Windows 2000 platform, but not between Sun Solaris/Windows 2000.

(2) The character sets of the source database and target database must be the same as those of the national character set.

(3) The tablespace cannot be moved to the target database with the same name as the tablespace.

(4) When moving the tablespace between Oracle8i, the data block size of the source database and the target database must be the same.

The following uses Oracle for Windows 2000 as an example to describe how to move a tablespace.

1. Check the tablespace to be moved

When a tablespace is moved, if the data in the tablespace to be moved does not have a reference relationship with the data in other tablespaces, The tablespace can be directly moved; if the data in the removed tablespace has a reference relationship with the data in other tablespaces, when moving the tablespace, any tablespace with reference to the moved tablespace should also be moved, otherwise, the data in the tablespace to be migrated to the destination is incomplete. For example, the tablespace local contains table A1, while the tablespace local2 contains the index IDX_A1 of table A1. If you want to move the tablespace local, you should also move the tablespace local2 at the same time, in this case, the tablespace local and local2 are self-contained. Before moving a tablespace, you can use the following method to check whether the removed tablespace is a self-contained tablespace.

 
 

  
  
SQL>execute dbms_tts.transport_set_check(ts_list=>'local',   
  
  
incl_constraints=>TRUE); 
 
 

The ts_list parameter is used to specify the tablespace to be moved. incl_constraints is used to specify whether to check integrity constraints. After dbms_tts.transport_set_check is executed, information about the self-contained tablespace is saved to the temporary table transport_set_violations. If no information is returned when you query the table, it indicates that the table space is self-contained. Otherwise, the cause of non-self-contained table space is returned:

Ii. Export tablespace Information

To maintain data file consistency, you should first convert the self-contained tablespace to read-only before exporting the data dictionary information of the self-contained tablespace, so that the tablespace information will not change.

 
 

  
  
SQL>alter tablespace local read only;   
  
  
SQL>alter tablespace local2 read only; 
 
 

When the tablespace is set to a read-only off state, a checkpoint is issued on the tablespace data file and its content does not change. In this case, you can export the tablespace information. To export a tablespace is only to export the data dictionary information related to it, rather than to export any data of the tablespace. You can use the following operating system command to export the information of the tablespace local and local2.

 
 

  
  
D:>EXP transport_tablespace=y tablespaces=local,local2 file=  
  
  
expdat.dmp 
 
 

Username: internal/oracle @ test as sysdba
Parameter: The transport_tablespace option. "Y" indicates the tablespace information to be exported. "tablespaces" indicates the tablespace to be exported. "file" indicates the file to store the exported information.

After the tablespace information is exported, run the operating system command to copy the tablespace data file and the exported file (expdat. dmp) to the corresponding directory of the machine where the target database is located.

Iii. Import tablespace

After copying the tablespace data file and exported file to the machine where the target database is located, You can import the tablespace information to the target database. Imported self-contained tablespace actually loads data dictionary information from the exported file to the target data, such as the tablespace name, the data file corresponding to the tablespace, and the Data Object Name. The specific operating system commands are as follows:

 
 

  
  
D:>IMP transport_tablespace=y datafiles= 'd:localdblocal1.dbf'   
  
  
,'d:localdblocal2.dbf’ 
 
 

Username: internal/oracle @ test as sysdba

After the preceding command is executed, the local and local2 tablespaces and all their data objects are imported into the target database.

Note: When the tablespace is moved in Oracle, the owner of all data objects stored in the source tablespace must exist in the target database. If not, users should be created before the tablespace is imported, then import the tablespace.