Home » RDBMS Server » Server Utilities » Privileges required for Datapump / DBMS_DATAPUMP
Privileges required for Datapump / DBMS_DATAPUMP [message #248558] Fri, 29 June 2007 09:00 Go to next message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Hi,

I wanted to know what roles / privileges need to be granted for a normal user for performing export / import using DBMS_DATAPUMP package?

I am trying to export few data, but it is giving error saying *
ERROR at line 1:
ORA-06512: at "scott.EXPORT_data", line 218
ORA-06512: at line 1

But If I give a DBA permission to the user, it is exporting without giving any error.

Thanks for your help.
Sheshadri
Re: Privileges required for Datapump / DBMS_DATAPUMP [message #248566 is a reply to message #248558] Fri, 29 June 2007 10:01 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Yes, You need to grant the EXPORT_FULL_DATABASE priviledge to that user from where you want to export.
For details see the link
http://orafaq.com/wiki/Export
http://orafaq.com/node/65

Regards
Sanka Cool

[Updated on: Fri, 29 June 2007 10:01]

Report message to a moderator

Re: Privileges required for Datapump / DBMS_DATAPUMP [message #248571 is a reply to message #248566] Fri, 29 June 2007 10:30 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

[Updated on: Fri, 29 June 2007 10:31]

Report message to a moderator

Re: Privileges required for Datapump / DBMS_DATAPUMP [message #248656 is a reply to message #248558] Fri, 29 June 2007 23:43 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Depending on what you ultimately want to export/import, from a datapump perspective, all you need to get you started is READ/WRITE on a DIRECTORY object that can be used for datapump files.

SQL> grant create session, create table to test_dpump identified by test;

Grant succeeded.

SQL> grant read, write on directory DATA_PUMP_DIR to test_dpump;

Grant succeeded.

SQL> alter user test_dpump default tablespace users quota unlimited on users;

User altered.

SQL> conn test_dpump/test
Connected.
SQL> create table test1 (a1 number);

Table created.

SQL> insert into test1 select level from dual connect by level < 100;

99 rows created.

SQL> commit;

Commit complete.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

E:\>expdp test_dpump/test directory=DATA_PUMP_DIR dumpfile=test.dmp

Export: Release 10.2.0.1.0 - Production on Saturday, 30 June, 2007 0:40:48

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Starting "TEST_DPUMP"."SYS_EXPORT_SCHEMA_01":  test_dpump/******** directory=DATA_PUMP_DIR dumpfile=
test.dmp
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/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 "TEST_DPUMP"."TEST1"                        5.593 KB      99 rows
Master table "TEST_DPUMP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST_DPUMP.SYS_EXPORT_SCHEMA_01 is:
  E:\ORACLE\ADMIN\WIN10G\DPDUMP\TEST.DMP
Job "TEST_DPUMP"."SYS_EXPORT_SCHEMA_01" successfully completed at 00:42:29

E:\>
Previous Topic: why does an export of an empty table with DATAPUMP over 11 Seconds
Next Topic: sql loader script
Goto Forum:
  


Current Time: Wed Jun 26 13:50:03 CDT 2024