How to drop obsolete data files from Oracle

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

In SQL Plus, we dropped the Harvest tablespaces:

SQL> drop tablespace harvestblob;

Tablespace dropped.

SQL> drop tablespace harvestmeta;

Tablespace dropped.

SQL> drop tablespace harvestindex;

Tablespace dropped.

SQL> commit;

Commit complete.

We then went back to HDBSetup and tried to create a new database, but got this error message:

Creating database tablespaces...name
State: 60 Native Error Code: 1119me
[CA Harvest SCM][ODBC 20101 driver][20101]ORA-01119: error in creating database file 'HarvestMeta.ora'space File Size
ORA-27038: created file already exists
Additional information: 1ile Size

Error creating tablespace HARVESTMETA
Press the Enter key to continue

Any ideas ?

Environment

CA Harvest SCM all versions and platforms
Oracle database

Resolution

This gets sticky because the data files are registered in the Oracle instance.  You cannot simply delete them.

A solution was found in this forum post:
https://stackoverflow.com/questions/35138581/how-can-i-delete-a-datafile-that-has-no-tablespace-without-accessing-the-filesys

1. Create a new tablespace
2. Add the datafile to the tablespace
3. Drop the tablespace and its datafiles

For example:

CREATE TABLESPACE MYAPP
DATAFILE '/mnt/data/app/oracle/oradata/MYAPP_02.dat'
SIZE 100M AUTOEXTEND ON;

alter tablespace MYAPP add datafile '/mnt/data/app/oracle/oradata/MYAPP_01.dat';

drop tablespace MYAPP including contents and datafiles;