数据泵重建用户

目的：之前的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';

 

 

遇到的问题：