Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

Question

I am using Oracle 11g and want to export a table using below statement

C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER

After this statement cmd is giving this

UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

I proceeded with

Username:  SYS@SMSPSETUP AS SYSDBA
Password:

And finally I got

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

I have created directory 'Mydir' as below

SQL>CREATE DIRECTORY Mydir AS 'C:\Users\osman\Desktop\Export';

And gave everyone full privileges to this folder from the windows folder system.

When run the below statement:

select directory_name, directory_path from dba_directories; where directory_name='Mydir';

I can see the path 'C:\Users\osman\Desktop\Export'

So why am I getting this error?

Answer 1

I just wanted to chime in and share what got me past this error. In my case (Ora 11.2.0.3 on Win 7) I found I needed the trailing '\' in my directory path.

Replaced:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID'

With:

CREATE OR REPLACE DIRECTORY dpump_dir AS 'C:\Data\SID\'

Answer 2

Firstly, the oracle docs are VERY clear that you shouldn't use data pump as SYS:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL824:

Do not invoke Export as SYSDBA, except at the request of Oracle technical support

The problem will almost certainly be permissions related. The user that is running the Oracle database is the user that attempts to write to the file, so check that user can write to the folder in question (and can access it through all the parent folders).

Perhaps create a folder in the root of a drive (e.g. "E:\TestExport" or similar) so that you have fewer parent folders to worry about, then use this within Oracle to determine if this is the case.

Answer 3

check the file permissions because the export is done by oracle user whereas the directory belongs to osman user

Answer 4

Yeah, I found the solution. It is because my oracle database is located on another computer on the network. So, when I try to export to like 'c:\' directory, oracle is trying to export to its own C:\ directory not mine.

I used network address of the folder as export directory and the problem was solved. Use for example

\\OSMAN\dump_directory

Answer 5

oracle SQL statement "CREATE DIRECTORY Mydir AS 'C:\Users\osman\Desktop\Export';" can't create any direcotry on Windows or Linux, it's just a mapping between variable Mydir and directory 'C:\Users\osamn\Desktop\Export'

so you need to make sure your windows has 'C:\Users\osman\Desktop\Export'

hope this can help you !

Answer 6

in addition to the above, you may also encounter the following (on windows) :

• Go to services.
• Search for "OracleService"
• Right Mouse Click -> properties -> Log On (Tab)
• Change this account (current user name) -> to a more privileged one (of course you can change the current user privileges as well)
• Restart the service and it should work.