Moving datafiles,control files and log files-1

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can't take SYSTEM tablespace offline)

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Well lets try moving USERS tablespace.

SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

SUBSTR(FILE_NAME,1,50)                             TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\ORCL1\USERS01.DBF                USERS

SQL>

The current location of the datafile is c:\oracle\oradata\orcl1. Suppose I have to move it to c:\oracle\oradata. First take the tablespace offline

SQL> alter tablespace USERS offline;

Tablespace altered.

SQL>

Now copy the datafile to the new location.

C:\oracle\oradata\orcl1>copy USERS01.DBF c:\oracle\oradata\USERS01.DBF
1 file(s) copied.

C:\oracle\oradata\orcl1>

Now make database aware of the new location of the datafile using alter database rename file.

SQL> alter database rename file 'C:\ORACLE\ORADATA\ORCL1\USERS01.DBF' to 'C:\ORACLE\ORADATA\USERS01.DBF';

Database altered.

SQL>

The last thing is the bring back the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select substr(file_name,1,50),tablespace_name from dba_data_files where tablespace_name='USERS';

SUBSTR(FILE_NAME,1,50)                             TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\USERS01.DBF                      USERS

SQL>

In next post we will discuss moving datafiles, controlfiles and logfiles.

Sidhu