5

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.". enter image description here

Improve this question
3
  • The error is pretty clear: don't use the SYS account for that Commented Sep 4, 2018 at 9:10
  • I create new user and grant sysdba permission not sys user Commented Sep 4, 2018 at 9:13
  • Admin close to my question please! Commented Sep 4, 2018 at 9:53

2 Answers 2

Reset to default
7

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].

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I have found the answer to my question. It's easy to make
I granted DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE but it don't make Data Pump, and I must grant dba to user.
3

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

enter image description here

  • We need grant dba permission to user Ex:

    Grant dba to vinhhc_vsc;

Improve this answer

1 Comment

If you're creating a user just to do exports and imports it would be better to explicitly grant just the required privileges. But if you want a general admin user then go-ahead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.