Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problems writing to directories in Oracle 21c Windows

user8005280Oct 12 2021

It looks like, from my perspective, that there is a bug with directories on Oracle 21c on Windows.
This new database version was just released in the last few days.
I am upgrading from Oracle 19c.
Here is the test case. This is just a simple block of PL/SQL code. It opens a text file and writes the current date and time into the file. MYDIRECTORY is any directory created with the CREATE DIRECTORY statement. If you run this block of code more than once, it will simply overwrite the file and create it again. So each time you run the block of code, you get a new timestamp in your file.

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('MYDIRECTORY', 'test.txt', 'w');
  utl_file.put_line(f, to_char(sysdate, 'YYYY.MM.HH24.MI.SS'));
  utl_file.fclose(f);
end;
/

Now the above block of code either works or doesn't work, depending on where you create your directory.
On Oracle 21c you must create both a container database as well as a pluggable database. There is no option for stand-alone database as with Oracle 19c and prior versions.
I have run all my tests on the container database (not on the plug), and running SYS AS SYSDBA.
If you create the directory on the C: (where the Oracle base is installed), then there is no problem. The above block of code will run without error.
If you create the directory ON ANY OTHER DRIVE IN YOUR SYSTEM, the above block of code will fail.
So here is what happens when it works... Logged into the container SYS AS SYSDBA.
First, in the operating system, while logged in as the primary administrative user, create the folder on the C drive. From the command prompt, at the root...
image.pngThen connecting SYS AS SYSDBA, we will create the directory
image.pngdrop directory MYDIRECTORY;
create or replace directory MYDIRECTORY as 'C:\AAA\';
grant read, write on directory MYDIRECTORY to SYS;
grant read, write on directory MYDIRECTORY to IMP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to EXP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to DATAPUMP_EXP_FULL_DATABASE;
grant read, write on directory MYDIRECTORY to DATAPUMP_IMP_FULL_DATABASE;
There is no problem with any of that. Note that a couple of those grants are redundant. I just want to make sure that SYS definitely has the right to write to that directory.
Now we run our test block. No errors.
image.pngWe check for the file in the folder.
image.pngThe file is present and there is no error.
Now we try to do the same thing ON ANY OTHER DRIVE IN THE SYSTEM.
I have tested this on two different SATA drives, as well as an NMVE raid array, as well as a thumb drive. It fails on all of them. Here I am going through the thumb drive so that other people can easily replicate the test. First I format the thumb drive. It ends up as drive D:
image.pngimage.pngClearly there is nothing special about this disk. It has just been formatted. It should have all default properties. I create the folder as before.
image.pngFrom Windows explorer, I double check permission on the folder. "Everyone" has full rights. There should be no trouble writing to this disk.
image.pngNow, as before, while logged in SYS AS SYSDBA, drop and re-create the directory. Now using drive D although everything else is the same.
image.pngFinally run my test code.
image.pngAnd that will be the same error regardless of what drive you run that on. Any other drive other than C: will fail. Permission don't matter. You can grant everyone full write privileges, it won't matter. You can assign ownership of the drive root as well as the AAA subfolder, to the same user as the database was installed as, and it won't matter. Nothing you do will fix it. The directory simply will not write. I have tried dozens of variations.
This problem did not occur on Oracle 19c.
Any help is appreciated. Thank you,

Comments

user8005280

p.s. That test block should have been this. I forgot the day of the month but it doesn't matter.
declare
f utl_file.file_type;
begin
f := utl_file.fopen ('MYDIRECTORY', 'test.txt', 'w');
utl_file.put_line(f, to_char(sysdate, 'YYYY.MM.DD.HH24.MI.SS'));
utl_file.fclose(f);
end;
/

user5745638

We have exakt the same problem on Oracle 21c Windows.
Ist clearly a Bug!

Daniel Hurmuz-Oracle

Hi,
Please open an SR for this and we can take it forward to Oracle Development.
Thanks,
Daniel (Oracle Support)

user7009256

Hi,
I'm experiencing the same problem as well. I also noticed that if you install Oracle on to any other drive, then that is the only one that can be written to. I've installed Oracle on the D: drive and now you can write a file to the D: drive and not the C: drive using your same example as above.
Thanks,
Please let me know when this gets resolved.

user8005280

Daniel,
Who are you talking to? Me or the other guy?
I just saw this just now.
This here thread is the well document bug report, which two other users have confirmed.
Please forward to Oracle Support for resolution.
Thank you,

Daniel Hurmuz-Oracle

Hi,
To those who are interested to have this issue investigated by Oracle Development, please open an SR with Oracle Support.
Thanks,
Daniel (Oracle Support)

Jeff Chirco

Did anyone submit a bug for this and have the bug number? Also what was the resolution if any? We are experiencing the same issue however with 21c XE. Would like to know was some work around.

Wudthichai N.

How to correct this case?

Solomon Yakobson

I don't have 21C to test, but could you try removing trailing backslash?:

create or replace directory MYDIRECTORY as 'C:\AAA';

In old versions pre-dating directory objects where we had to use UTL_FILE_DIR trailing slash or backslash (depending on OS) was not allowed and was causing issues. Who knows could be reincanation.
SY.

user8005280

@Solomon Yakobson
The backslash has nothing to do with it.
It DID work on C drive.
It did not work on D drive or any other drive.
So if it did work on at least one drive, then the backslash has nothing to do with it.
Thanks,

Solomon Yakobson

Then most likely D drive isn't local drivs and is a network drive.
SY.

Wudthichai N.

The Oracle Support say that

"The UTL_FILE code is exactly same whether it is accessing D driver or F driver. So this is not a coding or Database issue.
It is the user permissions which are probably changing. Please check with your OS Administrator for further on this issue.
Or you can use Process Monitor to find the exact error which is thrown by the OS."

I cannot solve this problem.
How do you think???

Solomon Yakobson

"It is the user permissions which are probably changing" - well I don't know where did you get this quote from but "user" will throw off most people since they, most likely, assume user is their username. UTL_FILE runs on database server under OS ID oracle on Linux/Unix or under OS ID database service is started on Windows. So permissions pertain to the above OS ID. In addition to permissions on Linux/Unix NFS drive must be mounted with Oracle required options.
SY.

User_6H0KU

Hello,

Does anyone have found a solution to this issue ? Having the same here..

Thank you

User_3ABCE

Check that the service is running under a regular user, not a local system account. Try to stop and start Oracle database service (services.msc) manually. If this help, you can add service dependency.

Also, try to use UNC-paths instead of drive letters.

user3177088

Hello,

Same problem. Oracle Home on E: and my directory is on F:

CREATE OR REPLACE DIRECTORY DD_JOBSM31TRACE AS 'E:\Database21\T21P\Jobs\Sigal\Trace';
CREATE OR REPLACE DIRECTORY DD_JOBSM31TRACE2 AS 'F:\Database21\T21P\Jobs\Sigal\Trace';

DD_JOBSM31TRACE : works fine

DD_JOBSM31TRACE2 : ORA-29283

Test code :

DECLARE
hFile UTL_FILE.FILE_TYPE;
BEGIN

hFile := UTL_FILE.FOPEN('DD_JOBSM31TRACE', 'debugoutils.txt', 'r', 4096);
UTL_FILE.FCLOSE(hFile);

END;
/

Same OS rights on “E:\Database21\T21P\Jobs\Sigal\Trace” and “F:\Database21\T21P\Jobs\Sigal\Trace”

A solution ?

Thank you

user2615427

Beginning of 2024. Still an issue after 2+ years after release.

1 - 17

Post Details

Added on Oct 12 2021
17 comments
3,944 views