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