Oracle DBF : data file의 이동 방법
data file의 위치나 이름을 변경하고자 할 경우에 대하여 살쳐보자
Data file 이동 방법 두 종류
| ALTER TABLESPACE 문 | 이동하고자 하는 tablespace를 offline으로 변경한 다음 복사 이동 후 tablespace를 online시킴 |
|---|
| ALTER DEATABASE 문 | 이동하고자 하는 file을 복사하기 전 반드시 DB 종료 후 복사 이동 후 DATABASE를 OPEN 시킴 |
| 1)ALTER TABLESPACE 문을 사용하여 이동하는 경우 |
|
【형식】
ALTER TABLESPACE <tablespace명>
RENAME DATAFILE '기존 file경로와 이름' [,'file명',...]
TO '이동하고자하는 file경로와 이름' [,'file명'...];
이동 순서
a) TABLESPACE를 OFFLINE 상태로 전환한다.
b) 운영체제 상에서 data file을 이동하거나 복사한다.
c) ALTER TABLESPACE ... RENAME DATAFILE ... TO ...문을 실행한다.
d) OFFLINE의 TABLESPACE를 onLINE 상태로 전환한다.
【예제】
SQL> sqlplus '/as sysdba'
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL> CREATE TABLESPACE users2
2 DATAFILE '/export/home/oracle/app/oracle/oradata/orcl/users201.dbf' SIZE 300k;
Tablespace created.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
USERS2 /export/home/oracle/app/oracle/oradata/orcl/users201.dbf
SQL> ALTER TABLESPACE users2 OFFLINE;
Tablespace altered.
SQL> SELECT tablespace_name,status FROM DBA_TABLESPACES;
TABLESPACE STATUS
---------- ---------
SYSTEM onLINE
UNDOTBS1 onLINE
SYSAUX onLINE
TEMP onLINE
USERS onLINE
USERS2 OFFLINE
6 rows selected.
SQL> !
$ mv /export/home/oracle/app/oracle/oradata/orcl/users201.dbf \
> /export/home/oracle/app/oracle/oradata/user201.dbf
$ exit
SQL> ALTER TABLESPACE users2
2 RENAME DATAFILE '/export/home/oracle/app/oracle/oradata/orcl/users201.dbf'
3 TO '/export/home/oracle/app/oracle/oradata/user201.dbf';
Tablespace altered.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
USERS2 /export/home/oracle/app/oracle/oradata/user201.dbf
SQL> ALTER TABLESPACE users2 onLINE;
Tablespace altered.
SQL> SELECT tablespace_name,status FROM DBA_TABLESPACES;
TABLESPACE STATUS
---------- ---------
SYSTEM onLINE
UNDOTBS1 onLINE
SYSAUX onLINE
TEMP onLINE
USERS onLINE
USERS2 onLINE
6 rows selected.
SQL>
| 2)ALTER DATABASE 문을 사용하여 이동하는 경우 |
|
【형식】
ALTER DATABASE [database명]
RENAME FILE '기존 file경로와 이름' [,'file명',...]
TO '이동하고자하는 file경로와 이름' [,'file명'...];
이동 순서
a) 반드시 DATABASE를 종료한다.
b) 운영체제 상에서 data file을 이동하거나 복사한다.
c) DATABASE를 MOUNT한다.
d) ALTER DATABASE RENAME FILE...TO...문을 실행한다.
e) DATABASE를 OPEN한다.
【예제】
SQL> sqlplus '/as sysdba'
SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL> CREATE TABLESPACE test
2 DATAFILE '/export/home/oracle/app/oracle/oradata/orcl/test01.dbf' SIZE 300K;
Tablespace created.
SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
TEST /export/home/oracle/app/oracle/oradata/orcl/test01.dbf
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
$ mv /export/home/oracle/app/oracle/oradata/orcl/test01.dbf \
> /export/home/oracle/app/oracle/oradata/test02.dbf
$ exit
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 778796 bytes
Variable Size 99360212 bytes
Database Buffers 188743680 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE
2 '/export/home/oracle/app/oracle/oradata/orcl/test01.dbf'
3 TO '/export/home/oracle/app/oracle/oradata/test02.dbf';
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
TEST /export/home/oracle/app/oracle/oradata/test02.dbf
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> SELECT tablespace_name,file_name FROM dba_data_files;
TABLESPACE FILE_NAME
---------- ------------------------------------------------------------
USERS /export/home/oracle/app/oracle/oradata/orcl/users01.dbf
SYSAUX /export/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
UNDOTBS1 /export/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
SYSTEM /export/home/oracle/app/oracle/oradata/orcl/system01.dbf
SQL>
http://radiocom.kunsan.ac.kr/lecture/oracle/statement_alter/alter_tablespace_rename_datafile_to.html