NID-00137: All datafiles that are not dropped should be readable | Oracle Database

The following opinions expressed in this post are my own and in no way connected to my employer. 

Issue:

When Performing NID operation on Database for DB Name Change( for example) you may run in to below error because of Temp Files. 

NID-00137: All datafiles that are not dropped should be readable

Solution:

Dropping the TEMP files which were causing error:

set pagesize 

set linesize 600

select 'alter database tempfile '''||name||''' drop including datafiles;' as cmd from v$tempfile;

OR

begin

for i in (select 'alter database tempfile "'||name||"' drop including datafiles' as cmd from v$tempfile)

loop

execute immediate i.cmd;

end loop;

end;

/