目的：之前的7個使用者預設表空間為user，要將7個使用者中的資料轉移到新的表空間，並將預設表空間修改為新建的七個表空間。
測試庫IP 10.66.223.140
例項名 tbomnew
使用者名稱：CORE,CFGMGMT,CHGMGMT,BOMMGMT,CUST,MSTDATA,INTEGRATION
1、新建表空間,注意資料檔案位置
2、匯出資料庫使用者 sys密碼是oracle
3、匯入remap tablespace （單個匯入）
4、修改使用者預設表空間
5、修改使用者對錶空間許可權
grant unlimited tablespace to username;
匯入前檢查：
查詢當前例項
echo $ORACLE_SID
echo
匯入目標例項
export ORACLE_SID=bomdb
查詢資料字典
select * from dba_directories;
DATA_PUMP_DIR /oracle/admin/bomdb/dpdump/
查詢資料檔案位置
select file_name,tablespace_name from dba_data_files;
資料檔案位置： /oracle/oradata/BOMDB
檢視以上使用者是否存在
select username from dba_users order by created;
存在
查詢使用者所屬預設表空間
select default_tablespace from dba_users where username='CORE';
select default_tablespace from dba_users where username='CFGMGMT';
select default_tablespace from dba_users where username='CHGMGMT';
select default_tablespace from dba_users where username='BOMMGMT';
select default_tablespace from dba_users where username='CUST';
select default_tablespace from dba_users where username='MSTDATA';
select default_tablespace from dba_users where username='INTEGRATION';
USERS
匯出資料庫使用者 "'/ as sysdba'"
expdp "'sys/oracle as sysdba'" dumpfile=core.dmp directory=DATA_PUMP_DIR schemas=CORE logfile=20200417_core.logfile
expdp "'sys/oracle as sysdba'" dumpfile=cfgmgmt.dmp directory=DATA_PUMP_DIR schemas=CFGMGMT logfile=20200417_cfgmgmt.logfile
expdp "'sys/oracle as sysdba'" dumpfile=chgmgmt.dmp directory=DATA_PUMP_DIR schemas=CHGMGMT logfile=20200417_chgmgmt.logfile
expdp "'sys/oracle as sysdba'" dumpfile=bommgmt.dmp directory=DATA_PUMP_DIR schemas=BOMMGMT logfile=20200417_bommgmt.logfile
expdp "'sys/oracle as sysdba'" dumpfile=cust.dmp directory=DATA_PUMP_DIR schemas=CUST logfile=20200417_cust.logfile
expdp "'sys/oracle as sysdba'" dumpfile=mstdata.dmp directory=DATA_PUMP_DIR schemas=MSTDATA logfile=20200417_mstdata.logfile
expdp "'sys/oracle as sysdba'" dumpfile=integration.dmp directory=DATA_PUMP_DIR schemas=INTEGRATION logfile=20200417_integration.logfile
建立表空間
資料檔案掛錯盤了，刪掉重新建
drop tablespace YT_CORE including datafiles;
DROP TABLESPACE YT_CORE INCLUDING CONTENTS AND DATAFILES;
create tablespace YT_CORE datafile '/oracle/oradata/BOMDB/YT_CORE.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_CFGMGMT datafile '/oracle/oradata/BOMDB/YT_CFGMGMT.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_CHGMGMT datafile '/oracle/oradata/BOMDB/YT_CHGMGMT.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_BOMMGMT datafile '/oracle/oradata/BOMDB/YT_BOMMGMT.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_CUST datafile '/oracle/oradata/BOMDB/YT_CUST.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_MSTDATA datafile '/oracle/oradata/BOMDB/YT_MSTDATA.dbf' size 30G autoextend on next 500M maxsize unlimited
create tablespace YT_INTEGRATION datafile '/oracle/oradata/BOMDB/YT_INTEGRATION.dbf' size 30G autoextend on next 500M maxsize unlimited
刪除之前的使用者
drop user CORE cascade;
drop user CFGMGMT cascade;
drop user CHGMGMT cascade;
drop user BOMMGMT cascade;
drop user CUST cascade;
drop user MSTDATA cascade;
drop user INTEGRATION cascade;
如果被刪除使用者還在connected，停掉監聽。
重啟資料庫
刪除完之後啟監聽
匯入使用者
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=core.dmp REMAP_TABLESPACE=users:YT_CORE logfile=20200417_core.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=cfgmgmt.dmp REMAP_TABLESPACE=users:YT_CFGMGMT logfile=20200417_cfgmgmt.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=chgmgmt.dmp REMAP_TABLESPACE=users:YT_CHGMGMT logfile=20200417_chgmgmt.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=bommgmt.dmp REMAP_TABLESPACE=users:YT_BOMMGMT logfile=20200417_bommgmt.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=cust.dmp REMAP_TABLESPACE=users:YT_CUST logfile=20200417_cust.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=mstdata.dmp REMAP_TABLESPACE=users:YT_MSTDATA logfile=20200417_mstdata.logfile
impdp "'sys/oracle as sysdba'" directory=DATA_PUMP_DIR dumpfile=integration.dmp REMAP_TABLESPACE=users:YT_INTEGRATION logfile=20200417_integration.logfile
修改使用者預設表空間
alter user CORE default tablespace YT_CORE;
grant unlimited tablespace to core;
alter user CFGMGMT default tablespace YT_CFGMGMT;
grant unlimited tablespace to cfgmgmt;
alter user CHGMGMT default tablespace YT_CHGMGMT;
grant unlimited tablespace to chgmgmt;
alter user BOMMGMT default tablespace YT_BOMMGMT;
grant unlimited tablespace to bommgmt;
alter user CUST default tablespace YT_CUST;
grant unlimited tablespace to cust;
alter user MSTDATA default tablespace YT_MSTDATA;
grant unlimited tablespace to mstdata;
alter user INTEGRATION default tablespace YT_INTEGRATION;
grant unlimited tablespace to integration;
檢查當前使用者的預設表空間
select default_tablespace from dba_users where username='CORE';
select default_tablespace from dba_users where username='CFGMGMT';
select default_tablespace from dba_users where username='CHGMGMT';
select default_tablespace from dba_users where username='BOMMGMT';
select default_tablespace from dba_users where username='CUST';
select default_tablespace from dba_users where username='MSTDATA';
select default_tablespace from dba_users where username='INTEGRATION';
遇到的問題：
因為經驗不足，單個匯入的，所以導致了報錯，ORA-39083 ，因為逐個恢復的使用者資料，在其他地方沒有恢復的使用者有關聯許可權以及約束，後期可以直接逐個執行一遍。
ORA-39083: 物件型別 DEFAULT_ROLE 建立失敗, 出現錯誤:
ORA-01917: 角色 'BOMMGMT' 不存在
