How to delete a data file by mistake in the Oracle tutorial

If you delete a data file in a tablespace before Oracle10g, the file will still exist in the Database Data Dictionary. Unless you delete the tablespace, the file information will not be cleared.

However, from oracle10gr2, Oracle allows us to completely delete an empty file without any trace.

But note: if you wantSystemIf a file is added incorrectly in the tablespace, it is better to leave it unchanged.

For common tablespaces, follow these steps.

Database Version oracle10gr2:

SQL> select * from V $ version; Banner ---------------------------------------------------------------- OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Prod PL/SQL release 10.2.0.1.0-Production Core 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0-Production Nlsrtl version 10.2.0.1.0-Production

Add a data file to the users tablespace:

SQL> alterTablespaceUsers Add datafile '/opt/Oracle/oradata/eygle/users02.dbf' size 10 m; Tablespace altered. SQL> selectFile#, Name from V $ datafile; File # Name ------------------------------------------------------------ 1/opt/Oracle/oradata/eygle/system01.dbf 2/opt/Oracle/oradata/eygle/undotbs01.dbf 3/opt/Oracle/oradata/eygle/sysaux01.dbf 4/opt/Oracle/oradata/eygle/users01.dbf 5/opt/Oracle/oradata/eygle/users02.dbf 5 rows selected.

Confirm the tablespace file information:

SQL> select file_name, file_id from dba_data_files where tablespace_name = 'users '; File_name file_id ------------------------------------------------------------ /Opt/Oracle/oradata/eygle/users02.dbf 5 /Opt/Oracle/oradata/eygle/users01.dbf 4

Confirm that the tablespace is not occupied by storage:

SQL> select segment_name, file_id, blocks from dba_extents where file_id = 5; No rows selected

Delete An Empty data file in a tablespace:

SQL> alterTablespaceUsers drop datafile '/opt/Oracle/oradata/eygle/users02.dbf '; Tablespace altered.

Check the data dictionary. The information of this empty file has been completely cleared:

SQL> select file_name, file_id from dba_data_files where tablespace_name = 'users '; File_name file_id ------------------------------------------------------------ /Opt/Oracle/oradata/eygle/users01.dbf 4