export and import

Subject:	Oracle Server - Export and Import FAQ
	Doc ID:	Note:175624.1	Type:	FAQ
	Last Revision Date:	08-FEB-2006	Status:	PUBLISHED

In this Document
Purpose
Questions and Answers
GENERIC
COMPATIBILITY
PARAMETERS
PERFORMANCE
LARGE FILES
TABLESPACE
ORA-942
NLS
INTERMEDIA OBJECTS
ADVANCED QUEUING OBJECTS
REPLICATION OBJECTS
FREQUENTLY REPORTED EXPORT/IMPORT ERRORS
EXPORT TOP ISSUES CAUSED BY DEFECTS
IMPORT TOP ISSUES CAUSED BY DEFECTS

---

Applies to:

Enterprise Manager for RDBMS - Version: 1.6 to 10.2.0
Oracle Server - Enterprise Edition - Version: 7.3.0 to 10.2.0
Oracle Server - Personal Edition - Version: 7.3.0 to 10.2.0
Oracle Server - Standard Edition - Version: 7.3.0 to 10.2.0
Information in this document applies to any platform.

Purpose

@ This Note is linked to the MetaLink Export/Import Service Request template.
@ ITAR_TEMPLATE
@ See also Note 267927.1 "Export/Import Knowledge Browser Product Page"
@ .

PURPOSE
This Frequently Asked Questions (FAQ) provides common Export and Import issues (including Export DataPump and Import DataPump) in the following sections:
Generic, Compatibility, Parameters, Performance, Large Files, Tablespace, ORA-942, NLS, InterMedia, Advanced Queueing, Replication, Frequent Errors, Top Export Defects, and Top Import Defects.

Questions and Answers

GENERIC

What is actually happening when I export and import data?
Note 61949.1 "Overview of Export and Import in Oracle7"

What is important when doing a full database export or import?Note 10767.1 "How to perform full system Export/Import"

Can data corruption occur using export & import (version 8.1.7.3 to 9.2.0)?Note 199416.1 "ALERT: Client Program May Give Incorrect Query Results (EXP Can Produce Dump File with Corrupted Data)"
Note 223399.1 "ALERT: EXPORT with large BUFFER Can Silently Produce a Dump File with Corrupted Data"

How to Connect AS SYSDBA when Using Export or Import?Note 277237.1 "How to Connect AS SYSDBA when Using Export or Import"

COMPATIBILITY

Which version should I use when moving data between different database releases?Note 132904.1 "Compatibility Matrix for Export & Import Between Different Oracle Versions"
Note 277650.1 "How to Use Export and Import when Transferring Data Across Platforms or Across 32-bit and 64-bit Servers"
Note 291024.1 "Compatibility and New Features when Transporting Tablespaces with Export and Import"
Note 76542.1 "NT: Exporting from Oracle8, Importing Into Oracle7"

How to resolve the IMP-69 error when importing into a database?Note 163334.1 "Import Gets IMP-00069 when Importing 8.1.7 Export"
Note 1019280.102 "IMP-69 on Import"

PARAMETERS

What is the difference between a Direct Path and a Conventional Path Export?Note 155477.1 "Parameter DIRECT: Conventional Path Export versus Direct Path Export"

What is the meaning of the Export parameter CONSISTENT=Y and when should I use it?Note 113450.1 "When to Use CONSISTENT=Y During an Export"

How to filter data that needs to be exported/imported ?Note 91864.1 "Query= Syntax in Export in 8i"
Note 277010.1 "Export/Import DataPump Parameters QUERY - How to Specify a Query in Oracle10g Export DataPump and Import DataPump"
Note 341733.1 "Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects"

How to create multiple export dumpfiles instead of one large file?Note 290810.1 "Parameter FILESIZE - Make Export Write to Multiple Export Files"

PERFORMANCE

Import takes so long to complete. How can I improve the performance of Import?Note 93763.1 "Tuning Considerations when Import is slow"

Why has export performance decreased after creating tables with LOB columns?Note 281461.1 "Export and Import of Table with LOB Columns (like CLOB and BLOB) has Slow Performance"

LARGE FILES

Which commands to use for solving Export dump file problems on UNIX platforms?Note 30528.1 "Large File Issues (2Gb+) when Using Export (EXP-2 EXP-15), Import (IMP-2 IMP-21), or SQL*Loader"

