0

I have created a user say A. I want to export some of my schemas using A user.

For example, I have B and C users. I want to export B schemas using A user. Which grant do I have to use?

I am using this statement:

EXP FILE = B.DMP OWNER(B)

But it is not working. A user has only select grant on B schemas. There is no other grant A has.

Which grant do I have to use?

1

http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2641

If you do not have the system privileges contained in the EXP_FULL_DATABASE role, then you cannot export objects contained in another user's schema.

  • Although I gave the user EXP_FULL_DATABASE grant I could not export. But when I try with DBA user I can export. Am I doing something wrong? – osman Jul 15 '14 at 7:01
2

First, just to be certain we're covering our basis, this piece of code:

EXP FILE = B.DMP OWNER(B)

won't work. That's not a properly formatted EXP command. But assuming that you're using a properly formatted command, all that a user really needs is the EXP_FULL_DATABASE role, and they should be able to export any object in the database:

SQL> create user captjackvane identified by x default tablespace users temporary tablespace temp;

User created.

SQL> grant create session to captjackvane;

Grant succeeded.

exp userid=captjackvane/x owner=rpt file=rpt.dmp log=rpt_dmp.log resumable=Y

This results in

EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully

But when I add the grant:

SQL> grant exp_full_database to captjackvane;

Grant succeeded.

We see that I can now do the export:

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user RPT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user RPT 
About to export RPT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export RPT's tables via Conventional Path ...
. . exporting table               AD_HOC_FIELD_SET          2 rows exported

If you've granted this permission, then there are a few other things you may want to check. The first is the permissions on the directory you're trying to write your dump file to; your Oracle type user will need to be able to read/write in that directory.

Can you post the exact error message you're getting?

1

Grant EXP_FULL_DATABASE privilege to user A and then export.

SQL> grant EXP_FULL_DATABASE to A;

  • Although I gave the user EXP_FULL_DATABASE grant I could not export. But when I try with DBA user I can export. Am I doing something wrong? – osman Jul 15 '14 at 7:06
  • You need to make it a default role. – Colin 't Hart Jul 15 '14 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.