Kongzz' blog

* 시스템 Tablespace(system, sysaux,undotbs)는 startup mount 상태에서 해야함

* 일반 Tablespace는 open 상태에서....

SQL> select file#, status, name from v$datafile;

     FILE# STATUS  NAME

---------- ------- ----------------------------------------

         1 SYSTEM  C:\ORADATA\ORCL\SYSTEM01.DBF

         2 ONLINE  C:\ORADATA\ORCL\SYSAUX01.DBF

         3 ONLINE  C:\ORADATA\ORCL\UNDOTBS01.DBF

         4 ONLINE  C:\ORADATA\ORCL\USERS01.DBF

         5 ONLINE  C:\ORADATA\DATAFILE\EMR01.DBF

         6 ONLINE  C:\ORADATA\DATAFILE\EMR02.DBF

6 개의 행이 선택되었습니다.

SQL> SELECT tablespace_name,  file_name,  autoextensible,  file_id

  2    FROM sys.dba_data_files;

TABLESPACE_NAME FILE_NAME                                AUT    FILE_ID

--------------- ---------------------------------------- --- ----------

SYSTEM          C:\ORADATA\ORCL\SYSTEM01.DBF             YES          1

SYSAUX          C:\ORADATA\ORCL\SYSAUX01.DBF             YES          2

UNDOTBS1        C:\ORADATA\ORCL\UNDOTBS01.DBF            YES          3

USERS           C:\ORADATA\ORCL\USERS01.DBF              YES          4

EMR_DB          C:\ORADATA\DATAFILE\EMR01.DBF            NO           5

EMR_DB          C:\ORADATA\DATAFILE\EMR02.DBF            NO           6

6 개의 행이 선택되었습니다.

SQL> alter tablespace emr_db offline;

테이블스페이스가 변경되었습니다.

SQL> host ocopy c:\oradata\datafile\emr* c:\oradata\orcldatafile

c:\oradata\datafile\EMR01.DBF

c:\oradata\datafile\EMR02.DBF

        2개 파일이 복사되었습니다.

SQL> alter database rename file 'c:\oradata\datafile\emr01.dbf' to 'c:\oradata\orcldatafile\emr01.dbf';

데이타베이스가 변경되었습니다.

SQL> alter database rename file 'c:\oradata\datafile\emr02.dbf' to 'c:\oradata\orcldatafile\emr02.dbf';

데이타베이스가 변경되었습니다.

SQL> alter tablespace emr_db online;

테이블스페이스가 변경되었습니다.

SQL> select file#, status, name from v$datafile;

     FILE# STATUS  NAME

---------- ------- ----------------------------------------

         1 SYSTEM  C:\ORADATA\ORCL\SYSTEM01.DBF

         2 ONLINE  C:\ORADATA\ORCL\SYSAUX01.DBF

         3 ONLINE  C:\ORADATA\ORCL\UNDOTBS01.DBF

         4 ONLINE  C:\ORADATA\ORCL\USERS01.DBF

         5 ONLINE  C:\ORADATA\ORCLDATAFILE\EMR01.DBF

         6 ONLINE  C:\ORADATA\ORCLDATAFILE\EMR02.DBF

6 개의 행이 선택되었습니다.

SQL> SELECT tablespace_name, file_name, autoextensible, file_id from dba_data_files;

TABLESPACE_NAME FILE_NAME                                AUT    FILE_ID

--------------- ---------------------------------------- --- ----------

SYSTEM          C:\ORADATA\ORCL\SYSTEM01.DBF             YES          1

SYSAUX          C:\ORADATA\ORCL\SYSAUX01.DBF             YES          2

UNDOTBS1        C:\ORADATA\ORCL\UNDOTBS01.DBF            YES          3

USERS           C:\ORADATA\ORCL\USERS01.DBF              YES          4

EMR_DB          C:\ORADATA\ORCLDATAFILE\EMR01.DBF        NO           5

EMR_DB          C:\ORADATA\ORCLDATAFILE\EMR02.DBF        NO           6

6 개의 행이 선택되었습니다.

SQL>shutdown immediate

SQL>startup mount

c:\>ocopy ~~~~

SQL>alter database rename file ....