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...
Then connecting SYS AS SYSDBA, we will create the directory
drop 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.
We check for the file in the folder.
The 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:
Clearly there is nothing special about this disk. It has just been formatted. It should have all default properties. I create the folder as before.
From Windows explorer, I double check permission on the folder. "Everyone" has full rights. There should be no trouble writing to this disk.
Now, as before, while logged in SYS AS SYSDBA, drop and re-create the directory. Now using drive D although everything else is the same.
Finally run my test code.
And 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,