Oracle tutorial adds data file deletion method by mistake

If you delete a data file in a table space before Oracle10g, the file will still exist in the database data dictionary. Unless you delete the table space, the file information will not be cleared.

But starting with Oracle10gR2, Oracle allows us to completely delete an empty file without leaving a trace.

But note: if you add a file to the SYSTEM tablespace by mistake, leave it there.

For normal table Spaces, follow these steps.

Database version Oracle10gR2:

SQL > select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-ES31en
PL/SQL Release 10.2.0.1.0-ES36en
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-ES45en
NLSRTL Version 10.2.0.1.0-ES49en

Add 1 data file to USERS table space:

SQL > alter tablespace users add datafile '/opt/oracle/oradata/eygle/users02.dbf' size 10M;
Tablespace altered.
SQL > select file#,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 table space 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

Verify that the table space is not occupied by storage:

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

Delete empty data files in a table space:

SQL > alter tablespace users drop datafile '/opt/oracle/oradata/eygle/users02.dbf';
Tablespace altered.

Check the data dictionary. The information for 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