Change the location of datafiles

Note: steps from sys user

— offline the tablespace
SQL>alter tablespace MYTBS offline

— copy the tablespace’s datafile to new location

— delete the tablespace’s datafile from old location
$ rm MYTBS.dbf

— rename the tablespace’s datafile in the control files
SQL> alter tablespace MYTBS rename datafile ‘/ORASYS/ORCLDB/ORCLDB/MYTBS.dbf’ to ‘/ORADATA1/ORCL/MYTBS.dbf’

— online the tablespace
SQL> alter tablespace MYTBS online

— Check the status
select tablespace_name,
from dba_tablespaces
where tablespace_name=’MYTBS’;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.