oracle数据文件"*.dbf"的转移 - 数据库编程

转移oracle数据文件"*.dbf"

转移过程：

1.先登录sqlplus：?

C:/Documents and Settings/jbdu>sqlplus “/as sysdba”

2.修改表空间为Offline： ?www.2cto.com ??

SQL> alter tablespace users offline;?

3.拷贝表空间文件?

拷贝?

D:/oracle/product/10.2.0/oradata/orclado/USERS01.DBF 到?

H:/oracle/product/10.2.0/oradata/orclado/USERS01.DBF?

4.修改oracle表空间指向地址?

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.修改表空间为Online?

SQL> alter tablespace users online;?

具体如下：

1、查看表空间：

select tablespace_name from dba_tablespaces;?

获知表空间的名称。

2、只有部分表空间能按照此方式转移，system、temp、sysaux等表空间按照第3种方式进行转移。

alter tablespace users offline;?

alter tablespace EXAMPLE offline;?

alter tablespace CMD_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 CMD_TS online;?

3.对于system、temp、undotbs1、sysaux等表空间，先关闭数据库，然后启动到mount状态，再修改数据文件名称。

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';

注意如果报错出现 ?www.2cto.com ?

ora-01113

ora-01110

就执行

recover datafile 'H:/ORACLE/PRODUCT/10.2./ORADATA/ORCL/UNDOTBS01.DBF';

alter database open;

再执行

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.查询数据文件名称：

select file_name from sys.dba_data_files;

作者 zhywjw