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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s