How to solve the EXP-15 and EXP-2 errors when Export dump file is larger than 2Gb?Note 30528.1 "Large File Issues (2Gb+) when Using Export (EXP-2 EXP-15), Import (IMP-2 IMP-21), or SQL*Loader"
Note 290810.1 "Parameter FILESIZE - Make Export Write to Multiple Export Files"
Note 62427.1 "2Gb or Not 2Gb - File limits in Oracle"

How to export to a tape device by using a named pipe?Note 30428.1 "Exporting to Tape on Unix System"

TABLESPACE

How to transport tablespace between different versions?Note 291024.1 "Compatibility and New Features when Transporting Tablespaces with Export and Import"

How to move tables to a different tablespace and/or different user?Note 1012307.6 "Moving Tables Between Tablespaces Using EXPORT/IMPORT"
Note 1068183.6 "How to change the default tablespace when importing using the INDEXFILE option"

How can I export all tables of a specific tablespace?Note 1039292.6 "How to Export Tables for a specific Tablespace"

ORA-942

How to resolve an ORA-942 during import of the ORDSYS schema?Note 109576.1 "Full Import shows Errors when adding Referential Constraint on Cartrige Tables"

How to resolve an ORA-942 during import of a snapshot (log) into a different schema?Note 1017292.102 "IMP-00017 IMP-00003 ORA-00942 USING FROMUSER/TOUSER ON SNAPSHOT [LOG] IMPORT"

How to resolve an ORA-942 during import of a trigger on a renamed table?Note 1020026.102 "ORA-01702, ORA-00942, ORA-25001, When Importing Triggers"

How to resolve an ORA-942 during import of one specific table?Note 1013822.102 "ORA-00942: ON TABLE LEVEL IMPORT"

NLS

Which effect has the client's NLS_LANG setting on an export and import?Note 227332.1 "NLS considerations in Import/Export - Frequently Asked Questions"
Note 15656.1 "Export/Import and NLS Considerations"

How to prevent the loss of diacritical marks during an export/import?Note 96842.1 "Loss Of Diacritics When Performing EXPORT/IMPORT Due To Incorrect Charactersets"

INTERMEDIA OBJECTS

How to solve an EXP-78 when exporting metadata for an interMedia Text index?Note 130080.1 "Problems with EXPORT after upgrading from 8.1.5 to 8.1.6"

I dropped the ORDSYS schema, but now I get ORA-6550 and PLS-201 when exporting? Note 120540.1 "EXP-8 PLS-201 After Drop User ORDSYS"

ADVANCED QUEUING OBJECTS

Why does export show ORA-1403 and ORA-6512 on an AQ object, after an upgrade?Note 159952.1 "EXP-8 and ORA-1403 When Performing A Full Export"

How to resolve export errors on DBMS_AQADM_SYS and DBMS_AQ_SYS_EXP_INTERNAL?Note 114739.1 "ORA-4068 while performing full database export"

REPLICATION OBJECTS

How to resolve import errors on DBMS_IJOB.SUBMIT for Replication jobs?Note 137382.1 "IMP-3, PLS-306 Unable to Import Oracle8i JobQueues into Oracle8"

How to reorganize Replication base tables with Export and Import?Note 1037317.6 "Move Replication System Tables using Export/Import for Oracle 8.X"

FREQUENTLY REPORTED EXPORT/IMPORT ERRORS

EXP-00002: Error in writing to export file
Note 30528.1 "Large File Issues (2Gb+) when Using Export (EXP-2 EXP-15), Import (IMP-2 IMP-21), or SQL*Loader"

EXP-00003: No storage definition found for segment(%s,%s) (EXP-3 EXP-0)
Note 274076.1 "EXP-00003 When Exporting From 9.2.0.5.0 or any Higher Release with a Pre-9.2.0.5.0 Export Client"
Note 124392.1 "EXP-3 while exporting Rollback Segment definitions during FULL Database Export"

EXP-00067: "Direct path cannot export %s which contains object or lob data."
Note 1048461.6 "EXP-00067 PERFORMING DIRECT PATH EXPORT"

EXP-00079: Data in table %s is protected (EXP-79)
Note 277606.1 "How to Prevent EXP-00079 or EXP-00080 Warning (Data in Table xxx is Protected) During Export"

