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@]