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
$ cp /ORASYS/ORCLDB/ORCLDB/MYTBS.dbf /ORADATA1/ORCL/MYTBS.dbf

— delete the tablespace’s datafile from old location
$ cp /ORASYS/ORCLDB/ORCLDB/
$ 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,
status
from dba_tablespaces
where tablespace_name=’MYTBS’;

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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