oracle10g下的ORA-39095

ORACLE:10.2.0.4    

今天在expdp时碰到了ORA-39095错误，网上也有几篇处理该错误的文章，在此总结一下我遇到的问题的解决办法。

 

-bash-3.00$ expdp test /xxx@orcl directory=temp_dump_dir dumpfile=test-static-table0504.dmp logfile=test-static-table0504.log  tables=(xxxxxx) exclude=statistics parallel=8  version=10.2.0.4.0 &

[1] 24068

-bash-3.00$ 

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 04 May, 2011 14:18:52

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "TEST"."SYS_EXPORT_TABLE_01":   test/********@orcl directory=temp_dump_dir dumpfile=test-static-table0504.dmp logfile=test-static-table0504.log tables=(xxxxxxx) exclude=statistics parallel=8 version=10.2.0.4.0 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 891.6 MB

. . exported "TEST"."MOD_GRID_CELL"                      9.453 MB  421526 rows

. . exported "TEST"."MOD_GRID_CELL_TMP"                  8.390 MB  425081 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "TEST"."NE_CELL_G"                          3.947 MB   28265 rows

. . exported "TEST"."CLT_LOG_INSERT":"CLT_LOG_INSER_2011041400"  9.415 MB  104800 rows

. . exported "TEST"."PARA_ADJ_G"                         8.438 MB   34924 rows

. . exported "TEST"."MOD_RMP_LOG"                        7.418 MB   89418 rows

. . exported "TEST"."TMP_TABLE_PARTITION_INFO"           4.643 MB   20439 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "TEST"."NE_CELL_G_BAK_0117"                 5.031 MB   34261 rows

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

Job "TEST"."SYS_EXPORT_TABLE_01" stopped due to fatal error at 14:21:04

 

以上就是错误发生时的情况，由于是生产环境，所以有些内容用xxxx代替了。

该错误的成因就是因为并行 parallel=8 惹的祸，但该问题并不一定每次都出现，而且也不会根据导出数据的大小来决定会不会报错，也就是说该错误的发生有随机性。

根据oracle官网的说法：

Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.

You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth.

可以得知，为防止该问题的发生，可以有3种解决办法：

1、将parallel调小

2、在dumpfile中使用变量 %u(大小写均可)

3、交互模式下，可以用add_file增加文件个数，然后restart该job

当然，解决该问题的首选办法还是选用第二种，即使用变量 %u ，以下是使用举例：

 

由于没有找到10.2.0.4的环境，所以在 11.2.0.1 下做的测试：

[[oracle@dl380 ~]$ expdp scott/tiger@test_90 dumpfile=tt%U.dmp logfile=tt.log directory=dp_dir parallel=20 exclude=statistics

Export: Release 11.2.0.1.0 - Production on Wed May 4 16:39:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/********@test_90 dumpfile=tt%U.dmp logfile=tt%U.log directory=dp_dir parallel=20 exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /app/expdp_dir/tt01.dmp
  /app/expdp_dir/tt02.dmp
  /app/expdp_dir/tt03.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:40:37

 

 

[oracle@dl380 expdp_dir]$ impdp scott/tiger@test_90 dumpfile=tt%U.dmp logfile=tt-2.log directory=dp_dir parallel=20 exclude=statistics

Import: Release 11.2.0.1.0 - Production on Wed May 4 16:48:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39168: Object path STATISTICS was not found.

[oracle@dl380 expdp_dir]$ impdp scott/tiger@test_90 dumpfile=tt%U.dmp logfile=tt-2.log directory=dp_dir parallel=20

Import: Release 11.2.0.1.0 - Production on Wed May 4 16:48:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@test_90 dumpfile=tt%U.dmp logfile=tt-2.log directory=dp_dir parallel=20
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . imported "SCOTT"."EMP"                               8.570 KB      14 rows
. . imported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . imported "SCOTT"."BONUS"                                 0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 16:48:12

 

注意：%u 的范围从 01至99 ，如果连续使用多个 %u，会同步变化，如： %uaaaa%u 当变化的时候是：

01aaaa01

02aaaa02

.....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10037372/viewspace-694436/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-694436/