Oracle 移表空間或資料檔

 

select 'alter ' || segment_type || ' ' || segment_name ||
       decode(segment_type,
              'TABLE',
              ' MOVE ',
              'INDEX',
              ' REBUILD ',
              ' OTHERS:cluster?mv?,please define by your self') ||
       ' tablespace ' ||
       decode(segment_type, 'TABLE', 'TABLESPACE_TAB;', 'INDEX', 'TABLESPACE_IDX;')
  from user_segments;

 

$ sqlplus '/ as sysdba'
-- 正常關閉
shutdown immediate
-- 正常關閉很慢,採用強制關閉
shutdown abort
-- Mount Start
startup mount;


-- 查看狀態
select status from v$instance;

-- linux 
$ mv /u01/ewf/ewf_lobd1.dbf /u02/ewf/ewf_lobd1.dbf
$ mv /u01/ewf/ewf_lobd2.dbf /u02/ewf/ewf_lobd2.dbf
$ mv /u01/ewf/ewf_lobd3.dbf /u02/ewf/ewf_lobd3.dbf
-- Renamed the location
alter database rename file '/u01/ewf/ewf_lobd1.dbf' to '/u02/ewf/ewf_lobd1.dbf' ;
alter database rename file '/u01/ewf/ewf_lobd2.dbf' to '/u02/ewf/ewf_lobd2.dbf' ;
alter database rename file '/u01/ewf/ewf_lobd3.dbf' to '/u02/ewf/ewf_lobd3.dbf' ;

--
set autorecovery on;
alter database open;

--  see the results after the change
select file_name from dba_data_files;