Monday, September 12, 2011

Workaround to let non-oracle account access datapump (expdp) dumpfiles

donghua:donghua is my application account, and it's able to read dump files and delete if necessary



[root@vmxdb01 ~]# rm -rf /u01/dumpfiles
[root@vmxdb01 ~]# mkdir /u01/dumpfiles
[root@vmxdb01 ~]# chown oracle:donghua /u01/dumpfiles
[root@vmxdb01 ~]# chmod 770 /u01/dumpfiles
[root@vmxdb01 ~]# chmod g+s /u01/dumpfiles
[root@vmxdb01 ~]# ls -ld /u01/dumpfiles
drwxrws---. 2 oracle donghua 4096 Sep 12 12:37 /u01/dumpfiles
[root@vmxdb01 ~]# exit
logout
[oracle@vmxdb01 u01]$ expdp donghua/donghua@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y

Export: Release 11.2.0.2.0 - Production on Mon Sep 12 12:38:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "DONGHUA"."SYS_EXPORT_SCHEMA_01": donghua/********@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DONGHUA"."TBL_C" 913.3 KB 9997 rows
. . exported "DONGHUA"."TBL_P" 874.0 KB 9997 rows
Master table "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_SCHEMA_01 is:
/u01/dumpfiles/test1.dmp
Job "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:39:17

[oracle@vmxdb01 u01]$ ls -l /u01/dumpfiles
total 2004
-rw-r-----. 1 oracle donghua 2048000 Sep 12 12:39 test1.dmp
-rw-r--r--. 1 oracle donghua 1993 Sep 12 12:39 test1.log
[oracle@vmxdb01 u01]$ su -
Password:
[root@vmxdb01 ~]# su - donghua
[donghua@vmxdb01 ~]$ cd /u01/dumpfiles
[donghua@vmxdb01 dumpfiles]$ ls
test1.dmp test1.log
[donghua@vmxdb01 dumpfiles]$ file test1.dmp
test1.dmp: DBase 3 data file (1728092032 records)
[donghua@vmxdb01 dumpfiles]$ rm test1.dmp
rm: remove write-protected regular file `test1.dmp'? y
[donghua@vmxdb01 dumpfiles]$ rm test1.log
rm: remove write-protected regular file `test1.log'? y
[donghua@vmxdb01 dumpfiles]$ ls -l
total 0
[donghua@vmxdb01 dumpfiles]$ exit
logout
[root@vmxdb01 ~]# exit
logout