Today I noticed an interesting fact about Oracle data file by accident — a data file name can cantain white space!

I added a few temp data files and then I thought that’s more than enough. So I tried to drop and got the following error:

SQL> alter database tempfile '/dbase/system/PROD/temp15.dbf' drop INCLUDING DATAFILES;
alter database tempfile '/dbase/system/PROD/temp15.dbf' drop INCLUDING DATAFILES
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/dbase/system/PROD/temp15.dbf" in the current container

When I checked files at the OS level, I did see them:

[oracle@joetest ~]$ ls -lrt /dbase/system/PROD/temp*
total 304990372
-rw-r-----. 1 oracle oinstall 34358697984 Jan 11 18:31 temp1.dbf
-rw-r-----. 1 oracle oinstall  1132470272 Jan 11 20:00 system1.dbf
-rw-r-----. 1 oracle oinstall  4970258432 Jan 11 20:01 sysaux1.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp9.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp15.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp14.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp6.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp2.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp11.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp8.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp4.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp13.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp7.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp5.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp12.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp10.dbf
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:01 temp3.dbf

But if I was checking individual one, I got the same thing: no such file!

[oracle@joetest ~]$ ls -lrt /dbase/system/PROD/temp3.dbf
ls: cannot access /dbase/system/APROD/temp3.dbf: No such file or directory

That’s really weird! I was thinking somehow the control file was out of sync, but I could see them when I queried DBA_TEMP_FILES.

Then I noticed that when I use “tab” key to match the file name at the OS level, I got a trailing “/” at the end of file name.

[oracle@joetest ~]$ ls -lrt /dbase/system/PROD/temp3.dbf\
-rw-r-----. 1 oracle oinstall 25769811968 Jan 11 20:06 /dbase/system/PROD/temp3.dbf

That exaplained it. There is a space at the end of file name. So I looked at the adding temp data file statement, yes, I acciendentally put a space at the end which has caused the issue.

ALTER tablespace temp add TEMPFILE '/dbase/system/APROD/temp2.dbf ' size 24g;

As a test, I added the following file “temp 2.dbf ” with 2 white spaces:

SQL> ALTER tablespace temp add TEMPFILE '/dbase/system/PROD/temp 2.dbf ' size 4g;

Tablespace altered.

SQL> !ls -lrt /dbase/system/PROD
total 3250520
-rw-r-----. 1 oracle oinstall  536879104 Jan 11 18:58 temp1.dbf
-rw-r-----. 1 oracle oinstall 1069555712 Jan 11 19:52 system1.dbf
-rw-r-----. 1 oracle oinstall 1761615872 Jan 11 19:52 sysaux1.dbf
-rw-r-----. 1 oracle oinstall 4294975488 Jan 11 19:54 temp 2.dbf


SQL> alter database tempfile '/dbase/system/PROD/temp 2.dbf ' drop INCLUDING DATAFILES;

Database altered.

To drop such a file, just including white space within the single quotes as you add it.

• Oracle