EXP into the table space (2)

Jan 3 posted in Oracle (RSS), 127 views.

Using EXP / IMP data migration, often need to convert the table space operations, a simple record of what, EXP encountered during the conversion table space needed attention.
10g introduced the use of the name of the renamed tablespace to resolve the issue into the table space.
EXP conversion table space: http://yangtingkun.itpub.net/post/468/455820

The former article describes the spatial transformation of the table to solve problems, but this method can not handle, including the partition table, including table contains multiple paragraphs situation.
Look at a simple example:
SQL> CREATE TABLESPACE TEST DATAFILE 'F: \ ORACLE \ ORADATA \ TEST1 \ TEST01.DBF' SIZE10M;
Table space has been created.
SQL> CREATE TABLE T_PARTITION (ID NUMBER, NAME VARCHAR2 (30))
2 PARTITION BY RANGE (ID)
3 (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST,
4 PARTITION P2 VALUES LESS THAN (200) TABLESPACE TEST);
The table has been created.
Here To T_PARTITION migrated to another database, but the target database TEST does not exist in the table space:
F: \> exp test / test @ test1 file = t_partition.dmp tables = t_partition
Export: Release9.2.0.4.0 - Production on Sunday March 2 16:55:34 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to: Oracle9i Enterprise Edition Release9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining option
s
JServer Release 9.2.0.4.0 - Production
ZHS16GBK exported AL16UTF16 NCHAR character set and character set About to export specified tables via Conventional Path ...
.. Exporting table T_PARTITION
.. Exporting partition P1 0 rows are being exported
.. Exporting partition P2 0 rows are being exported in the absence of warning, the successful termination of export.
For 9i databases, due to geographical space where the table does not exist, even if the upward, as described in an article to set the default tablespace QUOTA, import still have to Baocuo follows:
SQL> CREATE USER TEST_IMP IDENTIFIED BY TEST_IMP DEFAULT TABLESPACE YANGTK;
The user has been created
SQL> GRANT CONNECT, RESOURCE TO TEST_IMP;
Grant succeeded.
SQL> ALTER USER TEST_IMP QUOTA UNLIMITED ON YANGTK;
The user has changed.
SQL> REVOKE UNLIMITED TABLESPACE FROM TEST_IMP;
Removed successfully.
F: \> imp test_imp / test_imp @ test file = t_partition.dmp tables = t_partition
Import: Release9.2.0.4.0 - Production on Sunday March 2 16:56:30 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connect to: Oracle9i Enterprise Edition Release9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining option
s
JServer Release 9.2.0.4.0 - Production
Through the conventional path export by EXPORT: V09.02.00 files created WARNING: This object is derived from the TEST, not the current user has been completed ZHS16GBK character set and AL16UTF16 NCHAR character set import
. Is to TEST the object into TEST_IMP
IMP-00017: the 959 due to ORACLE error, the following statement to fail
"CREATE TABLE" T_PARTITION "(" ID "NUMBER," NAME "VARCHAR2 (30)) PCTFREE 10 P"
"CTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE" SYSTEM "LOGGING PARTITION BY"
"RANGE (" ID ") (PARTITION" P1 "VALUES LESS THAN (100) PCTFREE 10 PCTUSED 4"
"0 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 FREELISTS 1 FREELIST GROUPS"
"1) TABLESPACE" TEST "LOGGING NOCOMPRESS, PARTITION" P2 "VALUES LESS THAN ("
"200) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE" TEST "LOGGING NOCOMPRESS)"
IMP-00003: encountered ORACLE error 959
ORA-00959: tablespace 'TEST' does not exist to terminate the import, but with a warning.
As mentioned earlier, the 9i the only way to manually create the table only the first, and then use the IGNORE = Y import implementation.
But for 10g, this can be through alternative methods to solve this problem:
SQL> CONN YANGTK / YANGTK @ YTK102
Is connected.
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
YANGTK
LOB_SPACE
Has selected eight rows.
At this time, if the direct import will also be an error message:
E: \> imp yangtk / yangtk @ ytk102 file = t_partition.dmp tables = t_partition
Import: Release10.2.0.1.0 - Production on Sunday March 2 17:17:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

