Orace expdp ORA ORA-39167

解决方法:

Applies to:

Enterprise Manager for RDBMS - Version: 10.2.0
Oracle Server - Enterprise Edition - Version: 10.2.0
Oracle Server - Personal Edition - Version: 10.2.0
Oracle Server - Standard Edition - Version: 10.2.0
This problem can occur on any platform.

Symptoms

You run an Export DataPump job in tablespace mode, with the 10.2.0.x Export DataPump utility:

expdp system/manager directory=my_dir dumpfile=exp_t.dmp logfile=exp_t.log tablespaces=testdat, testind

Although the tables are exported, the export job reports an error:

Export: Release 10.2.0.1.0 - Production on Friday, 25 November, 2005 9:19:45
...
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SYSTEM"."TESTTAB"                              0 KB       0 rows
ORA-39167: Tablespace TESTIND was not found.
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
...

Changes

A similar export job did not have any errors when exported from an Oracle10g Release1 (10.1.0.x) database.

Cause

According to the manual B14215-01 "Oracle Database Utilities 10g Release 2 (10.2)", Chapter 2 "Data Pump Export", section "Parameters Available in Export's Command-Line Mode":

"In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, its dependent objects are also unloaded. If any part of a table resides in the specified set, then that table and all of its dependent objects are exported."

The tablespace TESTIND is also specified at the TABLESPACES parameter. However, this tablespace does not contain any tables. Example:

CONNECT system/manager
CREATE TABLESPACE testdat DATAFILE 'D:/testdat01.dbf' SIZE 10m;
CREATE TABLESPACE testind DATAFILE 'D:/testind01.dbf' SIZE 10m;

CREATE TABLE testtab (nr NUMBER) TABLESPACE testdat;
CREATE INDEX testind ON testtab(nr) TABLESPACE testind;

expdp system/manager directory=my_dir dumpfile=exp_t.dmp logfile=exp_t.log tablespaces=testdat, testind

The index SYSTEM.TESTIND will be exported because it is a dependant object of the table SYSTEM.TESTTAB and this table is located in the tablespace TESTDAT that is specfified at the Export DataPump TABLESPACES parameter.

Tablespace TESTIND was explicitly specified at the TABLESPACES parameter during export. As no tables were stored in this tablespace, the export shows the error: ORA-39167: Tablespace TESTIND was not found.

Solution

Determine which objects are located in the tablespaces you want to export:

SET lines 100 pages 100
COL owner.segment_name FOR a45
SELECT tablespace_name, segment_type,  owner||'.'||segment_name 
   "OWNER.SEGMENT_NAME"
  FROM dba_segments
 WHERE tablespace_name IN ('TESTDAT', 'TESTIND') 
   AND segment_name NOT LIKE 'BIN$%'
 ORDER BY 1,2;

TABLESPACE_NAME                SEGMENT_TYPE       OWNER.SEGMENT_NAME
------------------------------ ------------------ ----------------------
TESTDAT                        TABLE              SYSTEM.TESTTAB
TESTIND                        INDEX              SYSTEM.TESTIND

Determine on which tables the indexes were created:

COL table_owner.table_name FOR a45
COL owner.index_name FOR a45
SELECT owner||'.'||index_name "OWNER.INDEX_NAME", 
       table_owner||'.'||table_name "TABLE_OWNER.TABLE_NAME"
 FROM dba_indexes
 WHERE tablespace_name IN ('TESTDAT', 'TESTIND');

OWNER.INDEX_NAME                              TABLE_OWNER.TABLE_NAME
--------------------------------------------- --------------------------
SYSTEM.TESTIND                                SYSTEM.TESTTAB

1. Ignore the error ORA-39167 (Tablespace xxx was not found) if that tablespace is empty, or only contains indexes. When running an Export DataPump job in tablespace mode, it would have been sufficient to specify the tablespaces in which tables are stored. E.g.:

expdp system/manager directory=my_dir dumpfile=exp_t.dmp logfile=exp_t.log tablespaces=testdat

This will export all tables in tablespace TESTDAT, but also their dependant objects like indexes (regardless in which tablespace this index is located).

Note that if another index was located in the tablespace TESTIND or TESTDAT and for which the base table was not stored in TESTDAT (or TESTIND), that this index will not be exported.

or:

2. If the objective is to export all tables with their dependant objects that are stored in the tablespaces TESTDAT and TESTIND, and in addition, to export the other indexes that are stored in these tablespaces, then you have to run two export jobs. Example:

2.1. First run an Export DataPump job to export all tables and related objects in the tablespaces TESTDAT and TESTIND:

expdp system/manager directory=my_dir dumpfile=exp_t.dmp logfile=exp_t.log tablespaces=testdat, testind

2.2. Determine which additional indexes are stored in the tablespaces TESTDAT and TESTIND and for which the base table is not located in these tablespaces (use queries mentioned above).

2.3. Run another Export DataPump job with the INCLUDE parameter to export these indexes with their statistics. E.g.:

Export parameter file: exp.par
------------------------------
DIRECTORY = my_dir
DUMPFILE  = exp_i.dmp
LOGFILE   = exp_i.log
# include all schema's that own indexes in TESTDAT and TESTIND:
SCHEMAS   = system, scott
# include all other index names that are stored in TESTDAT and TESTIND:
INCLUDE   = INDEX:"IN ('IND1', 'IND2', 'IND3')"

expdp system/manager parfile=exp.par