1、创建DIRECTORY (必须在数据库用户下创建目录)
- sqlplus system/manager
- create directory test_dir as '/home/orauat/zzj';
2、授权
- Grant read,write on directory test_dir to cux;
--查看目录及权限
- 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、执行导出导入
- 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(必须在数据库用户下创建目录)
- 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(必须在数据库用户下创建目录)
- 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
- $