ORA-39095: dumped file space exhausted: unable to allocate 4096 bytes

Author: skate

Time: 2010-08-31

 

The following error occurs when testing the speed of exp and expdp:

ORA-39095: dumped file space exhausted: unable to allocate 4096 bytes
The job "sys". "sys_export_table_01" was stopped at 13:50:54 due to a fatal error.

 

This experiment uses expdp to export data to multiple export files and improves the export speed in parallel.

 

C:/Documents and Settings/Administrator> expdp 'sys/Aibo @ test as sysdba 'ctor
Y = dpdump dumpfile = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp,
Skate. test_idx) filesize = 150 M & time

 

Export: Release 10.2.0.4.0-production on Tuesday, August, 2010 13:49:35

Copyright (c) 2003,200 7, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options
Start "sys". "sys_export_table_01": 'sys/********* @ test as sysdba 'directory = dpdum
P dumpfile = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp, skate. t
Est_idx) filesize = 150 m
Using the blocks Method for estimation...
Processing object type table_export/table/table_data
Total estimation using the blocks method: 926 MB
Processing object type table_export/table
Processing object type table_export/table/index/Index
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
ORA-39095: dumped file space exhausted: unable to allocate 4096 bytes
The job "sys". "sys_export_table_01" was stopped at 13:50:54 due to a fatal error.

 

Time: 13:51:59. 51

 

Analyze the cause of the error: with the error prompt, we can know that because we have specified the filesize of this job (that is, the size of the dump file), a dump file

For 150 MB, two dump files are specified for a total of 300 MB, and the actual dump file evaluation is 926 MB, so that the dump file is too small to store exported files, so an error is reported.
In fact, the size of the specified filesize is a mechanism for managing disk resources.

 

If you already know the cause of the error, you can solve the problem. You can re-specify The filesize and re-execute the job. You can also add a dump file for the job to continue the job.

 

Generally, you can use job_name to specify the job name at the beginning of the job to facilitate interaction management. If not, you can also query the job by view dba_datapump_jobs.

 

Open an SQL window
C:/Documents and Settings/Administrator> sqlplus "sys/Aibo @ test as sysdba"

SQL * Plus: Release 10.2.0.4.0-production on Tuesday August 31 15:21:42 2010

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options

SQL>

 

SQL> select job_name, State from dba_datapump_jobs;

Job_name state
------------------------------------------------------------
Sys_export_table_01 not running

SQL>

 

Use attach to match an existing job "sys_export_table_01"

C:/Documents and Settings/Administrator> expdp 'sys/Aibo @ test as sysdba 'Attach = s
Ys_export_table_01

Export: Release 10.2.0.4.0-production on Tuesday, August, 2010 14:01:43

Copyright (c) 2003,200 7, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options

Job: sys_export_table_01
Owner: SYS
Operation: Export
Creator permission: True
Guid: b2b05084b70e4a2eb136f84c2249001d
Start Time: Tuesday, August, 2010 14:01:44
Mode: Table
Instance: Test
Maximum degree of Parallelism: 1
Export job parameters:
Parameter value:
Client_command 'sys/******** @ test as sysdba 'directory = dpdump dumpfi
Le = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp, skate. test_idx)
Filesize = 150 m
Status: idling
Bytes processed: 0
Current degree of Parallelism: 1
Job error count: 0
Dump File: D:/backup/Oracle/expdp1.dp
Size: 157,286,400
Written Bytes: 20,480
Dump File: D:/backup/Oracle/expdp2.dp
Size: 157,286,400
Written Bytes: 4,096

WORKER 1 status:
Status: Undefined
Object solution: skate
Object Name: test_idx
Object Type: table_export/table/table_data
Number of completed objects: 1
Total number of objects: 3
Number of completed rows: 1,468,189
Completed byte: 314,548,224
Completion percentage: 38
Worker concurrency: 1

Now the interaction mode is in. Use add_file to add a dump file for the job (this dump file does not specify filesize, and the mode is infinite)

 

Export> add_file = expdp3.dp

Export> add_file = expdp4.dp

Export> add_file = expdp5.dp

Export> add_file = expdp6.dp, expdp7.dp

Export> add_file = expdp8.dp, expdp9.dp

 

Check whether the added dump file exists in the job

 

Export> Status

Job: sys_export_table_01
Operation: Export
Mode: Table
Status: idling
Bytes processed: 0
Current degree of Parallelism: 1
Job error count: 0
Dump File: D:/backup/Oracle/expdp1.dp
Size: 157,286,400
Written Bytes: 20,480
Dump File: D:/backup/Oracle/expdp2.dp
Size: 157,286,400
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp3.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp4.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp5.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp6.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp7.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp8.dp
Written Bytes: 4,096
Dump File: D:/backup/Oracle/expdp9.dp
Written Bytes: 4,096

WORKER 1 status:
Status: Undefined
Object solution: skate
Object Name: test_idx
Object Type: table_export/table/table_data
Number of completed objects: 1
Total number of objects: 3
Number of completed rows: 1,468,189
Completed byte: 314,548,224
Completion percentage: 38
Worker concurrency: 1

 

Run the continue_client command to restore the running of the job. You can also run the start_job command. Continue_client command
Change the mode from interactive command mode to record mode, and then execute start_job.

 

Export> continue_client
Job sys_export_table_01 opened again on Tuesday, August, 2010
Restart "sys". "sys_export_table_01": 'sys/******** @ test as sysdba 'directory = d
Pdump dumpfile = expdp1.dp, expdp2.dp tables = (skate. testbind, skate. testbind_tmp, ska
Te. test_idx) filesize = 150 m
.. Exported "skate". "test_idx" 668.8 MB 6529200 rows
.. Exported "skate". "testbind" 89.51 MB 912936 rows
.. Exported "skate". "testbind_tmp" 872.9 kb 100000 rows
The master table "sys". "sys_export_table_01" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of SYS. sys_export_table_01 is:
D:/backup/Oracle/expdp1.dp
D:/backup/Oracle/expdp2.dp
D:/backup/Oracle/expdp3.dp
The job "sys". "sys_export_table_01" has been completed, but there is an error (completed at 14:08:52)

C:/Documents and Settings/Administrator>

 

Solve this problem

 

 

----- End -----