Error during export dump file oracle using sql developer Version Version 18.1.0.095 Build 095.1630

Question

I want to export dump file DDL my database (Oralce database version 11.2.0.4). I created user and granted permission (sysdba). I connected to database using user above, I choose View --> DBA --> Data Pump --> Data Pump Export Wizard --> choose connection above --> and it alert error "Cant use Data Pump as sys.".

Answer 1

Here's an interesting thing about SYS, it cannot use SERIALIZABLE transactions. This also applies to users connected as SYSDBA. Find out more.

The SERIALIZABLE isolation level means that all statements in a transaction are read consistent. The default for Oracle is READ COMMITTED, which applies at the statement level. The difference is this: if we run select * from T1 followed by select * from T2 under READ COMMITTED isolation level then we will any changes committed to T2 while we were querying T1. That is, the result sets for T1 and T2 are both consistent set of records, but we might have seen different results if we had run the queries the other way round. Whereas, under SERIALIZABLE the result sets are consistent with the start of the transaction. It doesn't matter what order we query the tables, the results are stable.

You can see why this is important for exporting. The entire set of exported tables must be consistent in order to guarantee the relational integrity of a subsequent import. We don't want to export a child table with records which depend on records added to the parent table after we exported it. (The old Export utility allowed us to set consistency=N - indeed that was the default! - but Data Pump protects us from ourselves).

So this is why we can't run exports as SYS, or SYSDBA users. Fortunately there is a simple solution: revoke SYSDBA from your user and grant it DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE roles instead. [Find out more][2].

Answer 2

I found answer to my question: - when login we choose Role default

• We need grant dba permission to user Ex:

  Grant dba to vinhhc_vsc;