Created
January 4, 2019 01:58
-
-
Save wongsyrone/50ff4224b7b3827d0e3dbf7d9be37d03 to your computer and use it in GitHub Desktop.
Oracle EXP-00003: no storage definition found for segment(0, 0)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Oracle11gR2中空表是不会分配segment的。当含有空表,使用exp导出整个用户的时候报EXP-00003: no storage definition found for segment(0, 0)错误。 | |
解决办法: | |
方法一:如果有些表数据量较大,在分析数据量上花费的时间较多。总体来说推荐这种方法 | |
1、重新分析表的数据量,因为num_rows行数并不是准确的行数。 | |
SELECT 'analyze table ' || TABLE_NAME || ' compute statistics;' FROM USER_TABLES; | |
2、生成以下动态SQL,之后执行查询生成结果。 | |
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; | |
方法二:为所有表重新分配segement,执行以下查出的结果 | |
select 'alter table '||table_name||' allocate extent;' from user_tables; | |
补充说明: | |
通过上面办法解决后,在导出的过程中,日志仍会有部分表报EXP-00003: no storage definition found for segment(0, 0)。虽然报了这个错,但是日志中记录了导出行数,说明已经导出成功。可忽略这种报错,并且能够还原成功。这个应该是Oracle的一个BUG。 | |
--------------------- | |
作者:zha_sir | |
来源:CSDN | |
原文:https://blog.csdn.net/zha_penfee/article/details/50075749 | |
版权声明:本文为博主原创文章,转载请附上博文链接! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment