oracle如何移动datafile的位置

oracle如何移动datafile的位置?

[root@sc ~]# su - oracle

[oracle@sc ~]$ sqlplus / as sysdba

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /recovery/arch

Oldest online log sequence     85

Next log sequence to archive   87

Current log sequence           87

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/ocp/system01.dbf

/u01/app/oracle/oradata/ocp/undotbs01.dbf

/u01/app/oracle/oradata/ocp/sysaux01.dbf

/u01/app/oracle/oradata/ocp/users01.dbf

/u01/app/oracle/oradata/ocp/example01.dbf

/u01/app/oracle/oradata/ocp/perstat.dbf

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

PERSTAT                        ONLINE

第一步,将表空间offline

SQL> alter tablespace perstat offline;-- 如果是多个文件，则会同时变成offline

Tablespace altered.

自己手动拷贝文件到新的目录，并使用rename修改数据文件的地址属性, 可以同时移动 一 个表空间下的多个数据库文件

[oracle@sc ~]$ cp /u01/app/oracle/oradata/ocp/perstat.dbf /u01/app/oracle/oradata/perstat.dbf

SQL> alter tablespace perstat rename datafile '/u01/app/oracle/oradata/ocp/perstat.dbf' to '/u01/app/oracle/oradata/perstat.dbf';

Tablespace altered.

将表空间online

SQL> alter tablespace perstat online;

Tablespace altered.

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/ocp/system01.dbf

/u01/app/oracle/oradata/ocp/undotbs01.dbf

/u01/app/oracle/oradata/ocp/sysaux01.dbf

/u01/app/oracle/oradata/ocp/users01.dbf

/u01/app/oracle/oradata/ocp/example01.dbf

/u01/app/oracle/oradata/perstat.dbf