Transfer of oracle Data Files & quot; *. dbf & quot;

Transfer An oracle Data File "*. dbf" Transfer Process:
1. log on to sqlplus: C:/Documents and Settings/jbdu> sqlplus "/as sysdba" 2. change the tablespace to Offline: www.2cto.com SQL> alter tablespace users offline; 3. copy tablespace file copy D:/oracle/product/10.2.0/oradata/orclado/USERS01.DBF to H:/oracle/product/10.2.0/oradata/orclado/USERS01.DBF 4. modify the oracle tablespace pointing to the address SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/orclado/USERS01.DBF 'to' H: /oracle/product/10.2.0/oradata/orclado/users01.dbf'; 5. change the tablespace to Online SQL> alter tablespace users online. The details are as follows:
1. view the tablespace: select tablespace_name from dba_tablespaces; obtain the tablespace name. 2. Only some tablespaces can be transferred in this way, and system, temp, sysaux, and other tablespaces can be transferred in 3rd ways. Alter tablespace users offline; alter tablespace EXAMPLE offline; alter tablespace into _ts offline; alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/EXAMPLE01.DBF 'to' H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/EXAMPLE01.DBF '; www.2cto.com alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/USERS01.DBF' to 'H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/USERS01.DBF '; alter tablespace users online; alter tablespace EXAMPLE online; alter tablespace into _ts online;
3. For system, temp, undotbs1, sysaux, and other tablespaces, first shut down the database, then start to the mount state, and then modify the data file name. Shutdown immediate; startup mount; alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/SYSAUX01.DBF 'to' H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSAUX01.DBF '; alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/SYSTEM01.DBF' to 'H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/SYSTEM01.DBF '; alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/TEMP01.DBF' to 'H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEMP01.DBF '; alter database rename file 'd:/ORACLE/PRODUCT/10.2.0/ORADATA/orcl/UNDOTBS01.DBF' to 'H: /ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/UNDOTBS01.DBF '; note that if an error occurs www.2cto.com ora-01113ora-01110, execute recover datafile' H:/ORACLE/PRODUCT/10.2. /ORADATA/ORCL/UNDOTBS01.DBF '; alter database open; then execute SQL> alter tablespace users/example /... online; www.2cto.com % alter tablespace SYSTEM offline; % alter tablespace UNDOTBS1 offline; % alter tablespace SYSAUX offline; % alter tablespace TEMP offline; alter tablespace SYSTEM online; alter tablespace UNDOTBS1 online; alter tablespace SYSAUX online; alter tablespace TEMP online; 4. query data file name: select file_name from sys. dba_data_files; Author: zhywjw