Square DBA - Oracle DBA Talk

Moving tables to a new tablespace

Need to move a table to a new tablespace?  That is just what I needed to do today.  I have a couple if hot datafiles that I need to relieve the load on.  Basically I have one staging tablespace for loading and manipulating data beforethe data is moved to the production tables.  I have several larger jobs that are now utilizing this tablespace.  There are two problems with my current setup.  Multiple jobs are hitting the tablespace at the same time and the tablespace is on a single RAID group.  This particualr server has two RAID groups so I created multiple stagin tablespaces (one for each job).  I also split the datafiles for these new tablespace across both RAID groups.  Move the tables was easy.

For my non-partitioned tables I used:

ALTER TABLE O1DW.STG_OSCAR_CALLPROC
MOVE TABLESPACE STG_CSE;

and for my partitioned tables I used:

ALTER TABLE O1DW.STG_OSCAR
MOVE PARTITION STOP_EVENT TABLESPACE STG_CSE; 

Moving an Oracle tablespace

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;