I'm trying add more data into my schema, but I encounter the following error

ORA-01116: error in opening database file 14
ORA-01110: data file 14: '/u01/oracle/oradata/REVA/ra_tbsp07.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

I've run the following query to try and identify what's wrong with the datafile (I'm not the DBA - hes on leave : but i do have SYS access)

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024,0) "Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
(SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
(SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id AND df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name, df.file_name

and I get the following output

Tablespace Name     File Name                               Size(M) Used(M) Free(M) % Used
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp.dbf    32768   30548   1983    93
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp02.dbf  32768   31953   640     98
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp04.dbf  32768   28068   1745    86
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp05.dbf  32768   28920   1689    88
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp06.dbf  32768   28686   2014    88
RA_TBSP             /u01/oracle/oradata/REVA/ra_tbsp07.dbf      0       0      0

From what I can see is the datafile is empty, now I know there is no feature in oracle wherein you can drop a datafile (like how you drop objects) After reading through https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:515222144417, It appears that the only "clean" way of doing this is to export all tables in the other datafiles, and drop the tablespace and then re-import. That isn't an option for me.

I was wondering if there is some other alternative to this problem? If i add another datafile, say ra_tbsp08.dbf will I be able to add more data into my tablespace? Or will I still face this error?

Will resizing the datafile help? Or is this even an error caused by oracle? as in the settings have to be changed in UNIX via chmod command?

Basically I require a solution with as little modification to the DB as possible that will help me work around this! I hope such a solution exists!

  • 3
    Does the file exist o the file system? It appears that it used to exist and now does not. If that's the case, you're looking at doing a database recovery. Hopefully, the DBA left excellent documentation on how to do that in your environment. – Justin Cave Apr 13 '14 at 9:17
  • 1
    Please execute the Linux/Unix command ls -l /u01/oracle/oradata/REVA/ra_tbsp07.dbf on the database server and append the output to your post. – miracle173 Apr 13 '14 at 15:23
  • It appears that the datafile does not exist. When I tried to locate it with the above command, i got the following error ra_tbsp07.dbf: No such file or directory I think I shall leave this for the DBA to handle as I'm not the owner – Joshua1729 Apr 14 '14 at 7:26
  • I think the biggest question is how you tried to "add more data into my schema" -- if you wanted to do it right, you would have needed DBA authority to do an alter tablespace add datafile '/u01/oracle/oradata/REVA/ra_tbsp07.dbf' size 32g; – Mark Stewart Oct 28 '15 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.