EXPDP encountered ora-31693&ora-02354&ora-01466
Perform EXPDP export to a schema, EXPDP command:
Nohup EXPDP hq_x1/hq_x1 dumpfile=hq_x1. DMP directory=dir1 parallel=8 flashback_scn=10838324803 &
Ora-39095:dump file space has been exhausted:unable to allocate 8192 bytes
This is usually due to the use of the parallel parameter, the official document is interpreted as parallel IO server processes write file cannot write one at the same time,
And when an IO server process writes the rest of the IO server process while waiting, it will report ORA-39095.
So the solution: (1). Cancel the parallel, but this will affect the performance of EXPDP;
(2) When writing the export statement, specify dumpfile to use the variable%u (uppercase and lowercase), so that it can freely allocate the dump file.
So I used the variable to re-export:
Nohup EXPDP hq_x1/hq_x1 dumpfile=hq_x1%u.dmp directory=dir1 parallel=8 flashback_scn=10838324803 &
The previous problem has been resolved, but a new error has been encountered:
Ora-31693:table data Object "Hq_x1". " Tbl_bill ":" sys_p109 "failed to Load/unload and was being skipped due to error:
Ora-02354:error in exporting/importing data
ora-01466:unable to read data-table definition had changed
Look at the next dba_objects, and sure enough, there are DDL operations at that point:
Last_ddl_time
2015-06-23/17:32:19
2015/1/12 22:57:10
Suspect is the most likely grant or the like, check the V$sql, sure enough to have the following SQL, the time is also fully corresponding to:
Insert into objauth$ (obj#, grantor#, grantee#, privilege#, sequence#, option$, col#) VALUES (: 1,:2,:3,:4,:5,:6,:7) 2015-0 6-23/17:32:19
--insert into objauth$ (option$,grantor#,obj#,privilege#,grantee#,col#,sequence#) VALUES (decode (: 1,0,null,:1),: 2, : 3,:4,:5,decode (: 6,0,null,:6), object_grant.nextval);
One of Oracle's note is that:
The issue is discussed in
Bug 8534161-ora-1466 reported by EXPDP when USING FLASHBACK_SCN and privileges were granted
Oracle Development confirmed this was expected behavior and not a bug.
and give the solution:
Workaround
does not grant any privileges to any object while EXPDP is running with FLASHBACK_SCN.
Attach another test I made myself:
Open 2 Session,
Session1 open a cursor first:
sql> exec open:x for select * from B where user_id=100;
PL/SQL procedure successfully completed.
Session2 executes a grant:
Sql> Grant Select on B to mine;
Grant succeeded.
Session1 Print Cursors:
sql> Print X
USERNAME user_id CREATED
------------------------------ ---------- --------------
HQ_X2 100 December-September-14
HQ_X2 100 December-September-14
You can see that for a select operation, there is no problem when executing grant at another session during execution, but not under EXPDP, which is why Oracle thought it was a bug at first.
EXPDP encountered ora-31693&ora-02354&ora-01466
