Square DBA - Oracle DBA Talk

Today I needed to move a tablespace between mount points. My primary mount points were starting to fill up and I needed to make some breathing room. I recently added secondary storage to the server. I say secondary because the storage just added is on less spindles and slightly slower disks. The idea is to move less accessed data to the secondary storage. One on of my first moves involves the USERS tablespace. In this particular database the USERS tablespace is simple used to store temp type tables and is not used for anything “production”. Here are the steps I followed.

In SQLPlus:

alter tablespace USERS offline;

OS Level:

cd /u01/app/oracle/oradata/dw2

cp users01.dbf /storage/oradata/dw2/users01.dbf

Back in SQLPlus:

alter tablespace USERS
rename datafile /u01/app/oracle/oradata/dw2/users01.dbf
to /storage/oradata/dw2/users01.dbf;

alter tablespace USERS online;