具体如下：
$ impdp parfile=reporter_new1.par
Import: Release 10.2.0.3.0 - 64bit Production on Wednesday, 02 June, 2010 21:24:34
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Master table "REPORTER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "REPORTER"."SYS_IMPORT_TABLE_01": parfile=reporter_new1.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31696: unable to export/import TABLE_DATA:"REPORTER"."REPORTER_STATUS":"REPORTER_STATUS_2010_MAY" using client specified AUTOMATIC method
Job "REPORTER"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:24:46
$ more reporter_new1.par
userid=reporter
directory=EXP_DIR
dumpfile=reporter%U.dmp
logfile=reporter_new.log
CONTENT=DATA_ONLY
table_exists_action=append
TABLES=REPORTER_STATUS:REPORTER_STATUS_2010_MAY
SQL> desc reporter_status
Name Null? Type
----------------------------------------- -------- ----------------------------
IDENTIFIER VARCHAR2(255)
SERIAL NUMBER(16)
OWNERUID NUMBER(16)
OWNERGID NUMBER(16)
STATECHANGE DATE
FIRSTOCCURRENCE DATE
LASTOCCURRENCE DATE
INTERNALLAST DATE
CLEAREDTIMESTAMP DATE
EXTERNALTICKETNUM VARCHAR2(64)
INTERNALTICKETNUM VARCHAR2(64)
TALLY NUMBER(16)
FLASH NUMBER(16)
SEVERITY NUMBER(1)
ACKNOWLEDGED NUMBER(16)
SUMMARY VARCHAR2(255)
ALERTKEY VARCHAR2(64)
AGENT VARCHAR2(64)
TYPE NUMBER(16)
IPADDRESS VARCHAR2(64)
CLASS NUMBER(16)
CUSTOMERNAME VARCHAR2(64)
NODE VARCHAR2(64)
MANAGER VARCHAR2(64)
ALERTGROUP VARCHAR2(64)
DURATION NUMBER(16)
FREQUENCY NUMBER(16)
GRADE NUMBER(16)
DATACENTER VARCHAR2(64)
NETWORK VARCHAR2(64)
CAGE VARCHAR2(64)
RACK VARCHAR2(64)
OPERATINGSYSTEM VARCHAR2(64)
SYSTEMPURPOSE VARCHAR2(64)
PRODUCTOFFERING VARCHAR2(64)
SLA VARCHAR2(64)
RCATEGORY VARCHAR2(64)
RTYPE VARCHAR2(64)
RITEM VARCHAR2(64)
TICKETFLAG NUMBER(16)
CUSTOMERPOC VARCHAR2(64)
CUSTOMERPOCNUMBER VARCHAR2(64)
FQDN VARCHAR2(255)
DEVSTATUS VARCHAR2(64)
REDUNDANT VARCHAR2(64)
ORIGINALMESSAGE VARCHAR2(255)
INTERFACE VARCHAR2(64)
SWITCH VARCHAR2(64)
MODULE VARCHAR2(64)
PORT VARCHAR2(64)
MANAGINGHOST VARCHAR2(64)
CUSTOMERSTATUS NUMBER
LASTMODIFIED DATE
DELETEDAT DATE
ORIGINALSEVERITY NUMBER(16)
SERVERNAME NOT NULL VARCHAR2(64)
SERVERSERIAL NOT NULL NUMBER(16)
MAINTFLAG NUMBER
IMP1 NUMBER
IMP2 NUMBER
IMPTEXT VARCHAR2(64)
TRACKINGCODE NUMBER(8)
CONTRACTTITLE VARCHAR2(64)
PARTNERNAME VARCHAR2(64)
SUPPORTDIRECTOR VARCHAR2(64)
MAST VARCHAR2(64)
CTARECEIVETIME DATE
STARTTIME DATE
ENDTIME DATE
OWNERNAME VARCHAR2(64)
但文档上说的是这样的：
| Bug 4239903: IMPDP FAILED IF LONG DATATYPE IS THERE IN THE TABLE | |||||
|
| |||||
| Type | B - Defect | Fixed in Product Version | - |
| Severity | 2 - Severe Loss of Service | Product Version | 10.1.0.2 |
| Status | 92 - Closed, Not a Bug | Platform | 23 - Oracle Solaris on SPARC (64-bit) |
| Created | 14-Mar-2005 | Platform Version | 2.8 |
| Updated | 18-Jun-2007 | Base Bug | - |
| Database Version | 10.1.0.2 | ||
| Affects Platforms | Generic | ||
| Product Source | Oracle |
Related Products
| Line | Oracle Database Products | Family | Oracle Database |
| Area | Oracle Database | Product | 5 - Oracle Server - Enterprise Edition |
Hdr: 4239903 10.1.0.2 RDBMS 10.1.0.2 DATA PUMP IMP PRODID-5 PORTID-23
Abstract: IMPDP FAILED IF LONG DATATYPE IS THERE IN THE TABLE
*** 03/14/05 11:10 pm ***
TAR:
----
4235402.996
PROBLEM:
--------
Impdp failed if LONG cloums are there.
DIAGNOSTIC ANALYSIS:
--------------------
Problem reproduces as stated. Not sure if this is a limitation
WORKAROUND:
-----------
RELATED BUGS:
-------------
REPRODUCIBILITY:
----------------
TEST CASE:
----------
Test case
---------
create user emauser identified by emauser;
grant connect,resource to emauser;
create directory eventlogdir as '/var/tmp/eventlogdir;
grant read, write on directory eventlogdir to emauser;
create table tab_event_log
( EVENTLOG_ID NUMBER(15) NOT NULL PRIMARY KEY,
HOST_ID CHAR(8) ,
EVENTOCCURE_SECONDS VARCHAR2(100),
EVENTOCCURE_TIME DATE ,
MODULE_NAME VARCHAR2(200),
EVENT_NAME VARCHAR2(500),
TYPE VARCHAR2(100),
OCCURE_NUMBER VARCHAR2(30) ,
AFFECT_OBJECT VARCHAR2(200),
DETAIL_INFORMATION LONG )
/
insert into tab_event_log values
(&1,'&a','&b',sysdate,'&c','&d','&e','&f','&g','&lg');
- Inserted 3-4 test rows.
C:/apadhi>expdp emauser/emauser DIRECTORY=eventlogdir DUMPFILE=withLong.dmp
CONTENT=DATA_ONLY TABLES=tab_event_log
C:/apadhi>impdp emauser/emauser DIRECTORY=eventlogdir DUMPFILE=withLong.dmp
TABLE_EXISTS_ACTION=APPEND CONTENT=DATA_ONLY TABLES=tab_event_log
Import: Release 10.1.0.2.0 - Production on Tuesday, 15 March, 2005 12:22
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options
Master table "EMAUSER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EMAUSER"."SYS_IMPORT_TABLE_01": emauser/********
DIRECTORY=eventlogdi
r DUMPFILE=withLong.dmp TABLE_EXISTS_ACTION=APPEND CONTENT=DATA_ONLY
TABLES=tab_event_log
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
ORA-31696: unable to export/import TABLE_DATA:"EMAUSER"."TAB_EVENT_LOG" using
client specified AUTOMATIC method
Job "EMAUSER"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 12:23
## Failed with ORA-31696
But the same will work fine if there is no LONG column
create table tab_event_log1
( EVENTLOG_ID NUMBER(15) NOT NULL PRIMARY KEY,
HOST_ID CHAR(8) ,
EVENTOCCURE_SECONDS VARCHAR2(100),
EVENTOCCURE_TIME DATE ,
MODULE_NAME VARCHAR2(200),
EVENT_NAME VARCHAR2(500),
TYPE VARCHAR2(100),
OCCURE_NUMBER VARCHAR2(30) ,
AFFECT_OBJECT VARCHAR2(200))
/
C:/apadhi>expdp emauser/emauser DIRECTORY=eventlogdir
DUMPFILE=withoutLoNg.dmp CONTENT=DATA_ONLY TABLES=tab_event_log1
Truncated the table
C:/apadhi>impdp emauser/emauser DIRECTORY=eventlogdir
DUMPFILE=withoutLoNg.dmp TABLE_EXISTS_ACTION=APPEND CONTENT=DATA_ONLY
TABLES=tab_event_log1
Import: Release 10.1.0.2.0 - Production on Tuesday, 15 March, 2005 12:29
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options
Master table "EMAUSER"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "EMAUSER"."SYS_IMPORT_TABLE_01": emauser/********
DIRECTORY=eventlogdir DUMPFILE=withoutLoNg.dmp TABLE_EXISTS_ACTION=APPEND
CONTENT=DATA_ONLY TABLES=tab_event_log1
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "EMAUSER"."TAB_EVENT_LOG1" 7.562 KB 2
rows
Job "EMAUSER"."SYS_IMPORT_TABLE_01" successfully completed at 12:29
STACK TRACE:
------------
SUPPORTING INFORMATION:
-----------------------
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 03/15/05 02:14 am *** (CHG: Asg->NEW OWNER OWNER)
*** 03/15/05 05:59 am ***
BDE Screening
~~~~~~~~~~~~~
** NB: Minimal screening in effect due to workload.
Testcase
~~~~~~~~~~~~~~~~~
Files: BDETC.tar.Z
Steps:
sqlplus /nolog @setup
Creates a user TC with table to export
sqlplus /nolog @tc
Loads several rows to the table, exports it using expdp
then attempts to import it which fails.
Reproduced
~~~~~~~~~~
Reproduced in 10.1.0.3
Reproduced in RDBMS_MAIN_LINUX_050301
Diagnostic notes
~~~~~~~~~~~~~~~~
LONGS are a problem for NETWORK_LINK but I cannot see an restriction
that expdp/impdp should not be able to exp/imp LONG column data
when no network link is involved.
*** 03/15/05 06:01 am *** (CHG: Sta->11)
*** 03/15/05 06:01 am *** (CHG: Asg->NEW OWNER OWNER)
*** 03/15/05 06:01 am ***
*** 03/15/05 10:04 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 03/15/05 10:04 pm ***
*** 03/16/05 11:56 pm *** (CHG: DevPri->2)
*** 03/16/05 11:56 pm *** (CHG: Confirmed Flag->Y)
*** 03/16/05 11:56 pm ***
*** 04/06/05 01:10 am *** (CHG: Sta->32)
*** 04/06/05 01:10 am ***
*** 02/06/07 09:07 am *** (CHG: Sta->92)
扫一扫
2330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
