ALTER TABLESPACE OFFLINE vs. ALTER DATABASE DATAFILE OFFLINE

http://www.quest-pipelines.com/pipelines/dba/tips06.htm

There is a big difference between:

• Taking the tablespace offline and
• Taking the datafiles offline

ALTER TABLESPACE … OFFLINE

1. Does a checkpoint on the datafiles
2. Takes the datafiles offline

ALTER DATABASE DATAFILE … OFFLINE does not perform a checkpoint, so that if the database is open, you may need to perform media recovery when bringing it online.

That is the reason why:

• You cannot do ‘alter database datafile … offline’ if you are in noarchivelog (but tablespace offline works)
• You cannot do ‘alter tablespace … offline’ if database is read-only (but datafile offline works)

Note that in both cases, you can check the STATUS column from v$datafile to see if the file is online, offline or needs recovery.