Oracle Fix Missing Datafile

To fix the missing user tablespace,
1. Create a new tablespace
2. Drop the old tablespace
3. Change the new tablespace name to old tablespace name
4. Change affected user default tablespace to new one.

Example:
1.  Create a new tablespace
create tablespace USERS datafile '/data6/GOWEEK/USERSPACE.dbf' size 200M autoextend on next 500k maxsize 500m;

2. Drop the old tablespace    
DROP TABLESPACE USERSPACE INCLUDING CONTENTS AND DATAFILES;       

3. Change new table space name  
ALTER TABLESPACE USERS RENAME TO USERSPACE;

4. Use below script to change user default table space, you may need to change the statement

SQL> select 'ALTER USER '||username||' DEFAULT TABLESPACE USERSPACE;'  FROM DBA_USERS WHERE DEFAULT_TABLESPACE like '%_$%' and username not in('SYS','SYSTEM');

Reference:

select * from dba_data_files;

select * from dba_users;

select *  from database_properties where property_name like 'DEFAULT%TABLESPACE';