Oracle表的导出、导入

有些情况下，需要单独导出某些表，用或者分析数据。

下面记录Oracle表的导出导入方法

1. 表的导出

./exp $username/$passwd@$ORACLE_SID file=/$file_path/$file_name.dmp tables=table1,table2,...
# username 数据库用户名
# passwd 数据库用户密码
# ORACLE_SID 数据库实例名
# file_path 导出文件路径
# file_name 导出文件名称

2. 表的导入

./imp $username/$passwd file=/file_path/file_name.dmp log=/log_path/log_name.log fromuser=$uname
# username 数据库用户名
# passwd 数据库用户密码
# ORACLE_SID 数据库实例名
# file_path 导入文件路径
# file_name 导入文件名称
# uname 导出数据库用户名

示例：

1. 导出

[oracle@ufdb39 bin]$ ./exp cwy_init/init_123456789@orcl file=/u01/20230116.dmp tables=sys_usermanage,ma_emp

Export: Release 11.2.0.4.0 - Production on Mon Jan 16 15:00:38 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


EXP-00056: ORACLE error 28002 encountered
ORA-28002: the password will expire within 4 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                 SYS_USERMANAGE      10082 rows exported
. . exporting table                         MA_EMP      10166 rows exported
Export terminated successfully without warnings.

2. 导入：

[oracle@ufdb39 bin]$ ./imp cwy_init/init_123456789 file=/u01/20230116.dmp log=/u01/20230116.log fromuser=cwy_init

Import: Release 11.2.0.4.0 - Production on Mon Jan 16 15:02:10 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


IMP-00058: ORACLE error 28002 encountered
ORA-28002: the password will expire within 4 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CWY_INIT's objects into CWY_INIT
. importing CWY_INIT's objects into CWY_INIT