当前位置:操作系统 > Unix/Linux >>

用expdp/impdp实现将数据从一个数据库导入另一个数据库

用expdp/impdp实现将数据从一个数据库导入另一个数据库
 
1、创建DIRECTORY (必须在数据库用户下创建目录)
 
Sql代码   
sqlplus system/manager  
  
create directory test_dir as '/home/orauat/zzj';  
 
2、授权
 
Sql代码   
Grant read,write on directory test_dir to cux;  
 
--查看目录及权限
Sql代码   
SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH  
  FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D  
 WHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME  
 ORDER BY 2, 1;  
 
3、执行导出导入
 
Sql代码   
su - orauat/orauat  
  
expdp cux/cux TABLES=cux_expdp_test_table DUMPFILE=cux_expdp_test_table.dmp DIRECTORY=test_dir LOGFILE=cux_expdp_test_table.log  
  
impdp cux/cux DIRECTORY=test_dir DUMPFILE=cux_expdp_test_table.dmp TABLE_EXISTS_ACTION=TRUNCATE LOGFILE=cux_expdp_test_table.log  
 
例子:将UAT环境中表cux.cux_expdp_test_table中的数据导入到CRP2环境中;
 
一、从UAT环境中将数据导出:
 
1、创建DIRECTORY(必须在数据库用户下创建目录)
Sql代码   
login: orauat  
orauat's Password:  
  
. . . . . .  
  
$ sqlplus system/manager  
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 15 15:20:58 2013  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
SQL> create directory test_dir as '/home/orauat/zzj';  
Directory created.  
SQL> Grant read,write on directory test_dir to cux;  
Grant succeeded.  
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  
$ cd /home/orauat/zzj  
$ expdp cux/cux TABLES=cux_expdp_test_table DUMPFILE=cux_expdp_test_table.dmp DIRECTORY=test_dir LOGFILE=cux_expdp_test_table.log  
Export: Release 11.2.0.2.0 - Production on Wed May 15 15:06:12 2013  
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
;;;   
  
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  
Starting "CUX"."SYS_EXPORT_TABLE_01":  cux/******** TABLES=cux_expdp_test_table DUMPFILE=cux_expdp_test_table.dmp DIRECTORY=test_dir LOGFILE=cux_expdp_test_table.log   
Estimate in progress using BLOCKS method...  
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA  
Total estimation using BLOCKS method: 128 KB  
Processing object type TABLE_EXPORT/TABLE/TABLE  
. . exported "CUX"."CUX_EXPDP_TEST_TABLE"                5.460 KB       5 rows  
Master table "CUX"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded  
******************************************************************************  
Dump file set for CUX.SYS_EXPORT_TABLE_01 is:  
  /export/home/orauat/zzj/cux_expdp_test_table.dmp  
Job "CUX"."SYS_EXPORT_TABLE_01" successfully completed at 15:07:02  
$ ls  
cux_expdp_test_table.dmp      cux_expdp_test_table.log  
$  
 
2、将cux_expdp_test_table.dmp从/home/orauat/zzj目录下载下来,然后上传到CRP2环境的/home/oracrp2/zzj
 
二、将数据上传到CRP2环境:
 
1、创建DIRECTORY(必须在数据库用户下创建目录)
Sql代码   
login: oracrp2  
oracrp2's Password:  
  
. . .   
  
$ sqlplus system/manager  
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 15 15:20:58 2013  
Copyright (c) 1982, 2010, Oracle.  All rights reserved.  
Connected to:  
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
SQL> create directory test_dir as '/home/oracrp2/zzj';  
Directory created.  
SQL> Grant read,write on directory test_dir to cux;  
Grant succeeded.  
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  
$ cd /home/oracrp2/zzj  
$ ls  
cux_expdp_test_table.dmp  
$ impdp cux/cux DIRECTORY=test_dir DUMPFILE=cux_expdp_test_table.dmp TABLE_EXISTS_ACTION=TRUNCATE LOGFILE=cux_expdp_test_table.log  
Import: Release 11.2.0.2.0 - Production on Wed May 15 15:21:55 2013  
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  
Master table "CUX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded  
Starting "CUX"."SYS_IMPORT_FULL_01":  cux/******** DIRECTORY=test_dir DUMPFILE=cux_expdp_test_table.dmp TABLE_EXISTS_ACTION=TRUNCATE LOGFILE=cux_expdp_test_table.log   
Processing object type TABLE_EXPORT/TABLE/TABLE  
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA  
. . imported "CUX"."CUX_EXPDP_TEST_TABLE"                5.460 KB       5 rows  
Job "CUX"."SYS_IMPORT_FULL_01" successfully completed at 15:22:22  
$ ls  
cux_expdp_test_table.dmp      cux_expdp_test_table.log  
$  
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,