Search My Oracle Blog

Custom Search

24 November, 2008

expdp to the default directory without the DBA role

Here I show how EXP_FULL_DATABASE (and not the DBA Role) is sufficient for an account to be able to export to the default directory.



ora10204>pwd
/oracle_fs/ora10204/admin/ORT24FS/dpdump
ora10204>ls -l
total 0
ora10204>sqlplus hemant/hemant

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 24 22:15:31 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user expdp_test identified by expdp_test;

User created.

SQL> alter user expdp_test default tablespace users;

User altered.

SQL> alter user expdp_test quota unlimited on users;

User altered.

SQL> grant create session, create table to expdp_test;

Grant succeeded.

SQL> connect expdp_test/expdp_test
Connected.
SQL> create table my_exp_test_tab (col_1 varchar2(5));

Table created.

SQL> insert into my_exp_test_tab values ('a');

1 row created.

SQL> commit;

Commit complete.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>
ora10204>expdp expdp_test/expdp_test

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 November, 2008 22:17:19

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null


ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 24 22:18:04 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: hemant/hemant

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant exp_full_database to expdp_test;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>
ora10204>expdp expdp_test/expdp_test

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 24 November, 2008 22:18:54

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "EXPDP_TEST"."SYS_EXPORT_SCHEMA_01": expdp_test/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
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/TABLESPACE_QUOTA
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
. . exported "EXPDP_TEST"."MY_EXP_TEST_TAB" 4.937 KB 1 rows
Master table "EXPDP_TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPDP_TEST.SYS_EXPORT_SCHEMA_01 is:
/oracle_fs/ora10204/admin/ORT24FS/dpdump/expdat.dmp
Job "EXPDP_TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:19:22

ora10204>pwd
/oracle_fs/ora10204/admin/ORT24FS/dpdump
ora10204>ls -l
total 164
-rw-r----- 1 ora10204 dba 159744 Nov 24 22:19 expdat.dmp
-rw-r--r-- 1 ora10204 dba 1553 Nov 24 22:19 export.log
ora10204>



Without the EXP_FULL_DATABASE privileges, the account could not do an export to the default DATA_PUMP_DIR.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016