oracle数据库导入导出时,ORA-39083;ORA-00439错误解决思路
ORA-39083: Object type TABLE:"TEST"."TEST_SS_NAME" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation
错误解析与解决方法;
错误:当表为空时,不分配segment,以便节省空间,导致表在新库中是无法创建的。
解决方法:
查看源库中deferred_segment_creation的状态;
设置deferred_segment_creation状态为false,以便以后可以顺利导出导入空表;
查看当前用户下所有的空表;
对空表进行数据添加删除的操作;
重新导出、导入数据;
1、查看源库、目标库的deferred_segment_creation状态:
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
2、将状态true设置为false;
sql> alter system set deferred_segment_creation=false scope=both;
System altered.
查看设置后的状态
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
3、查看源库中的空表:
select table_name from user_tables where NUM_ROWS = 0;
4、对查出来的空表进行数据编辑,并及时还原为原来的空值;
5、重新导入导出数据即可;
expdp username/password@ORACLESID DIRECTORY=/backup/oracle
DUMPFILE=test00001.dmp
impdp username/password@ORACLESID DIRECTORY=/backup/oracle
DUMPFILE=test00001.dmp remap_schema=username:username
ORA-39083: Object type TABLE:"TEST"."TEST_SS_NAME" failed to create with error:
ORA-00439: feature not enabled: Deferred Segment Creation
错误解析与解决方法;
错误:当表为空时,不分配segment,以便节省空间,导致表在新库中是无法创建的。
解决方法:
查看源库中deferred_segment_creation的状态;
设置deferred_segment_creation状态为false,以便以后可以顺利导出导入空表;
查看当前用户下所有的空表;
对空表进行数据添加删除的操作;
重新导出、导入数据;
1、查看源库、目标库的deferred_segment_creation状态:
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
2、将状态true设置为false;
sql> alter system set deferred_segment_creation=false scope=both;
System altered.
查看设置后的状态
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
3、查看源库中的空表:
select table_name from user_tables where NUM_ROWS = 0;
4、对查出来的空表进行数据编辑,并及时还原为原来的空值;
5、重新导入导出数据即可;
expdp username/password@ORACLESID DIRECTORY=/backup/oracle
DUMPFILE=test00001.dmp
impdp username/password@ORACLESID DIRECTORY=/backup/oracle
DUMPFILE=test00001.dmp remap_schema=username:username