数据泵：重建用户
目的：之前的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' 不存在
