Oracle的exp、imp的數據遷移步驟

把AA數據庫遷移到BB數據庫
1、AA數據庫查看用戶的默認表空間
select username,default_tablespace from dba_users where username='AA_Username'

2、查看用戶的角色
select * from user_role_privs;

3、BB數據庫的數據文件所在位置

SQL> select name from v$datafile;

NAME                                                                           
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\SYSTEM01.DBF                            
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\UNDOTBS01.DBF                           
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\SYSAUX01.DBF                            
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\USERS01.DBF                             
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\EXAMPLE01.DBF                           

4、BB數據庫創建用戶使用的默認表空間，數據文件自動擴展
SQL> create tablespace BB_default_space
  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\CCOMS\BB_default_space.DBF'
  3  size 5M autoextend on;

表空間已創建。

5、BB數據庫創建新用戶
SQL> create user BB_Username
  2  identified by BB_Username000
  3  default tablespace AA_default_space;

用戶已創建。

6、BB數據庫授權給新用戶
SQL> grant connect,resource,dba to BB_Username;

授權成功。

7、BB數據庫配置listener.ora 和 tnsnames.ora
配置ip，以便外面可以訪問
listener.ora文件：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
    )
  )

tnsnames.ora文件：
BB_Username =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BB_Username)
    )
  )

  修改監聽器配置文件，需要重新啟動監聽器
  關閉監聽器：lsnrctl stop
  重啟監聽器：lsnrctl start

8、在另一個數據庫（配置方便使用plsql）配置tnsnames。ora
 BB_Username =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = BB_IP)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BB_Username)
    )
  )

9、在AA數據庫查詢用戶下都用把數據放到哪些表空間中，建立相應的表空間

select distinct tablespace_name from user_segments
where tablespace_name not in ('USERS','USERS02')

10、生成下面的腳本，用plsql導入到BB數據庫中
CREATE TABLESPACE DRILL_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\TEST_LOB_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE DH_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE TEST_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\TEST_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE LOG_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\LOG_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE MLOG_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\MLOG_NORM_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE MLOG_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\MLOG_LOB_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE LOG_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\LOG_NORM_SPACE.DBF' size 5M autoextend on;
CREATE TABLESPACE HHH_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHH_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE GPE_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHDS_NORM_SPACE DATAFIL.DBF' size 5M autoextend on;
CREATE TABLESPACE HHDS_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\HHDS_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE PUB_NORM_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\PUB_NORM_SPACE DATAFILE.DBF' size 5M autoextend on;
CREATE TABLESPACE PUB_LOB_SPACE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\BB_Username\BBII_LOB_SPACE DATAFILE.DBF' size 5M autoextend on;

11、導出AA數據庫的數據
exp AA_Username/AA_Password@AA_Database file=F:\AA_Databaseoraclebak\AA_Database14.dump log=F:\AA_Databaseoraclebak\AA_Database14.log

12、導入BB數據庫的數據:不需要用戶授權權限

imp BB_Username/BB_Password@BB_Database FROMUSER=AA_Username  TOUSER=BB_Username ignore=yes grants=no  file=F:\AA_Databaseoraclebak\AA_Database14.dump  log=F:\AA_Databaseoraclebak\IMP20100121BB_Username.log