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" FROM DBA_DATA_FILES DF, (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!