CODEWITHSUNDEEP
oracle-databaseoracle11grman
Mark
Mark

Reputation: 31

if an oracle datafile gets too big for the directory what does one do

if I have a datafile in a directory and it is getting too big for the directory what command in Oracle can I use to move that file, or do I make another datafile into another directory?

ALTER TABLESPACE 
   users 
ADD DATAFILE 
   ‘/ora01/oracle/oradata/booktst_users_02.dbf’ 
size 100m

Upvotes: 1

Views: 2384

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You can do either. Adding a datafile is simpler as it can be done online, with the command you've shown. As long as you don't mind having the datafiles spread across directories, and you don't need to remove the filesystem the file is currently on, this ought to be fine for you.

The documentation explains how to relocate a datafile. I won't quote the whole thing, but essentially, with the tablespace offline, copy the datafile(s) to another disk then update the database control file (and data dictionary) to point to the new location with an ALTER TABLESPACE...RENAME DATAFILE command, then you can bring the tablespace back online.

As the documentation also mentions, make a backup of the modified database. Once the tablespace is using the new copies of the files you can delete the old ones from the filesystem - carefully, make sure you're working on the unused copy! - to free up the disk space.

Another option is to create a new tablespace with its datafile(s) on a larger filesystem and them move all the objects into that, then drop the original. The tablespaces remain online, but access to the objects being moved will be temporarily blocked; if the objects are large that may be a significant issue and much slower than moving the datafiles.

Upvotes: 3

Related Questions