Privileges required for Datapump / DBMS_DATAPUMP [message #248558] |
Fri, 29 June 2007 09:00 |
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 #248656 is a reply to message #248558] |
Fri, 29 June 2007 23:43 |
|
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:\>
|
|
|