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:
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>
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:
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>
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
