数据泵重建用户
source link: http://www.cnblogs.com/houzhiheng/p/12726392.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
目的:之前的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' 不存在
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK