oracle目录移动,Oracle 数据文件移动步骤

ORACLE数据文件移动步骤

一、现创建好目标路径

二、关闭数据库和监听(该库里面只有一个用户表空间为sms所以使用tablespace offline和关闭数据库的方法所受影响的时间是一样的)

三、cp源文件到目标路径

四、startup mount pfile=$ORACLE_HOME/dbs/initsid.ora 然后使用下面语句更新控制文件的信息(如果控制的文件也要移动的话那么更改spfile文件在mount之前)

SQL> select 'alter database rename file '''||file_name||''' to '''||file_name||''' ;' from dba_data_files;

alter database rename file '/data/oradata/caitong/users01.dbf' to '/data1/oradata/caitong/users01.dbf';

alter database rename file '/data/oradata/caitong/sysaux01.dbf' to '/data1/oradata/caitong/sysaux01.dbf';

alter database rename file '/data/oradata/caitong/undotbs01.dbf' to '/data1/oradata/caitong/undotbs01.dbf';

alter database rename file '/data/oradata/caitong/system01.dbf' to '/data1/oradata/caitong/system01.dbf';

alter database rename file '/data/oradata/caitong/sms.dbf' to '/data1/oradata/caitong/sms.dbf';

alter database rename file '/data/oradata/caitong/sms2.dbf' to '/data1/oradata/caitong/sms2.dbf';

alter database rename file '/data/oradata/caitong/sms3.dbf' to '/data1/oradata/caitong/sms3.dbf';

alter database rename file '/data/oradata/caitong/sms4.dbf' to '/data1/oradata/caitong/sms4.dbf';

五、create spfile from pfile

六、shutdown immediate

七、startup

八、select group#,status from v$log

九、alter database drop logfile group 1;

十、alter database add logfiel group 1('$PATH/redo01.log') size 100m

十、select * from database_properties; --查看临时表空间

十一、create temporary tablespace new_temp tempfile '$PATH/temp02.dbf' size 2g autoextend off;

十二、alter database default temporary tablespace new_temp;

十三、drop tablespace old_temp including contents and datafiles;

备注：更改用户默认表空间

alter user username temporary tablespace new_temp[@more@]