To connect to: Oracle Database10gEnterprise Edition Release10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Through the conventional path by EXPORT: V09.02.00 create export file Warning: These objects are derived from the TEST, not the current user has been completed ZHS16GBK character set and AL16UTF16 NCHAR character set import
. Is to TEST the object into YANGTK
. Is to TEST the object into YANGTK
IMP-00017: due to ORACLE error 959, the following statement fails:
"CREATE TABLE" T_PARTITION "(" ID "NUMBER," NAME "VARCHAR2 (30)) PCTFREE 10 P"
"CTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE" SYSTEM "LOGGING PARTITION BY"
"RANGE (" ID ") (PARTITION" P1 "VALUES LESS THAN (100) PCTFREE 10 PCTUSED 4"
"0 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL 65536 FREELISTS 1 FREELIST GROUPS"
"1) TABLESPACE" TEST "LOGGING NOCOMPRESS, PARTITION" P2 "VALUES LESS THAN ("
"200) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (INITIAL65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE" TEST "LOGGING NOCOMPRESS)"
IMP-00003: encountered ORACLE error 959
ORA-00959: tablespace 'TEST' does not exist to terminate the import, but with a warning.
However, due to a change in tablespace 10g provides the name of SQL, can be a way to import partition table below:
SQL> ALTER TABLESPACE YANGTK RENAME TO TEST;
The table space has changed.
Here you can successfully import:
E: \> imp yangtk / yangtk @ ytk102 file = t_partition.dmp tables = t_partition
Import: Release10.2.0.1.0 - Production on Sunday March 2 17:11:55 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

To connect to: Oracle Database10gEnterprise Edition Release10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Through the conventional path by EXPORT: V09.02.00 create export file Warning: These objects are derived from the TEST, not the current user has been completed ZHS16GBK character set and AL16UTF16 NCHAR character set import
. Is to TEST the object into YANGTK
. Is to TEST the object into YANGTK
.. Importing partition is "T_PARTITION": "P1" to import the 0-line
.. Importing partition is "T_PARTITION": "P2" imported 0 OK to terminate the import, there was no warning.
After importing you can modify the name of the method of the table space modify the name of the table space will be the original name:
SQL> ALTER TABLESPACE TEST RENAME TO YANGTK;
The table space has changed.
In this way the import, can be solved contain multiple segment table, the table can not be conducted through the normal space conversion issue. However, the above methods provide only a table without prior construction of a feasible mode to import.
Not to mention the name change tablespace for a database system to provide services outside the impact of this action is to modify the table space itself may not be earlier than the small amount of work to build a table number.
Considering several circumstances, if the table contains multiple partitions, each partition where the tablespace different from the hope that all the partitions are moved into the target table space. So if the target database, only the target table space exists, and corresponds to the partition table for each table space partition does not exist, only by modifying a tablespace name into a partition, re-modify the table space name, and then into a partition way to achieve efficiency and workload was relatively large.
As for the relocation table, the original partition table partition where the table space is the target database exists, and when imported into the same name do not want partition, but to a unified import to the target table space. At this time we need to do is to first space will exist to rename the table, and then changed to target just the name of the tablespace, import partition, also need to modify the target table space will come, and then the name of the source table space recovery. In short, this method for many of the relocation is a very laborious operation.
Moreover, this method has a limitation that is generally used for 9i or lower version to migrate to 10g or later, when the use of. This is because only the 10g version of the above support for the table space before re-naming, which requires the target database must be in the 10g version of the above. And if the version of the source database of more than 10g, you can pump through the data conversion table space, there is no need to use such a cumbersome way, so this method will limit the data source versions 9i and below.