表字段如果有空格列，impdp导入失败

最新推荐文章于 2024-02-21 10:33:53 发布
最新推荐文章于 2024-02-21 10:33:53 发布
阅读量1k 收藏 1
点赞数 1
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jycjyc/article/details/118343004
版权

表字段如果有空格列，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)

好记忆不如烂笔头abc
关注
专栏目录
impdp传输表空间错误小记 ORA-39083: Object type INDEX_STATISTICS failed to create with error:
我爱海
06-02 8912
<br />经常用expdp和impdp  但今天在论坛上看到哥们在讨论一个问题所以下先记录下了免得今后碰到：<br /> 狐狸(375394747)  15:28:07<br />impdp遇到错误 <br />狐狸(375394747)  15:28:49<br />报告<br />ORA-39083: Object type INDEX_STATISTICS failed to create with error:<br />ORA-01403: no data found<br />ORA-0140
impdp远程导入oracle数据库,impdp导入远程数据库
weixin_31880383的博客
04-04 1755
使用expdp(非本地)远程导出数据背景：前段时间，需要从异地一个测试数据库中将测试数据(一张表)导入本地库，表数据量大约500万，字段160多个，开始用了exp/imp方式，速度奇慢，不能忍，于是转而使用expdp/impdp方式。expdp/impd介绍：从10g开始，除了传统的exp/imp导入导出工具外，Oracle...文章bisal2015-04-151100浏览量使用 EXPDP 远...
MySql遇到字段中有空格
Cheese_pop的博客
11-19 4272
在使用Mysql进行select数据的过程中，偶尔会遇到列名的字段中间有空格，比如CHCR这张表中的第一列：‘ACC LF’。 由于无论是ACC还是LF都不是该表中的任何一个字段，因此，在select语句执行后，会有报错信息； 在尝试过多种字符（包括中括号、单引号、双引号）都无效的情况下，发现使用反引号（`）,即键盘左上角在‘～’下面的引号，可以有效的含有空格的列进行操作。 ...
oracle查出某个字段带空格的数据
cke63021的博客
10-28 942
问题描述：把一个表从oracle同步到阿里云 rds,居然报错违反唯一键，很是诧异，然后具体查看报错的那条数据，结果如下：果真有两条。 MySQL [bidinfo]> select record_id ...
ORACLE查询字段中含有空格的数据
Soul_resonance的博客
04-10 3713
【代码】ORACLE查询字段中含有空格的数据。
oracle 怎么导入空表,Oracle11g导出空表、少表的解决办法
weixin_42300090的博客
04-03 310
Oracle 11g中有个新特性，当表无数据时，不分配segment，以节省空间。Oracle 11g中有个新特性，当表无数据时，不分配segment，以节省空间。解决方法：1)insert一行，再rollback就产生segment了该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。2)设置deferred_segment_creation参数SQL> show...
impdp导入多个文件的三种方式
最新发布
04-16
在Oracle数据库环境中，数据泵（Data Pump）工具是用于数据导入导出的主要手段，其中`impdp`是数据泵导入工具。当我们需要一次性导入多个数据文件时，有三种主要的方法可以实现。以下将详细介绍这三种方法。 ### ...
oracle impdp导入覆盖,关于impdp导入不能替换procedure以及package问题
weixin_34304274的博客
04-03 2568
连接到:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>SQL> create procedure testproc as...
oracle的impdp和expdp数据泵导入导出全备数据库-详细笔记文档总结
06-30
Oracle 的 IMPDP 和 EXPDP 数据泵导入导出全备数据库详细笔记文档总结 Oracle 的 IMPDP 和 EXPDP 是两个强大的数据泵工具，用于将 Oracle 数据库中的数据导入或导出到其他数据库或文件中。在本文档中，我们将详细...
oracle impdp导入覆盖,impdp导入时报序列、存储过程、视图已经存在，怎么直接覆盖导入...
weixin_34348050的博客
04-03 3772
我现在用impdp导入数据，报如下错误，我现在想让直接替换，而不是忽略，应该怎么办，用table_exists_action=replace 不管用[oracle@node admin]$ impdp system/oracle directory=DPDATA1 network_link=HIS_CQWY_ONLINEDBLINK logfile=import_his_cqwy_online...
ORA-07445异常报错opixguid()+13
lmxx2020的博客
02-21 1533
ORA-07445: 出现异常错误: 核心转储 [opixguid()+13] [SIGSEGV] [ADDR:0x7F67B7F4EBC0] [PC:0x648BB7D] [Address not mapped to object] []ORA-07445: 出现异常错误: 核心转储 [opixguid()+13] [SIGSEGV] [ADDR:0x7F67B7F4EBC0] [PC:0x648BB7D] [Address not mapped to object] []这是一个bug导致。
数据表创建参数介绍（一）
ciqu9915的博客
02-26 144
创建数据表create table是我们对数据库进行的常见操作。我们一般使用create table之后，指定了数据列信息和主键等约束信息，其他就交给Oracle使用默认值了。今天我们一起来看看这些默认值。说明：本片只关注...
oracle11g 导入空表的办法
weixin_30664615的博客
09-04 229
ORACLE 11G中有个新特性，当表无数据时，不分配segment，以节省空间 这样会出现导入导出数据库的时候报错，提示空表没有被还原，缺少表的情况 　　解决方法： 　　 设置deferred_segment_creation 参数 Sql代码 SQL>showparameterdeferred_segment_creation NAME...
oracle 怎么导入空表,Oracle不能导入空表解决方案
weixin_31419249的博客
04-03 284
Oracle不能导入空表的解决方案C:\Users\Administrator>sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on 星期日 8月 17 19:24:29 2014Copyright (c) 1982, 2010, Oracle. All rights reserved.连接到:Oracle Databa...
ORACLE中约束的禁用和启用
热门推荐
ZKLxuankai的专栏
06-17 2万+
查看表TEST_PHONE_TAB的元数据： SQL> select dbms_metadata.get_ddl('TABLE','TEST_PHONE_TAB') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','TEST_PHONE_TAB') --------------------------------------------------
oracle deferred_segment_creation,Oracle数据库的deferred_segment_creation和segment creation immediate...
weixin_39675679的博客
04-12 499
[oracle@rhel63single ~]$ sqlplus scott/aaaaaaSQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 8 02:54:40 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g E...
oracle 主键约束复制,Oracle主键及约束
weixin_34581040的博客
04-11 500
Oracle主键Primary Key包含非空约束及唯一约束。添加主键的语句alter table table_nameadd constraint cons_name primary key(col_name);查看主键是否被创建成功select dbms_metadata.get_ddl(‘OBJECT_TYPE‘,‘NAME‘,‘SCHEMA‘) from dual;OBJECT_TYPE ...
[每日一题] OCP1z0-047 :2013-08-28 DELETE..........................................................160
长期深耕于云计算、大数据、数据库领域！
08-28 3034
[每日一题] OCP1z0-047 :2013-08-28 DELETE
oracle impdp的table_exists_action详解
FIGHT_ANGEL的专栏
10-22 925
1 table_exists_action参数说明 使用imp进行数据导入时，若表已经存在，要先drop掉表，再进行导入。 而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式： 1)  skip：默认操作 2)  replace：先drop表，然后创建表，最后插入数据 3)  append：在原来数据的基础上增加数据 4)  truncate：先t
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值