ORA-27040
Tried to add a data file to a tablespace, but it failed with ORA-01119 and ORA-27040.
SQL> ALTER TABLESPACE EXAMPLE ADD DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDBX/example02.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER TABLESPACE EXAMPLE ADD DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDBX/example02.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/ORCLCDB/ORCLPDBX/example02.dbf'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
ORA-27040 means that the absolute path of data file you specified in the statement is invalid, you should make sure the path is existing in the file system.
In this case, we thought the location of data files should be under ORCLPDBX, but no, they are still under ORCLPDB1.
Solutions
Actually, OS error in the stack tells us the real problem, it could be one of the following:
- Linux-x86_64 Error: 2: No such file or directory
- Linux-x86_64 Error: 13: Permission denied
- Linux-x86_64 Error: 28: No space left on device
- Linux-x86_64 Error: 5: Input/output error
The directory you specified in the statement cannot be seen by the user, it could be misspelled or missing. You check the existence of the path.
The directory does exist, but it cannot be write because of permission issue. Please check the directory's ownership and permission set for sure.
The used space of destination is full or nearly full, it has no enough room to write a new data file. Please check the usage of destination space on the file system.
The storage could be corrupted.
In this case, we should use correct and existing path for creating the data file.
SQL> ALTER TABLESPACE EXAMPLE ADD DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
Tablespace altered.
We solved it.