1、  使用pl/sql工具，查看ods_6002/metabase_6002/data_6002的用户创建语句

-- Create the user

create user METABASE_6002

  identified by ""

  default tablespace USERS

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to METABASE_6002 with admin option;

grant dba to METABASE_6002;

grant exp_full_database to METABASE_6002;

grant imp_full_database to METABASE_6002;

grant resource to METABASE_6002 with admin option;

-- Grant/Revoke system privileges

grant create view to METABASE_6002;

grant debug any procedure to METABASE_6002;

grant debug connect session to METABASE_6002;

grant select any table to METABASE_6002;

grant unlimited tablespace to METABASE_6002 with admin option;

-- Create the user

create user DATA_6002

  identified by ""

  default tablespace DATA_DATA

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to DATA_6002 with admin option;

grant dba to DATA_6002 with admin option;

grant exp_full_database to DATA_6002;

grant imp_full_database to DATA_6002;

grant resource to DATA_6002;

-- Grant/Revoke system privileges

grant create view to DATA_6002;

grant select any table to DATA_6002;

grant unlimited tablespace to DATA_6002 with admin option;

-- Create the user

create user ODS_6002

  identified by ""

  default tablespace ODS_DATA

  temporary tablespace TEMP

  profile DEFAULT

  password expire;

-- Grant/Revoke role privileges

grant connect to ODS_6002 with admin option;

grant dba to ODS_6002 with admin option;

grant exp_full_database to ODS_6002;

grant imp_full_database to ODS_6002;

grant resource to ODS_6002;

-- Grant/Revoke system privileges

grant create view to ODS_6002;

grant select any table to ODS_6002;

grant unlimited tablespace to ODS_6002 with admin option;

注意create user XXX identified by “填写密码”

2、  将要覆盖的用户ods_6002/metabase_6002/data_6002做备份

SQL> select instance_name from v$instance;--查询所在数据库实例

ccbi

SQL> select userenv('language') from dual;--查询数据库字符集为UTF8

SIMPLIFIED CHINESE_CHINA.AL32UTF8

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ exp metabase_6002/123456 file=metabase6002_0731.dmp owner=metabase_6002 log=metabase6002_0731.log

[oracle@NEWCORE ~]$ exp ods_6002/ods_6002 file=ods6002_0731.dmp owner=ods_6002 log=ods6002_0731.log

[oracle@NEWCORE ~]$  exp data_6002/data_6002 file=data6002_0731.dmp owner=data_6002  log=data6002_0731.log

3、  备份想要导入的数据库用户ods_6003/metabase_6003/data_6003

为了防止Oracle11g，空表不导出，需要登录导出的用户分别执行如下的SQL

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

SQL> conn metabase_6003/metabase_6003

Connected.

SQL> select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

把罗列出来的SQL执行复制出来，执行一遍。

注意：只执行alter table XXX allocate extent;的SQL语句

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$exp metabase_6003/metabase_6003 file=metabase6003_0731.dmp owner=metabase_6003 log=metabase6003_0731.log

[oracle@NEWCORE ~]$ exp ods_6003/ods_6003 file=ods6003_0731.dmp owner=ods_6003 log=ods6003_0731.log

[oracle@NEWCORE ~]$  exp data_6003/data_6003 file=data6003_0731.dmp owner=data_6003 log=data6003_0731.log

4、  删除ods_6002/metabase_6002/data_6002用户

确认数据库实例名

SQL> drop user metabase_6002 cascade;

SQL> drop user data_6002 cascade;

SQL> drop user ods_6002 cascade;

5、  创建用户ods_6002/metabase_6002/data_6002用户

         逐一登录创建的用户来验证，会提示密码过期重新设置，这时重新输入一遍原始密码即可。

或者创建用户的时候password  expire改为 account unlock就不需要用户重新登录修改密码。

6、  将数据覆盖到ods_6002/metabase_6002/data_6002用户之中

设置ORACLE_SID和字符集

[oracle@NEWCORE ~]$ export ORACLE_SID=ccbi

[oracle@NEWCORE ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@NEWCORE ~]$ imp metabase_6002/123456 file=metabase6003_0731.dmp fromuser=metabase_6003 touser=metabase_6002 log=imp_metabase6002.log

[oracle@NEWCORE ~]$ imp data_6002/data_6002 file=data6003_0731.dmp fromuser=data_6003 touser=data_6002 log=imp_data6002.log

[oracle@NEWCORE ~]$ imp ods_6002/ods_6002 file=ods6003_0731.dmp fromuser=ods_6003 touser=ods_6002 log=imp_ods002.log