DROP DATAFILE FROM TABLESPACE IN ORACLE

Check the tablespace for which datafile needs to be removed

SQL> select file_name from dba_data_files where tablespace_name='RCAT';

/apps01/oradata/orcl19x/rcattb1
/data01/base/oradata/ORCL11X/datafile/RCAT5045360.dbf
/data01/base/oradata/ORCL11X/datafile/RCAT20191757.dbf
/data01/base/oradata/ORCL11X/datafile/RCAT87538078.dbf

Drop the individual datafiles

SQL> alter tablespace rcat drop datafile '/data01/base/oradata/ORCL11X/datafile/RCAT5045360.dbf';

Tablespace altered.

SQL> alter tablespace rcat drop datafile '/data01/base/oradata/ORCL11X/datafile/RCAT20191757.dbf';

Tablespace altered.

SQL> alter tablespace rcat drop datafile '/data01/base/oradata/ORCL11X/datafile/RCAT87538078.dbf';

Tablespace altered.

or

kIsH@Xhydra<>alter database datafile '/tmp/tmpd.dbf' offline;

Database altered.
or

kIsH@Xhydra<>alter database datafile '/tmp/tmpd.dbf' offline drop;

Database altered.


Leave a Reply