Saturday, March 10, 2012

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>

No comments: