Exp命令估计是一个oracleDBA 职业生涯中用得最多的命令之一。
使用当中，常常出现一个比较“经典”的问题：
Exp一个表的时候，系统提示exp-00003 错误！
$ oerr exp 00003
00003, 00000, "no storage definition found for segment(%lu, %lu)"
// *Cause: Export could not find the storage definitions for a cluster,
// index, or table.
// *Action: Record the accompanying messages and report this as an Export
// internal error to customer support.
造成以上问题原因比较多，但最常见的有以下两种：
1、 在出问题的表上，有一些对象的owner不是当前用户。（估计是两个用户权限差异引起的）
如一个索引，是system用户创建的。可以用类似下面的SQL来检查：
select a.owner,a.index_name from dba_indexes a where a.table_name=‘xxx’;
如果的确有上述情况，可以先drop掉再exp。
2、 用9205以前的版本导出其后版本的表，且此表中有带LOB字段时，也会出现exp-00003错误。
解决方法是：
更改数据库服务器端的oracle系统视图EXU9TNE的定义，
该视图定义为: $cat $ORACLE_HOME/rdbms/admin/catexp.sql
CREATE OR REPLACE VIEW EXU9TNE
(tsno, fileno, blockno, length)
AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
|
以sys用户登录数据库服务器重新建立该视图：
CREATE OR REPLACE VIEW EXU9TNE
(tsno, fileno, blockno, length)
AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL select * from sys.exu9tneb
|
PS：不要随意改动Oracle系统视图，执行完后，应立刻恢复。切记！！
*********************************************************************
附：metalink文档
Subject: Getting EXP-00003 Errors While Exporting a Transport Set to Create the .dmp File
Doc ID: Note:316215.1 Type: PROBLEM
Last Revision Date: 11-JUL-2005 Status: MODERATED
Applies to:
Portal - Version: 9.0.2 to 9.0.4
This problem can occur on any platform.
Symptoms
You are using Portal Export/Import utilities under the following scenario:
Your Portal is installed in a custom database version 9.2.0.5 or higher.
You are running the export script from your transport set from the MIDTIER_HOME.
The export log file generated by the export from the command line shows the following error:
EXP-00003: no storage definition found for segment(%lu, %lu)
Note: The export log file is created in the same directory where you run the export script.
Cause
The error is caused by the following database bug:
Bug 3784697 EXPORT OF LOB FROM 9205 WITH PRE-9205 EXP GIVES EXP-3 (NO STORAGE DEF FOUND)
Solution
Workaround 1
Run the export script from the DATABASE_HOME.
Workaround 2
Apply the Workaround from the following note to run the export script from the MIDTIER_HOME:
Note 274076.1 EXP-00003 When Exporting From 9.2.0.5.0 or 10.1.0.2.0 with a Pre-9.2.0.5.0 Export Utility
References
Bug 3784697 - Export Of Lob From 9205 With Pre-9205 Exp Gives Exp-3 (No Storage Def Found)
Note 274076.1 - EXP-00003 When Exporting From 9.2.0.5.0 or any Higher Release with a Pre-9.2.0.5.0 Export Client
Errors
EXP-3 "no storage definition found for segment(%lu, %lu)"v