Document Type | Technical Information
Category | Installation
Document Number | TINTI009
Overview
This guide explains how to change the name or path of a datafile without restarting the database.
This operation involves changing the tablespace containing the target datafile to an offline state, then executing the
RENAME DATAFILE command to change the datafile's name or path.Note that the tablespace must be non-SYSTEM to be changed to an offline state.
After this operation, since the datafile information is changed, it is essential to back up the control file.
Method
Proceed with the change operation as outlined in the steps below.
1. Check Datafile and Tablespace
SQL> set linesize 200 SQL> col file_name for a40 SQL> col tablespace_name for a20 SQL> select file_name, tablespace_name from dba_datafiles where file_name like '%test0_.dtf'; FILE_NAME TABLESPACE_NAME ---------------------------------------- -------------------- /home/lcj/tbdata/cjdb1/test01.dtf TEST /home/lcj/tbdata/cjdb1/test02.dtf TEST /home/lcj/tbdata/cjdb1/test03.dtf TEST 3 rows selected.
2. Change Target Tablespace to Offline
SQL> alter tablespace TEST offline; Tablespace 'TEST' altered. SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'TEST'; TABLESPACE_NAME STATUS -------------------- ------------- TEST OFFLINE 1 row selected.
3. Change Path of Target Datafile
[lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]ls test01.dtf test01.dtf [lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cp test01.dtf /home/lcj/tbdata/cjdb1/rename/test01.dtf [lcj@finlin:cj1:/home/lcj/tbdata/cjdb1]cd rename [lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ls test01.dtf test01.dtf
4. Rename Datafile Command
SQL> alter tablespace TEST rename datafile '/home/lcj/tbdata/cjdb1/test01.dtf' TO '/home/lcj/tbdata/cjdb1/rename/test01.dtf'; Tablespace 'TEST' altered.
5. Change Target Tablespace Back to Online
SQL> alter tablespace TEST online; Tablespace 'TEST' altered.
6. Verify Operation and Backup Controlfile
SQL> select file_name, tablespace_name from dba_datafiles where tablespace_name='TEST'; FILE_NAME TABLESPACE_NAME -------------------------------------------- -------------------- /home/lcj/tbdata/cjdb1/rename/test01.dtf TEST /home/lcj/tbdata/cjdb1/test02.dtf TEST /home/lcj/tbdata/cjdb1/test03.dtf TEST 3 rows selected. SQL> alter database backup controlfile to trace as '/home/lcj/tbdata/cjdb1/rename/cre_ctl_1.sql' reuse noresetlogs; Database altered. [lcj@finlin:cj1:/home/lcj/tbdata/cjdb1/rename]ls -alrt total 512016 drwxr-xr-x 4 lcj dba 4096 Dec 4 20:17 .. -rw------- 1 lcj dba 524288000 Dec 4 20:26 test01.dtf drwxr-xr-x 2 lcj dba 4096 Dec 4 20:35 . -rw-r--r-- 1 lcj dba 1554 Dec 4 20:35 cre_ctl_1.sql