henrybai的专栏

ORACLE,SOLARIS,SQL SERVER,LINUX,MYSQL,Cluster

导入时遇到一个BUG?

具体如下:

$ 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  

 

Show Related ProductsRelated 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)

 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