Saturday, December 16, 2006

To take the full database export (Oracle 10g):

Create a Export Directory:
##########################

On Solaris:
-----------
SQL> create or replace directory sys_dmp as '/u02/expdp';
Directory created.

On Windows:
-----------
SQL> create or replace directory sys_dmp as 'D:\expdp';
Directory created.

Create a separate export user:
##############################

SQL> Connect /as sysdba

SQL> CREATE USER expdpadmin IDENTIFIED BY expdp default tablespace users;
User created.

Grant Export and Import Privileges.
###################################

SQL> GRANT CONNECT,RESOURCE TO expdpadmin;
Grant succeeded.

SQL> GRANT exp_full_database to expdpadmin;
Grant succeeded.

SQL> alter user expdpadmin quota unlimited on USERS;
User altered.

SQL> GRANT READ, WRITE ON DIRECTORY SYS_DMP to expdpadmin;
Grant succeeded.

To check on which directories you have privilege to read & write:
#################################################################

SQL> SELECT privilege, directory_name
2 FROM user_tab_privs t, all_directories d
3 WHERE t.table_name(+)=d.directory_name
4 ORDER BY 2,1;

Exporting Full Database:
########################

expdp expdpadmin/XXXXXX full=y directory=sys_dmp dumpfile=full_db_expdp.dmp logfile=full_db_expdp.log

Update: Take a look at my other post on "How to perform a full database export using original export/import utility?"

http://sabdarsyed.blogspot.com/2008/08/how-to-perform-full-database-export.html

Regards,
Sabdar Syed.

2 comments:

Nicolas Gasparotto said...

Hi,

>>SQL> GRANT CONNECT,RESOURCE TO expdpadmin;
Note that these roles (and DBA role) should no more use like Oracle does recommandation, it could be a security failure.

CONNECT, RESOURCE, and DBA

Best regards,

Sabdar Syed said...

Yes, thanks for the comment.