精华内容
下载资源
问答
  • 表字段如果有空格列,expdp导出正常,但impdp导入失败。 测试过程如下:11g-19c均impdp失败 [oracle@bddb dmp]$ sqlplus his_bas/abcd1234 SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 29 20:02:12 2021...

    表字段如果有空格列,expdp导出正常,但impdp导入失败。

    经过多次测试验证:源端的空格字段重命名为字符,比如t之类,alter table test rename column "   " to t;再导出dmp后,才可在目标端段导入(即使目标端的字段名称为空格,也可导入)。总之源端导出的表字段名称不能为空格,而目标端可任意。

     测试过程如下:11g-19c均impdp失败

    [oracle@bddb dmp]$ sqlplus his_bas/abcd1234

    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 29 20:02:12 2021

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> CREATE TABLE "TEST" 
          (       "   " VARCHAR2(255), --此处为空格列
           "YPID" VARCHAR2(255));

    Table created.


    SQL> insert into test values (null,'1');

    1 row created.

    SQL> insert into test values (null,'2');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from test;


    --------------------------------------------------------------------------------
    YPID
    --------------------------------------------------------------------------------

    1


    2


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb dmp]$ expdp his_bas/abcd1234 directory=dmp logfile=1.log dumpfile=test.dmp tables=test

    Export: Release 11.2.0.4.0 - Production on Tue Jun 29 20:04:55 2021

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "HIS_BAS"."SYS_EXPORT_TABLE_01":  his_bas/******** directory=dmp logfile=1.log dumpfile=test.dmp tables=test 
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    . . exported "HIS_BAS"."TEST"                            5.414 KB       2 rows
    Master table "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for HIS_BAS.SYS_EXPORT_TABLE_01 is:
      /oracle/dmp/test.dmp
    Job "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 29 20:05:01 2021 elapsed 0 00:00:05

    [oracle@bddb dmp]$ sqlplus his_bas/abcd1234

    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 29 20:05:17 2021

    Copyright (c) 1982, 2013, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> drop table test;

    Table dropped.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb dmp]$ impdp his_bas/abcd1234 directory=dmp logfile=1.log dumpfile=test.dmp tables=test

    Import: Release 11.2.0.4.0 - Production on Tue Jun 29 20:05:31 2021

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_01":  his_bas/******** directory=dmp logfile=1.log dumpfile=test.dmp tables=test 
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "HIS_BAS"."SYS_IMPORT_TABLE_01" stopped due to fatal error at Tue Jun 29 20:07:02 2021 elapsed 0 00:01:30

    alert日志也有报错:


    [oracle@bddb trace]$ more alert_zydbtest.log 
    Tue Jun 29 20:04:49 2021
    DM00 started with pid=28, OS id=11580, job HIS_BAS.SYS_EXPORT_TABLE_01
    Tue Jun 29 20:04:56 2021
    DM00 started with pid=28, OS id=11585, job HIS_BAS.SYS_EXPORT_TABLE_01
    Tue Jun 29 20:04:56 2021
    DW00 started with pid=29, OS id=11587, wid=1, job HIS_BAS.SYS_EXPORT_TABLE_01
    Tue Jun 29 20:05:32 2021
    DM00 started with pid=31, OS id=11605, job HIS_BAS.SYS_IMPORT_TABLE_01
    Tue Jun 29 20:05:32 2021
    DW00 started with pid=32, OS id=11607, wid=1, job HIS_BAS.SYS_IMPORT_TABLE_01
    Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x1BDFF63, kpodpals()+6775] [flags: 0x0, count: 1]
    Errors in file /oracle/diag/rdbms/zydbtest/zydbtest/trace/zydbtest_dw00_11607.trc
     (incident=8658):
    ORA-07445: exception encountered: core dump [kpodpals()+6775] [SIGSEGV] [ADDR:0x0] [PC:0x1BDFF63] [Address not mapped to object] []
    Incident details in: /oracle/diag/rdbms/zydbtest/zydbtest/incident/incdir_8658/zydbtest_dw00_11607_i8658.trc
    Use ADRCI or Support Workbench to package the incident.
    See Note 411.1 at My Oracle Support for error and packaging details.
    Tue Jun 29 20:05:36 2021
    Dumping diagnostic data in directory=[cdmp_20210629200536], requested by (instance=1, osid=11607 (DW00)), summary=[incident=8658].
    Tue Jun 29 20:05:38 2021
    Sweep [inc][8658]: completed
    Sweep [inc2][8658]: completed


    可通过实例级别生成trace分析(测试环境可这么做,因为没有其它会话活动,trace量少):
    alter system set events '10046 trace name context forever,level 12';
    执行impdp操作后,关闭trace
    alter system set events '10046 trace name context off';
    检查trace文件发现Unhandled datatype (121) found in kxsbndinf



    其它相关参考:

    如何对 Oracle 数据泵(expdp/impdp) 进行 debug(此次问题没发现有价值的信息,不如10046)。  加参数: TRACE=1ff0300

    https://blog.csdn.net/tianlesoftware/article/details/9003303

    更多测试记录:

    *****[2021-06-30 09:03:09]*****
    Last login: Tue Jun 29 18:59:51 2021 from 10.168.20.66
    [root@bddb ~]# su - oracle
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:04:01 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from test;
    
    no rows selected
    
    SQL> set long 9999999;
    SQL> set serveroutput on;
    set pagesize 0;
    SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST') FROM DUAL;SQL> SQL> 
    
      CREATE TABLE "HIS_BAS"."TEST"
       (    "   " VARCHAR2(255),
            "YPID" VARCHAR2(255)
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_HIS_DATA"
    
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:07:49 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> alter table test rename column "   " to tmp;
    
    Table altered.
    
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TMP                                                VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:11:07 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_02":  his_bas/******** directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "HIS_BAS"."SYS_IMPORT_TABLE_02" stopped due to fatal error at Wed Jun 30 09:11:37 2021 elapsed 0 00:00:29
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:11:54 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TMP                                                VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> alter table test drop column tmp;
    
    Table altered.
    
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
    
    SQL> set serveroutput on;
    SQL> set long 10000;
    SQL> set pagesize 0;
    SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST') FROM DUAL;
    
      CREATE TABLE "HIS_BAS"."TEST"
       (    "YPID" VARCHAR2(255)
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_HIS_DATA"
    
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:13:19 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_03" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_03":  his_bas/******** directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "HIS_BAS"."SYS_IMPORT_TABLE_03" stopped due to fatal error at Wed Jun 30 09:13:49 2021 elapsed 0 00:00:29
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:14:13 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> insert into test values(3);
    
    1 row created.
    
    SQL> insert into test values(4);
    
    1 row created.
    
    SQL> insert into test values(5);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from test;
    
    YPID
    --------------------------------------------------------------------------------
    3
    4
    5
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ expdp his_bas/abcd1234 directory=dmp logfile=2.log dumpfile=test2.dmp tables=test
    
    Export: Release 11.2.0.4.0 - Production on Wed Jun 30 09:15:15 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "HIS_BAS"."SYS_EXPORT_TABLE_01":  his_bas/******** directory=dmp logfile=2.log dumpfile=test2.dmp tables=test 
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported "HIS_BAS"."TEST"                            5.023 KB       3 rows
    Master table "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for HIS_BAS.SYS_EXPORT_TABLE_01 is:
      /oracle/dmp/test2.dmp
    Job "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 30 09:15:19 2021 elapsed 0 00:00:03
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:15:25 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> drop table test;
    
    Table dropped.
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2.log dumpfile=test2.dmp tables=test
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:15:38 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_04":  his_bas/******** directory=dmp logfile=2.log dumpfile=test2.dmp tables=test 
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "HIS_BAS"."TEST"                            5.023 KB       3 rows
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Job "HIS_BAS"."SYS_IMPORT_TABLE_04" successfully completed at Wed Jun 30 09:15:39 2021 elapsed 0 00:00:01
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:15:56 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from test;
    
    YPID
    --------------------------------------------------------------------------------
    3
    4
    5
    
    SQL> alter table test add column "   " varchar2(255);
    alter table test add column "   " varchar2(255)
                         *
    ERROR at line 1:
    ORA-00904: : invalid identifier
    
    
    SQL> alter table test add "   " varchar2(255);
    
    Table altered.
    
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
                                                        VARCHAR2(255)
    
    SQL> set serveroutput on;
    SQL> set long 10000;
    SQL> set pagesize 0;
    SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST') FROM DUAL;
    
      CREATE TABLE "HIS_BAS"."TEST"
       (    "YPID" VARCHAR2(255),
            "   " VARCHAR2(255)
       ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "TBS_HIS_DATA"
    
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:18:27 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_04":  his_bas/******** directory=dmp logfile=1.log dumpfile=test.dmp tables=test content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "HIS_BAS"."SYS_IMPORT_TABLE_04" stopped due to fatal error at Wed Jun 30 09:18:58 2021 elapsed 0 00:00:31
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2.log dumpfile=test2.dmp tables=test content=data_only 
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:19:13 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_05" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_05":  his_bas/******** directory=dmp logfile=2.log dumpfile=test2.dmp tables=test content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "HIS_BAS"."TEST"                            5.023 KB       3 rows
    Job "HIS_BAS"."SYS_IMPORT_TABLE_05" successfully completed at Wed Jun 30 09:19:14 2021 elapsed 0 00:00:00
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:19:17 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from test;
    
    YPID
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    3
    
    
    4
    
    
    5
    
    
    
    YPID
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    3
    
    
    4
    
    
    5
    
    
    
    6 rows selected.
    
    SQL> CREATE TABLE "HIS_BAS"."TEST1"
      2     (    "   " VARCHAR2(255),
      3          "YPID" VARCHAR2(255)
      4     ) SEGMENT CREATION IMMEDIATE
      5    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
      6   NOCOMPRESS LOGGING
      7    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      8    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      9    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
     10    TABLESPACE "TBS_HIS_DATA";
    
    Table created.
    
    SQL> insert into test1 values(null,'1');
    
    1 row created.
    
    SQL> insert into test1 values(null,'2');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from test1;
    
    
    --------------------------------------------------------------------------------
    YPID
    --------------------------------------------------------------------------------
    
    1
    
    
    2
    
    
    SQL> create table t2 (YPID) organization external
      2  (type oracle_datapump default directory dmp
      3  location ('t2.dp'))
      4  as select YPID from test1;
    
    Table created.
    
    SQL> select * from t2;
    
    YPID
    --------------------------------------------------------------------------------
    1
    2
    
    SQL> create table test2 as select * from test1 where 1>1;
    
    Table created.
    
    SQL> select * from test2;
    
    no rows selected
    
    SQL> desc test2;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
                                                        VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> insert into t2 (YPID) organization external
      2  (type oracle_datapump default directory dmp
      3  location ('t2.dp'));
    insert into t2 (YPID) organization external
                          *
    ERROR at line 1:
    ORA-00926: missing VALUES keyword
    
    
    SQL> create table t3 (YPID) organization external
      2  (type oracle_datapump default directory dmp
      3  location ('t2.dp'));
    create table t3 (YPID) organization external
                     *
    ERROR at line 1:
    ORA-02263: need to specify the datatype for this column
    
    
    SQL> create table t3 (YPID varchar2(255)) organization external
      2  (type oracle_datapump default directory dmp
      3  location ('t2.dp'));
    
    Table created.
    
    SQL> desc t3; 
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
    
    SQL> select * from t3;
    
    YPID
    --------------------------------------------------------------------------------
    1
    2
    
    SQL> 
    SQL> 
    SQL> set long 9999999;
    SQL> set serveroutput on;
    SQL> set pagesize 0;
    SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T3') FROM DUAL;
    
      CREATE TABLE "HIS_BAS"."T3"
       (    "YPID" VARCHAR2(255)
       )
       ORGANIZATION EXTERNAL
        ( TYPE ORACLE_DATAPUMP
          DEFAULT DIRECTORY "DMP"
    
          LOCATION
           ( 't2.dp'
           )
        )
    
    
    SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T2') FROM DUAL;
    
      CREATE TABLE "HIS_BAS"."T2"
       (    "YPID" VARCHAR2(255)
       )
       ORGANIZATION EXTERNAL
        ( TYPE ORACLE_DATAPUMP
          DEFAULT DIRECTORY "DMP"
    
          LOCATION
           ( 't2.dp'
           )
        )
    
    
    SQL> desc test2;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
                                                        VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> select * from test2;
    
    no rows selected
    
    SQL> insert into test2 values(null ,'6');
    
    1 row created.
    
    SQL> INSERT INTO test2 values(null,'7');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ expdp his_bas/abcd1234 directory=dmp logfile=2k.log dumpfile=test2-k.dmp tables=test2
    
    Export: Release 11.2.0.4.0 - Production on Wed Jun 30 09:40:23 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "HIS_BAS"."SYS_EXPORT_TABLE_01":  his_bas/******** directory=dmp logfile=2k.log dumpfile=test2-k.dmp tables=test2 
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    . . exported "HIS_BAS"."TEST2"                           5.414 KB       2 rows
    Master table "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for HIS_BAS.SYS_EXPORT_TABLE_01 is:
      /oracle/dmp/test2-k.dmp
    Job "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 30 09:40:25 2021 elapsed 0 00:00:02
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:40:29 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> desc test2;                    
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
                                                        VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> alter table test2 rename column "  " to t;
    alter table test2 rename column "  " to t
                                    *
    ERROR at line 1:
    ORA-00904: "  ": invalid identifier
    
    
    SQL> alter table test rename column "   " to t;
    
    Table altered.
    
    SQL> desc test2;    
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
                                                        VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> alter table test2 rename column "   " to t;
    
    Table altered.
    
    SQL> desc test2;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     T                                                  VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ expdp his_bas/abcd1234 directory=dmp logfile=2ok.log dumpfile=test2-ok.dmp tables=test2
    
    Export: Release 11.2.0.4.0 - Production on Wed Jun 30 09:42:12 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "HIS_BAS"."SYS_EXPORT_TABLE_01":  his_bas/******** directory=dmp logfile=2ok.log dumpfile=test2-ok.dmp tables=test2 
    Estimate in progress using BLOCKS method...
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    . . exported "HIS_BAS"."TEST2"                           5.414 KB       2 rows
    Master table "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for HIS_BAS.SYS_EXPORT_TABLE_01 is:
      /oracle/dmp/test2-ok.dmp
    Job "HIS_BAS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 30 09:42:14 2021 elapsed 0 00:00:01
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2k.log dumpfile=test2-k.dmp tables=test2 content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:42:41 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_05" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_05":  his_bas/******** directory=dmp logfile=2k.log dumpfile=test2-k.dmp tables=test2 content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Job "HIS_BAS"."SYS_IMPORT_TABLE_05" stopped due to fatal error at Wed Jun 30 09:43:12 2021 elapsed 0 00:00:31
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp tables=test2 content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:43:30 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_TABLE_06" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_TABLE_06":  his_bas/******** directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp tables=test2 content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "HIS_BAS"."TEST2"                           5.414 KB       2 rows
    Job "HIS_BAS"."SYS_IMPORT_TABLE_06" successfully completed at Wed Jun 30 09:43:31 2021 elapsed 0 00:00:01
    
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:43:39 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from test2;  
    
    T
    --------------------------------------------------------------------------------
    YPID
    --------------------------------------------------------------------------------
    
    6
    
    
    7
    
    
    6
    
    
    T
    --------------------------------------------------------------------------------
    YPID
    --------------------------------------------------------------------------------
    
    7
    
    
    SQL> desc test2;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     T                                                  VARCHAR2(255)
     YPID                                               VARCHAR2(255)
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:46:17 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> dest test;
    SP2-0042: unknown command "dest test" - rest of line ignored.
    SQL> dest test
    SP2-0042: unknown command "dest test" - rest of line ignored.
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
     T                                                  VARCHAR2(255)
    
    SQL> alter table test rename column t to "   ";
    
    Table altered.
    
    SQL> desc test;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
                                                        VARCHAR2(255)
    
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp tables=test content=data_only
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:47:20 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORA-39002: invalid operation
    ORA-39166: Object HIS_BAS.TEST was not found.
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp REMAP_TABLE=test2:test content=data_only[oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp REMAP_TABLE=test2:test content=data_only 
    
    Import: Release 11.2.0.4.0 - Production on Wed Jun 30 09:48:06 2021
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "HIS_BAS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "HIS_BAS"."SYS_IMPORT_FULL_01":  his_bas/******** directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp REMAP_TABLE=test2:test content=data_only 
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    . . imported "HIS_BAS"."TEST"                            5.414 KB       2 rows
    Job "HIS_BAS"."SYS_IMPORT_FULL_01" successfully completed at Wed Jun 30 09:48:07 2021 elapsed 0 00:00:01
    
    [oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp REMAP_TABLE=test2:test content=data_only[oracle@bddb ~]$ impdp his_bas/abcd1234 directory=dmp logfile=2ok-imp.log dumpfile=test2-ok.dmp tables=test content=data_only
    [oracle@bddb ~]$ sqlplus his_bas/abcd1234
    
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 30 09:48:12 2021
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select * from test;
    
    YPID
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    3
    
    
    4
    
    
    5
    
    
    
    YPID
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    3
    
    
    4
    
    
    5
    
    
    
    YPID
    --------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------
    6
    
    
    7
    
    
    
    8 rows selected.
    
    SQL> desc test
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     YPID                                               VARCHAR2(255)
                                                        VARCHAR2(255)
    
    

    展开全文
  • oracle12 impdp导入失败

    千次阅读 2019-07-02 17:20:21
    使用impdp出现需考虑的问题 UDI-00010: multiple job modes requested, full and schemas. 1.考虑创建不同的表空间,不同的用户之后导入 2.创建的用户和导入文件的用户名一样(以免出现ORA-01435: user does not ...

    使用impdp出现需考虑的问题

    UDI-00010: multiple job modes requested, full and schemas.

    1.考虑创建不同的表空间,不同的用户之后导入

    2.创建的用户和导入文件的用户名一样(以免出现ORA-01435: user does not exist)

    imp 用户名/密码@数据库实例名 file=/orcl/app/oracle/admin/a/dpdump/ora_20190628.dmp full=y ignore=no

    ORA-39001: invalid argument value

    ORA-39000: bad dump file specification

    ORA-39143: dump file "/orcl/app/oracle/admin

    转载地址:http://www.bubuko.com/infodetail-2286016.html

     

    IMP-00002: failed to open expdat.dmp for read
    Import file: expdat.dmp >

     

    IMP-00003: ORACLE error 1435 encountered

    ORA-01435: user does not exist

    IMP-00000: Import terminated unsuccessfully

     

    展开全文
  • 1、环境 源端:axi 6 oracle 11.2.0.3.10 目标端 linux 6——64bit oracle 11.2.0.3.10 2、导入报错 导入报错,现象如下: Master table "SYS"."...
    1、环境
    源端:axi 6 oracle 11.2.0.3.10
    目标端 linux 6——64bit oracle 11.2.0.3.10
    2、导入报错
    导入报错,现象如下:
    Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
    Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" tables= tables , tables sunny.t_STORE dumpfile=nea4.dmp l
    ogfile= directory= table_exists_action=
    Processing object type TABLE_EXPORT/TABLE/TABLE
    ORA-39083: Object type TABLE:"sunny"."t_RETAIL" failed to create with error:
    ORA-02261: such unique or primary key already exists in the table
    Failing sql is:
    CREATE TABLE "sunny"."t_RETAIL" ("ID" NUMBER(10,0) NOT NULL ENABLE, "AD_CLIENT_ID" NUMBER(10,0), "AD_ORG_ID" NUMBER(10
    ,0), "OWNERID" NUMBER(10,0), "MODIFIERID" NUMBER(10,0), "CREATIONDATE" DATE, "MODIFIEDDATE" DATE, "ISACTIVE" CHAR(1 BYTE
    ) DEFAULT 'Y' NOT NULL ENABLE, "DOCNO" VARCHAR2(80 BYTE), "DOCTYPE" CHAR(3 BYTE) DEFAULT 1, "BILLDATE" NUMBER(8
    ORA-39083: Object type TABLE:"sunny"."t_STORE" failed to create with error:
    ORA-02261: such unique or primary key already exists in the table
    3、分析
    原因 :由于有附加日志表引起的bug
    经查证是bug 16595641 ,目标库版本11.2.3.10,打补丁 Patch ID: 16918115 但是还是报上面的错误,导入不进去
    Cannot Import Reference Partition Tables Get ORA-2261 ON DATAPUMP IMPORT (文档 ID 1584356.1)

    During datapump import you get similar errors:
    官方解释说:源端也需要打patch 16918115


    因为源端不能动,最终的解决方案是:
    目标端安装oracle 11.2.0.4 然后打补丁p16595641:

    4、实施
    [oracle@dbtest bug]$ cd 16595641/
    [oracle@dbtest 16595641]$ ls
    etc files postinstall.sql README.txt
    [oracle@dbtest 16595641]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch apply
    Oracle Interim Patch Installer version 11.2.0.3.4
    Copyright (c) 2012, Oracle Corporation. All rights reserved.


    Oracle Home : /u01/app/oracle/product/11.2.0/db_1
    Central Inventory : /u01/app/oraInventory
    from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
    OPatch version : 11.2.0.3.4
    OUI version : 11.2.0.4.0
    Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/16595641_Apr_03_2015_15_14_21/apply2015-04-03_15-14-21PM_1.log

    Applying interim patch '16595641' to OH '/u01/app/oracle/product/11.2.0/db_1'
    Verifying environment and performing prerequisite checks...
    All checks passed.
    Backing up files...

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Verifying the update...
    Patch 16595641 successfully applied
    Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/16595641_Apr_03_2015_15_14_21/apply2015-04-03_15-14-21PM_1.log

    OPatch succeeded.
    [oracle@dbtest 16595641]$ ls
    etc files postinstall.sql README.txt
    [oracle@dbtest 16595641]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 3 15:14:52 2015

    Copyright (c) 1982, 2013, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 5.1577E+10 bytes
    Fixed Size 2265304 bytes
    Variable Size 6710890280 bytes
    Database Buffers 4.4695E+10 bytes
    Redo Buffers 169086976 bytes
    Database mounted.
    Database opened.
    SQL> @ postinstall.sql

    Session altered.


    PL/SQL procedure successfully completed.
                                                                                                                   

    ----------------------------------------------write by Sunny Hu


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

    转载于:http://blog.itpub.net/30109892/viewspace-2051827/

    展开全文
  • 使用impdp导入失败ORA-31634(转)

    千次阅读 2014-10-31 12:04:34
    使用数据泵进行全备,由于user的表空间不足导致备份失败,添加user表空间,清理空间后再次进行备份报如下错误: With the Partitioning, OLAP and Data Mining options ORA-31634: job already exists ORA-31664: ...

    expdp:ORA-31634: job already exists

     

    使用数据泵进行全备,由于user的表空间不足导致备份失败,添加user表空间,清理空间后再次进行备份报如下错误:

    With the Partitioning, OLAP and Data Mining options

    ORA-31634: job already exists

    ORA-31664: unable to construct unique job name when defaulted

     

    解决方法:

    1.在备份时指定一个在dba_datapump_jobs中没有不存在的一个job_name

     

    2.清理dba_datapump_jobs表

    1)查询可以清理的master table并生成SQL

    select 'drop table ' || owner_name || '.' || job_name || ';'
    from dba_datapump_jobs
    where state = 'NOT RUNNING';

    2)清理后再次dba_datapump_jobs确认清理成功

    SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
    FROM dba_datapump_jobs
    ORDER BY 1,2;

    若不成功,按下面的方法再次清理

    sqlplus oak/oak

    exec dbms_datapump.stop_job(dbms_datapump.attach('SYS_EXPORT_TABLE_01','OAK'));


    在导入导出时会自动创建一个user.SYS_EXPORT_FULL_12的表(好像是1~100)..占用user表空间..
    指定job_name不知道能不能更改他创建的表
    自动创建的job名可在导入导出时的输出或者log中看到


    参考:
    How to cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

    Original article

    In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y,the master table remain in the database.

    Though this topic is related to cleanup orphaned datapump jobs. But it is good to know several things before doing cleanup jobs.

    1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started,a new entry will be created, which has no relation to the old Data Pump jobs.

    2) For a new data pump job without any job name it is used a system generated name.
    From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a unique new system generated jobname.

    3) Data pump jobs are different from DBMS_JOBS and they are maintained differently.
    Jobs created with DBMS_JOBS use there own processes. Data Pump jobs use a master process and worker process(es).

    4) If you drop the master table while doing the data pump export or data pump import operation then the scenario is discussed below.
    In case of export if you drop data pump export operation then export process will abort.
    In case of import if you drop data pump import operation then import process will abort while it leads an incomplete import.
    If the data pump job is completed and master table exist (a common if you do export operation with KEEP_MASTER=y) then it is safe to drop the master table.
    Step by step cleanup orphaned datapump jobs is discussed below.
    Step 01: Check the orphaned datapump jobs.
    sqlplus / as sysdba
    SET lines 140
    COL owner_name FORMAT a10;
    COL job_name FORMAT a20
    COL state FORMAT a12
    COL operation LIKE owner_name
    COL job_mode LIKE owner_name
    SELECT owner_name, job_name, operation, job_mode,
    state, attached_sessions
    FROM dba_datapump_jobs;

    OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
    ---------- -------------------- ---------- ---------- ------------ -----------------
    ARJU SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
    ARJU SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0

    Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. So from the output we can say both are orphaned jobs.

    Step 03: Drop the master table.
    DROP TABLE ARJU.SYS_EXPORT_SCHEMA_01;
    DROP TABLE ARJU.SYS_EXPORT_SCHEMA_02;

    Step 04: Check for existing data pump jobs by query issued in step 01. If objects are in recyclebin then purge the objects from the recyclebin.

    SQL> SELECT owner_name, job_name, operation, job_mode,
    state, attached_sessions
    FROM dba_datapump_jobs;

    OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
    ---------- -------------------- ---------- ---------- ------------ -----------------
    ARJU       BIN$xMNQdACzQ6yl22kj EXPORT SCHEMA NOT RUNNING 0 9U0B8A==$0
    ARJU       BIN$BmUy4r5MSX+ojxFk EXPORT SCHEMA NOT RUNNING 0 sw8ocg==$0

    SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_01;

    Table purged.

    SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_02;

    Table purged.

    Check if there is any orphaned jobs again.
    SQL> SELECT owner_name, job_name, operation, job_mode,
    state, attached_sessions
    FROM dba_datapump_jobs;

    no rows selected

    Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table.
    If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

    SET serveroutput on
    SET lines 100
    DECLARE
    job1 NUMBER;
    BEGIN
    job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','ARJU');
    DBMS_DATAPUMP.STOP_JOB (job1);
    END;
    /

    DECLARE
    job2 NUMBER;
    BEGIN
    job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','ARJU');
    DBMS_DATAPUMP.STOP_JOB (job2);
    END;
    /

    展开全文
  • 今天对生产机数据库使用impdf导入的时候,出现如下错误,错误指令为: “ORA-39002,ORA-39070,ORA-29283,ORA-06512,ORA-29283”。 特此记录解决方案如下: 第一步,排查目录是否存在,权限是否oracle:oinstall。...
  • impdp导入文件失败问题解决(ORA-39001/ORA-39000/ORA-39143)
  • impdp导入

    千次阅读 2017-12-21 15:46:54
    《Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(上)》 《Oracle简单常用的数据泵导出导入(expdp/impdp)命令举例(下)》 目的:指导项目侧自行进行简单的数据泵迁移工作。 本文实验环境:Oracle 11.2.0.4...
  • 在从32位的10.2.0.2版本的生产数据库导入到64位11.2.0.1的版本中,在IMPDP导入新库的时候碰到如下的错误代码: Applies to: Oracle Server - Enterprise Edition - Version 9.2.0.8 to 11.1.0.7 [Release 9.2 to ...
  • 前一些测试里面,通过expdp导出了各种数据库表、模式、表空间以及全库。 在这里,我们就通过之前导出来的逻辑备份文件,使用impdp工具把导出的文件 逆向导入数据库里面。...----使用数据泵impdp导入数据: --...
  • 数据库版本:11.2.0.4.0 操作系统:windows 2008 在做用impdp做数据恢复的时候(相同版本的数据库),导入数据失败,提示ORA39242:表属性原因,导致导入失败 ...
  • 现象: impdp 导入后报大量的ORA-39112 错误 ...impdp导入时,会按照dmp文件中有关的存储的参数信息完成数据的导入。如果遇到上边说的情况,就会报 ora-39112 错误。 这个时候我们需要按照被导入用..
  • 而使用impdp完成数据库导入时,若表已经存在,有四种的处理方式: 1) skip:默认操作 2) replace:先drop表,然后创建表,最后插入数据 3) append:在原来数据的基础上增加数据 4) truncate:先...
  • ORACLE impdp 导入数据

    千次阅读 2013-10-20 21:23:19
    而使用impdp完成数据库导入时,若表已经存在,有四种的处理方式: 1) skip:默认操作 2) replace:先drop表,然后创建表,最后插入数据 3) append:在原来数据的基础上增加数据 4) truncate:先truncate,...
  • 一、文档说明 1.0 需求:需要将生产环境PICC用户导出,在测试环境中docker 测试数据库导入,只需要表结构; 2.0 思路:根据开发人员提供的... 工具:使用expdp进行导出,impdp进行导入,scp进行传输 ...
  • impdp 导入数据库报错 ORA-00001

    千次阅读 2018-09-19 12:42:53
    impdp之后,log日志有如下报错: ORA-39083: Object type JOB failed to create with error: ORA-00001: unique constraint (SYS.I_JOB_... --导入时报创建job失败 原因是因为库里当前有个29281的job 解决方法: 1...
  • Linux系统下使用impdp导入dmp文件

    千次阅读 2020-03-04 15:59:21
    以下是记录一次导入dmp文件的过程。在Linux环境下操作,dmp文件是从另一个...(之所以把这个临时表空间命名为E8ITSM,是因为导出这dmp文件的时候,用的表空间就是E8ITSM,我也不知道为什么,命名成其他会导入失败。...
  • 解决Oracle impdp导入dmp文件时,各种报错提示 报错提示:各种表空间不存在 1、ORA-39083: 对象类型 TABLE:“SOP2”.“xxx” 创建失败, 出现错误: ORA-00959: 表空间 ‘SOP2_DATA’ 不存在 2、ORA-39083: 对象类型 ...
  • Oracle创建、impdp导入及删除表空间步骤 创建表空间 create tablespace 表空间名 datafile ‘dbf文件存放路径’ size 表空间大小 autoextend on next 扩展大小 maxsize unlimited; 例如:create tablespace WEB_HIS ...
  • 今天帮同事用impdp导入数据库是发现错误,到不进去。 主要错误信息如下: With the Partitioning, OLAP and Data Mining options ORA-39001: invalid argument value ORA-39000: bad dump file specification ...
  • Oracle利用impdp 导入数据库的归纳

    万次阅读 2013-01-09 13:51:12
    我们知道,当我们要导入...1, 利用impdp导入数据库分类  a, 完整导入:就是加入full=y这个关键词,不能和schemas, tables,等关键词重用  impdp \"/ as sysdba\" full=y directory=expdump dumpfile=test1.dmp logfi
  • 客户反馈impdp到新的环境中报错 检查字符均为AL32UTF8 报错如下 1 处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE 2 3 处理对象类型 SCHEMA_EXPORT/VIEW/VIEW 4 5 ORA-39346: 对象 SCHEMA_...
  • 为什么我用Impdp导入之后显示的是成功的,但是select之后却只有字段名没有数据呢?![图片说明](https://img-ask.csdn.net/upload/201708/15/1502767747_317451.jpg)![图片说明]...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,433
精华内容 573
关键字:

impdp导入失败