expdp 导出schema ora-39095

来源：互联网发布：基金从业资格报名知乎编辑：程序博客网时间：2024/05/18 02:13

用expdp 导出schema 报 ORA-39095

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

刚开始指定了 filesize

导出时，dumpfile 预估有26G，Total estimation using BLOCKS method: 26.02 GB

expdp system/system schemas=test directory=DATA_PUMP_DIR parallel=4 dumpfile=test0730.dmp logfile=test.log filesize=20480m

如果指定了filesize，如20G，dumpfile有26G，则会报ora-39095

然后去掉filesize参数，重新执行 expdp system/system schemas=test directory=DATA_PUMP_DIR parallel=4 dumpfile=test0730.dmp logfile=test.log

还是报ORA-39095 ，

原因是用parallel ，dumpfile需要制定多个文件名：dumpfile=test%U.dmp

expdp system/system schemas=test directory=DATA_PUMP_DIR parallel=4 dumpfile=test0730%U.dmp logfile=test.log

(1)在导出报错时，要停止datapump：

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
------------------------------ ------------------------------

OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYSTEM                         SYS_EXPORT_SCHEMA_01_BAK
EXPORT
SCHEMA
NOT RUNNING                             0                 0                 0

expdp system/system attach=SYS_EXPORT_SCHEMA_01

Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

(2)

如果删除了dump出来的文件，expdp system/system attach=SYS_EXPORT_SCHEMA_01 会提示文件不存在，出错

则可以直接 drop table system.’attach同名表‘

3. test用户有45G，dump出来的文件22G，用时3分钟，期间数据库服务器有短暂的hang住

0 0