alter tablespace offline vs. alter database datafile offline

Published on
7,186 Points
Last Modified:
There is a big difference between
- taking the tablespace offline or
- taking the datafiles offline


Open in new window

1- does a checkpoint on the datafiles
2- takes the datafiles offline


Open in new window

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.

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month