Use Oracle 10g Data pump to reorganize table space

The Oracle 10g version is designed to redesign the operational functions of data entry and output to increase the ability to disconnect and connect in the input or output work. Minor changes to these features can facilitate the operation of the DBA table space.

Output table space as a whole unit

There are 3 modes of output and input in the past: dependent on object output, such as a single table of indexes, output of all objects of a user, and output of the entire database. However, tablespace is a difficult problem to handle. Objects of different users are stored in a given table space, but some objects may be stored in other tablespaces.

Therefore, the only workaround is to use the query data dictionary to find the list and its subordinate owners, and then use "Table-mode export" to output a single whole unit.

In Oracle 10g, all objects can be exported directly from the tablespace because they contain the output "Data Pump" (EXPDP). The tablespaces parameter allows you to specify the tablespace to be output.

Tablespaces=name [,...]

The above method is more useful if the database inherits many dictionary-based tablespaces. Also, you can re-create the table space locally and reduce fragmentation before you re-import the content.

Rename a data file name when you enter

If you move a database from one platform to another, the DBA needs to create a table space beforehand before the data file is imported. Why, then? Because the data file path in the dump file contains the original database operating system format, it is possible to generate an error when you enter the dump file into another operating system.

In the Oracle 10g version of the input (IMPDP) feature, its remap_datafile parameter renames the data file to eliminate the above problems. The format is as follows:

Remap_datafile=source_datafile:target_datafile

This option only works on full input, and the specified userid must be imp_full_database.

Change table space name when entering

The IMPDP feature allows objects to be loaded into different table spaces. This is a very complex operation until the 10g version appears. First, because there is no write permission, you must remove the limit of the original table space, and then set the table space.

During the re-enter process, objects in the original tablespace can be stored in the Set table space. When the task completes, you must restore the table space to its original state.

In 10g input, the Remap_tablespace parameter setting makes this work much simpler. Only the target table space needs to be capped, and no other criteria are required. The format of the parameter is as follows:

Remap_tablespace=source_tablespace:target_tablespace