How to Move an Oracle Datafile

This worked example shows how to move a datafile. First create a tablespace:

SQL> create tablespace andrew

  2  datafile '/usr/users/oracle/andrew/file1'

  3  size 1m;

 

Tablespace created.

 

SQL>

Create a table in the tablespace then describe it and count the rows. This is only to check that the contents of the tablespace are OK after the datafile has been moved:

 

SQL> create table andrews_table

  2  tablespace andrew

  3  as select * from dba_objects;

 

Table created.

 

SQL> desc andrews_table

Name                       Null?    Type

-------------------------- -------- ------------------

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

Note the name(s) of the tablespace's datafile(s).

SQL> select file_name from dba_data_files

  2  where tablespace_name = 'ANDREW';

 

FILE_NAME

-------------------------------------------------------

/usr/users/oracle/andrew/file1

SQL>

Take the tablespace offline:

 

SQL> alter tablespace andrew offline;

 

Tablespace altered.

 

SQL>

Copy the file to its new location in the operating system. Normally you would be doing this to move the datafile(s) to a different disk but this is only an example on a test database so I will just copy the file within the same directory: 

UNIX /usr/users/oracle/andrew >cp file1 file2

UNIX /usr/users/oracle/andrew >

Rename the datafile at the Oracle level in SQL*Plus:

 

  1  alter tablespace andrew rename datafile

  2  '/usr/users/oracle/andrew/file1'

  3  to

  4* '/usr/users/oracle/andrew/file2'

SQL> /

 

Tablespace altered.

SQL>

Put the tablespace online again:

 

SQL> alter tablespace andrew online;

 

Tablespace altered.

 

SQL>

See that the new file name appears in dba_data_files: 

SQL> l

  1  select file_name from dba_data_files

  2* where tablespace_name = 'ANDREW'

SQL> /

 

FILE_NAME

-------------------------------------------------------

/usr/users/oracle/andrew/file2

 

SQL>

Check that the table created above is still in the tablespace, describe it and count the rows again then compare the figures with those above:

SQL> select table_name from dba_tables

  2  where tablespace_name = 'ANDREW';

 

TABLE_NAME

------------------------------

ANDREWS_TABLE

 

SQL> desc andrews_table

Name                       Null?    Type

-------------------------- -------- ------------------

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(128)

SUBOBJECT_NAME                      VARCHAR2(30)

OBJECT_ID                           NUMBER

DATA_OBJECT_ID                      NUMBER

OBJECT_TYPE                         VARCHAR2(18)

CREATED                             DATE

LAST_DDL_TIME                       DATE

TIMESTAMP                           VARCHAR2(19)

STATUS                              VARCHAR2(7)

TEMPORARY                           VARCHAR2(1)

GENERATED                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

 

SQL> select count(*) from andrews_table;

 

  COUNT(*)

----------

      7933

 

SQL>

You cannot use this method for system, undo or temporary tablespaces as you cannot take them offline. I will cover them in a future post:

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

 

SQL> alter tablespace undo_1 offline;

alter tablespace undo_1 offline

*

ERROR at line 1:

ORA-30042: Cannot offline the undo tablespace

 

SQL> alter tablespace temp offline;

alter tablespace temp offline

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY

TABLESPACE

 

SQL>