内容简介:目的:之前的7个用户默认表空间为user,要将7个用户中的数据转移到新的表空间,并将默认表空间修改为新建的七个表空间。测试库IP 10.66.223.140实例名 tbomnew
目的:之前的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' 不存在
以上所述就是小编给大家介绍的《数据泵重建用户》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
PHP经典实例(第3版)
David Sklar、Adam Trachtenberg / 苏金国、丁小峰 / 中国电力出版社 / 2015-7 / 128.00
想要掌握PHP编程技术?或者想要学习如何完成一个特定的任务?那么一定要先看看《PHP经典实例(第3版)》。本书介绍了专门为PHP 5.4和5.5修订的350个经典技巧,并提供了丰富的示例代码。特别是对生成动态Web内容的解决方案做了全面更新,从使用基本数据类型到查询数据库,从调用RESTful API到测试和保护网站安全都有涵盖。 各个技巧都提供了示例代码,可以免费使用,另外还讨论了如何解决......一起来看看 《PHP经典实例(第3版)》 这本书的介绍吧!