EXP-00091: Exporting questionable statistics
Note 159787.1 "9i: Import STATISTICS=SAFE"

IMP-00016: Required character set conversion (type %lu to %lu) not supported
Note 168066.1 "IMP-16 When Importing Dumpfile into a Database Using Multibyte Characterset"

IMP-00020: Long column too large for column buffer size
Note 148740.1 "ALERT: Export of table with dropped functional index may cause IMP-20 on import"

ORA-00904: Invalid column name (EXP-8 ORA-904 EXP-0)
Note 106155.1 "EXP-00008 ORA-1003 ORA-904 During Export"
Note 172220.1 "Export of Database fails with EXP-00904 and ORA-01003"
Note 158048.1 "Oracle8i Export Fails on Synonym Export with EXP-8 and ORA-904"
Note 130916.1 "ORA-904 using EXP73 against Oracle8/8i Database"
Note 1017276.102 "Oracle8i Export Fails on Synonym Export with EXP-8 and ORA-904"

ORA-00932: Inconsistent datatypes: expected BLOB, CLOB got CHAR (EXP-56 ORA-932 EXP-0)
Note 339938.1 "Full Export From 10.2.0.1 Aborts With EXP-56 ORA-932 (Inconsistent Datatypes) EXP-0"

ORA-01406: Fetched column value was truncated (EXP-8 ORA-1406 EXP-0)
Note 163516.1 "EXP-0 and ORA-1406 during Export of Object Types"

ORA-01422: Exact fetch returns more than requested number of rows
Note 221178.1 "PLS-201 and ORA-06512 at 'XDB.DBMS_XDBUTIL_INT' while Exporting Database"
Note 256548.1 "Export of Database with XDB Throws ORA-1422 Error"

ORA-01555: Snapshot too old
Note 113450.1 "When to Use CONSISTENT=Y During an Export"

ORA-04030: Out of process memory when trying to allocate %s bytes (%s,%s) (IMP-3 ORA-4030 ORA-3113)
Note 165016.1 "Corrupt Packages When Export/Import Wrapper PL/SQL Code"

ORA-06512: at "SYS.DBMS_STATS", line ... (IMP-17 IMP-3 ORA-20001 ORA-6512)
Note 123355.1 "IMP-17 and IMP-3 errors referring dbms_stats package during import"

ORA-29344: Owner validation failed - failed to match owner 'SYS'
Note 294992.1 "Import DataPump: Transport Tablespace Fails with ORA-39123 and 29344 (Failed to match owner SYS)"

ORA-29516: Aurora assertion failure: Assertion failure at %s (EXP-8 ORA-29516 EXP-0)
Note 114356.1 "Export Fails With ORA-29516 Aurora Assertion Failure EXP-8"

PLS-00103: Encountered the symbol "," when expecting one of the following ... (IMP-17 IMP-3 ORA-6550 PLS-103)
Note 123355.1 "IMP-17 and IMP-3 errors referring dbms_stats package during import"
Note 278937.1 "Import DataPump: ORA-39083 and PLS-103 when Importing Statistics Created with Non "." NLS Decimal Character"

