<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

alter tablespace offline vs. alter database datafile offline

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

 
ALTER TABLESPACE ... OFFLINE

Open in new window

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

 
ALTER DATABASE DATAFILE ... 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.
0
Comment
0 Comments

Featured Post

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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.
Via a live example, show how to take different types of Oracle backups using RMAN.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month