Problem solve Get help with specific problems with your technologies, process and projects.

Error trying to recreate nonsystem control file

I am administering an Oracle 8.1.7 database. One of the nonsystem datafiles is lost. When I try to create the file it gives the following error though I have the entry of the same in v$datafile. I have tried recreating the control file and then creating the datafile, but still, it gives the following error:

 SQL> alter database create datafile
 alter database create datafile
 ERROR at line 1:
 ORA-01178: file 9 created before last CREATE
 CONTROLFILE, cannot recreate
 ORA-01110: data file 9:
Few days back I had recreated the control file but that also had the entry for this file.

You cannot create the file in this manner. While other vendor's RDBMS platforms may support this convention, Oracle does not. Why not drop the entire tablespace and then recreate it? Use the DROP TABLESPACE and CREATE TABLESPACE commands. When creating the tablespace again, you will be creating the datafile at the same time. This is akin to what you are trying to accomplish. Otherwise, use a backup to restore the lost datafile.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.