Oracle表的导出、导入
阅读原文时间:2023年08月09日阅读:5

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

下面记录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 数据库用户密码

file_path 导入文件路径

file_name 导入文件名称

log_path 导入日志路径

log_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