What is the EXPORT privilege on ORACLE

Question

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?

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

Answer 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?

Answer 3

Grant EXP_FULL_DATABASE privilege to user A and then export.

SQL> grant EXP_FULL_DATABASE to A;