数据库迁移(表空间)

--standby數據庫
SQL> startup nomount;
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish skip standby logfile;
SQL> alter database commit to switchover to primary with session shutdown;
SQL> shutdown immediate;
SQL> startup
1.源數據庫

--查看用戶涉及的表空間
select distinct TABLESPACE_NAME from dba_segments where owner ='MES_ADMIN';
TABLESPACE_NAME
------------------------------
MES_DATA_TS
MES_IDX_TS
---check 表空間self-including
set termout off
set echo off
set feedback off
set heading off
set verify off
--pagesize 0用?去除空行
set pagesize 0
set trimspool on
set trimout on
set colsep |
SET linesize 1000
spool c:\check_log.txt
execute DBMS_TTS.TRANSPORT_SET_CHECK ('MES_DATA_TS',TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
spool off
--查看用戶下的表及索引的表空間，進行移轉統一
select * from user_tables where TABLESPACE_NAME <>'MES_DATA_TS';
alter table TEST move tablespace MES_DATA_TS;
select * from user_indexes where TABLESPACE_NAME <>'MES_IDX_TS';
alter index idx_test move tablespace MES_IDX_TS;
--查看數據庫的默認表空間
select * from DATABASE_PROPERTIES where property_name like 'DEFAU%' ;
--修改數據庫的默認表空間
alter database default tablespace sysaux;
--導出meta
exp sys/oracle@bstest as sysdba file=C:\meta.dmp log=c:\exp_meta.log owner=mes_admin rows=n;
--查看表空間與datafile
select tablespace_name,FILE_NAME from dba_data_files
where tablespace_name in ('MES_DATA_TS','MES_IDX_TS')
order by tablespace_name;
--設定表空間狀態為唯讀
alter tablespace MES_DATA_TS read only;
alter tablespace MES_IDX_TS read only;
--導出表空間
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
exp tablespaces=MES_DATA_TS,MES_IDX_TS transport_tablespace=y file=C:\alltb.dmp log=C:\exp_tb.log
--關閉數據庫
shutdown immediate
--copy datafile到目的數據庫
mv or scp

2.目的數據庫

--修改數據庫的默認表空間
alter database default tablespace sysaux;
--創建用戶及付權限
CREATE USER mes_admin
IDENTIFIED BY mesadmin
--DEFAULT TABLESPACE MES_DATA_TS
--TEMPORARY TABLESPACE TEMP
/
GRANT UNLIMITED TABLESPACE TO mes_admin
/
GRANT CONNECT TO mes_admin
/
GRANT DBA TO mes_admin
/
GRANT EXP_FULL_DATABASE TO mes_admin
/
GRANT IMP_FULL_DATABASE TO mes_admin
/
GRANT RESOURCE TO mes_admin
/
ALTER USER mes_admin DEFAULT ROLE ALL
/
--導入表空間
imp mes_admin/mesadmin@demo file=e:\oratmp\alltb.dmp log=e:\oratmp\imp_tb.log parfile=e:\oratmp\imptb.par
$tablespaces=MES_DATA_TS,MES_IDX_TS transport_tablespace=y datafiles='E:\oracle\product\10.2.0\oradata\demo\TEST01.DBF','E:\oracle\product\10.2.0\oradata\demo\TEST_IDX_01.DBF'
--查看datafile狀態
select name, status, plugged_in from v$datafile;
--變更表空間狀態
alter tablespace MES_DATA_TS read write;
alter tablespace MES_IDX_TS read write;
--

編譯
@?/rdbms/admin/utlrp.sql
--導入meta ,用mes_admin導入
imp file=e:\oratmp\meta.dmp log=e:\oratmp\imp_meta.log full=y fromuser=mes_admin touser=mes_admin

--****************參考命令*************************************************************************************************************************************************************

--查看默認表空間
select * from DATABASE_PROPERTIES where property_name like 'DEFAU%' ;
--修改默認表空間
alter database default tablespace SYSAUX;
--刪除用戶
drop user mes_admin cascade ;
--刪除表空間
drop tablespace MES_DATA_TS including contents and datafiles;
drop tablespace MES_IDX_TS including contents and datafiles;
--新增表空間
CREATE TABLESPACE MES_DATA_TS DATAFILE
'E:\oracle\product\10.2.0\oradata\demo\MES_DATA_TS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
CREATE TABLESPACE MES_IDX_TS DATAFILE
'E:\oracle\product\10.2.0\oradata\demo\MES_IDX_TS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;


--*******************************************************************************************************************