PLS-00306: wrong number or types of arguments in call to ... (ORA-31693 ORA-02354 ORA-00604 ORA-06550
Note 313055.1 "Expdp on AIX or Tru64 Fails With Errors PLS-00306 in Procedure GET_DPSTRM_MD"

EXPORT TOP ISSUES CAUSED BY DEFECTS

Bug 1874468 (not a public bug)
Release: 8.1.7.2 and below
Problem: Export may fail with ORA-1406 when exporting object type definitions
Solution: apply patch-set 8.1.7.3
Workaround: no, see Note 163516.1 "EXP-0 and ORA-1406 during Export of Object Types"

Bug 1098503Release: Oracle8i (8.1.x) and Oracle9i (9.x)
Problem: EXP-79 when Exporting Protected Tables
Solution: this is not a defect
Workaround: N/A, see Note 277606.1 "How to Prevent EXP-00079 or EXP-00080 Warning (Data in Table xxx is Protected) During Export"

Bug 2410612Release: 8.1.7.3 and higher and 9.0.1.2 and higher
Problem: Conventional export may produce an export file with corrupt data
Solution: 8.1.7.5 and 9.2.0.x or check for Patch 2410612 (for 8.1.7.x), Patch 2449113 (for 9.0.1.x)
Workaround: yes, see Note 199416.1 "ALERT: Client Program May Give Incorrect Query Results (EXP Can Produce Dump File with Corrupted Data)"

Bug 1347528 (not a public bug)
Release: Oracle8i (8.1.x)
Problem: Full database export fails with EXP-3: no storage definition found for segment
Solution: Oracle9i (9.x)
Workaround: yes, see Note 124392.1 "EXP-3 while exporting Rollback Segment definitions during FULL Database Export"

Bug 2900891Release: 9.0.1.4 and below and 9.2.0.3 and below
Problem: Export with 8.1.7.3 and 8.1.7.4 from Oracle9i fails with invalid identifier SPOLICY (EXP-8 ORA-904 EXP-0)
Solution: 9.2.0.4 or 9.2.0.5
Workaround: yes, see Bug 2900891 how to recreate view sys.exu81rls

Bug 2685696Release: 9.2.0.3 and below
Problem: Export fails when exporting triggers in call to XDB.DBMS_XDBUTIL_INT (EXP-56 ORA-1422 ORA-6512)
Solution: 9.2.0.4 or check for Patch 2410612 (for 9.2.0.2 and 9.2.0.3)
Workaround: yes, see Note 221178.1 "ORA-01422 ORA-06512: at "XDB.DBMS_XDBUTIL_INT" while exporting full database"

Note 221178.1 "ORA-01422 ORA-06512: at "XDB.DBMS_XDBUTIL_INT" while exporting full database"

Bug 2919120Release: 9.2.0.4 and below
Problem: Export fails when exporting triggers in call to XDB.DBMS_XDBUTIL_INT (EXP-56 ORA-1422 ORA-6512)
Solution: 9.2.0.5 or check for Patch 2919120 (for 9.2.0.4)
Workaround: yes, see Note 256548.1 "Export of Database with XDB Throws ORA-1422 Error"

IMPORT TOP ISSUES CAUSED BY DEFECTS

Bug 1335408Release: 8.1.7.2 and below
Problem: Bad export file using a locale with a ',' decimal seperator (IMP-17 IMP-3 ORA-6550 PLS-103)
Solution: apply patch-set 8.1.7.3 or 8.1.7.4
Workaround: yes, see Note 123355.1 "IMP-17 and IMP-3 errors referring DBMS_STATS package during import"

Bug 1879479Release: 8.1.7.2 and below and 9.0.1.2 and below
Problem: Export of a wrapped package can result in a corrupt package being imported (IMP-3 ORA-4030 ORA-3113 ORA-7445 ORA-600[16201]).
Solution: in Oracle8i with 8.1.7.3 and higher; in Oracle9iR1 with 9.0.1.3 and higher
Workaround: no, see Note 165016.1 "Corrupt Packages When Export/Import Wrapper PL/SQL Code"

Bug 2067904Release: Oracle8i (8.1.7.x) and 9.0.1.2 and below
Problem: Trigger-name causes call to DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY to fail during Import (IMP-17 IMP-3 ORA-931 ORA-23308 ORA-6512).
Solution: in Oracle9iR1 with patchset 9.0.1.3
Workaround: yes, see Note 239821.1 "ORA-931 or ORA-23308 in SET_TRIGGER_FIRING_PROPERTY on Import of Trigger in 8.1.7.x and 9.0.1.x"

Note 239821.1 "ORA-931 or ORA-23308 in SET_TRIGGER_FIRING_PROPERTY on Import of Trigger in 8.1.7.x and 9.0.1.x"

Bug 2854856Release: Oracle8i (8.1.7.x) and 9.0.1.2 and below
Problem: Schema-name causes call to DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY to fail during Import (IMP-17 IMP-3 ORA-911 ORA-6512).
Solution: in Oracle9iR2 with patchset 9.2.0.4
Workaround: yes, see Note 239890.1 "ORA-911 in SET_TRIGGER_FIRING_PROPERTY on Import of Trigger in 8.1.7.x and Oracle9i"

Note 239890.1 "ORA-911 in SET_TRIGGER_FIRING_PROPERTY on Import of Trigger in 8.1.7.x and Oracle9i"

Keywords

' IMPORTING~DATA' ' DATAPUMP'

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/308563/viewspace-171967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-171967/