oracle如何安装exp,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 file=F:\AA_Databaseoraclebak\AA_Database14.dump log=F:\AA_Databaseoraclebak\AA_Database14.log

12、导入BB数据库的数据:不需要用户授权权限

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