Expdp when encountering ORA-31693 & ORA-02354 & ORA-01466, expdpora-31693

Source: Internet
Author: User
Tags table definition

Expdp ORA-31693 & ORA-02354 & ORA-01466, expdpora-31693
Expdp encountered ORA-31693 & ORA-02354 & ORA-01466
Execute the expdp export and expdp command on a schema:
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 because the parallel parameter is used. The official document explains that parallel io server processes cannot write one file at a time,
And when an io server process is waiting to write the rest of the io server process, the ORA-39095 is reported.
Solution: (1) Cancel parallel, but this will affect the performance of expdp;
(2) Specify the % u variable in the dumpfile when writing the Export Statement, so that it can be freely allocated with 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 solved, but a new error is reported:
ORA-31693: Table data object "HQ_X1". "TBL_BILL": "SYS_P109" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data-table definition has changed
Check dba_objects. The DDL operation was performed at that time:
LAST_DDL_TIME
2015-06-23/17: 32: 19
22:57:10


I suspect that GRANT is the most likely type. Check v $ SQL and check the following SQL statement, and the time is exactly the same:
Insert into objauth $ (obj #, grantor #, grantee #, privilege #, sequence #, option $, col #) values (: 1,: 2,: 3,: 4,: 5,: 6,: 7) 2015-06-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 );
An Oracle note says this:
The issue is discussed in
Bug 8534161-ORA-1466 reported by expdp when using FLASHBACK_SCN AND PRIVILEGES WERE GRANTED
Oracle Development confirmed this is expected behavior and not a bug.
The solution is as follows:
WORKAROUND
Do not grant any privileges to any object while Expdp is running with FLASHBACK_SCN.


I also attached a test:
Open 2 sessions,
Session1 first opens a cursor:
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 cursor:
SQL> print x
USERNAME USER_ID CREATED
------------------------------------------------------
HQ_X2 100 12-9 month-14
HQ_X2 100 12-9 month-14
It can be seen that for the SELECT operation, there is no problem when the grant operation is executed in another session during the execution period, but it does not work in expdp. This is why Oracle initially considered this a BUG.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.