Skip to main content

Search

Welcome to Tibero GTS!

How to Change Datafile Name and Path When Tablespace is Offline

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

 

 

 

Related to