數據泵重建用戶
目的：之前的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’ 不存在
