Oracle数据库导入导出实用技巧分享

一、导出数据

导出数据是Oracle数据库常见的操作之一。下面我们将介绍如何使用expdp命令导出数据。

1、连接到Oracle数据库。

$ sqlplus username/password@database

2、使用expdp命令导出数据。

$ expdp username/password@database tablespaces=tablespace_name dumpfile=dump_file.dmp logfile=log_file.log

其中，tablespace_name是要导出的表空间名称，dump_file.dmp是导出数据的文件名，log_file.log是导出过程的日志文件名。

3、等待导出完成。

Export: Release 19.0.0.0.0 - Production on Tue Aug 17 10:04:36 2021
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLESPACE_01":  username/password@database tablespaces=tablespace_name dumpfile=dump_file.dmp logfile=log_file.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "SCOTT"."EMP"                            4.039 KB       14 rows
. . exported "SCOTT"."DEPT"                           5.976 KB        4 rows
. . exported "SCOTT"."BONUS"                          0 KB            0 rows
. . exported "SCOTT"."SALGRADE"                       0 KB            0 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
***************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/dump_file.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Aug 17 10:08:18 2021 elapsed 0 00:03:42

4、查看导出的文件。

$ ls dump_file.dmp

二、导入数据

导入数据是Oracle数据库常见的操作之一。下面我们将介绍如何使用impdp命令导入数据。

1、连接到Oracle数据库。

$ sqlplus username/password@database

2、使用impdp命令导入数据。

$ impdp username/password@database tablespaces=tablespace_name dumpfile=dump_file.dmp logfile=log_file.log

其中，tablespace_name是要导入的表空间名称，dump_file.dmp是导入数据的文件名，log_file.log是导入过程的日志文件名。

3、等待导入完成。

Import: Release 19.0.0.0.0 - Production on Tue Aug 17 10:28:47 2021
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_TABLESPACE_01":  username/password@database tablespaces=tablespace_name dumpfile=dump_file.dmp logfile=log_file.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"         
. . importing partition                     "EMP": 
. . . 17 rows 
. . importing partition                     "EMP": 
. . . 19 rows 
. . importing partition                     "EMP": 
. . . 16 rows 
. . importing partition                     "EMP": 
. . . 24 rows 
. . importing partition                     "EMP": 
. . . 25 rows 
. . importing partition                     "EMP": 
. . . 27 rows 
. . importing partition                     "EMP": 
. . . 19 rows 
. . importing partition                     "EMP": 
. . . 18 rows 
. . importing partition                     "EMP": 
. . . 34 rows 
. . importing partition                     "EMP": 
. . . 26 rows 
. . importing partition                     "EMP": 
. . . 22 rows 
. . importing partition                     "EMP": 
. . . 20 rows 
. . importing table                          "DEPT"       
. . . 4 rows imported
. . importing table                          "BONUS"      
. . . 0 rows imported
. . importing table                          "SALGRADE"   
. . . 0 rows imported
Master table "SYS"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_IMPORT_TABLESPACE_01 is:
  /u01/app/oracle/admin/orcl/dpdump/dump_file.dmp
Job "SYS"."SYS_IMPORT_TABLESPACE_01" successfully completed at Tue Aug 17 10:41:19 2021 elapsed 0 00:12:31

4、查看导入的数据。

$ sqlplus username/password@database
SQL> select * from table_name;

三、导出表结构

导出表结构是有时我们需要备份数据库结构的一种操作。下面我们将介绍如何使用exp命令导出表结构。

1、连接到Oracle数据库。

$ sqlplus username/password@database

2、使用exp命令导出表结构。

$ exp username/password@database file=export_file.dmp log=log_file.log owner=owner_name

其中，export_file.dmp是导出表结构的文件名，log_file.log是导出过程的日志文件名，owner_name是要导出表的所有者。

3、等待导出完成。

Export: Release 19.0.0.0.0 - Production on Tue Aug 17 10:44:33 2021
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
Note: triggers on tables will not be exported
Note: referential constraints will not be exported
Note: bitmap indexes on tables will not be exported
Note: materialized views will not be exported
Note: system defined tables will not be exported
Note: flashback query attributes are not exported
Note: data for table "SCOTT"."EMP" will not be exported
Note: data for table "SCOTT"."DEPT" will not be exported
Note: data for table "SCOTT"."BONUS" will not be exported
Note: data for table "SCOTT"."SALGRADE" will not be exported
Export terminated successfully without warnings.

4、查看导出的文件。

$ ls export_file.dmp

四、导入表结构

导入表结构是有时我们需要恢复数据库结构的一种操作。下面我们将介绍如何使用imp命令导入表结构。

1、连接到Oracle数据库。

$ sqlplus username/password@database

2、使用imp命令导入表结构。

$ imp username/password@database file=export_file.dmp log=log_file.log full=y

其中，export_file.dmp是导入表结构的文件名，log_file.log是导入过程的日志文件名，full=y表示导入所有对象。

3、等待导入完成。

Import: Release 19.0.0.0.0 - Production on Tue Aug 17 10:46:09 2021
Version 19.9.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_IMPORT_FULL_01":  username/password@database file=export_file.dmp log=log_file.log full=y
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                            4.039 KB       14 rows
. . imported "SCOTT"."DEPT"                           5.976 KB        4 rows
. . imported "SCOTT"."BONUS"                          0 KB            0 rows
. . imported "SCOTT"."SALGRADE"                       0 KB            0 rows
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/SYS_PRIVILEGE
Processing object type SCHEMA_EXPORT/PRE_SYSTEM_PRIVILEGE
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE
Processing object type SCHEMA_EXPORT/PROFILE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/ROLE
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/PRIMARY/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/FOREIGN/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/PRIMARY/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/GLOBAL_INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX_PARTITION
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/FOREIGN/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT/FK_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX_SUBPARTITION
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/PRIMARY/INDEX_PARTITION
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS_TYPE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS_TYPE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS_TYPE
Completed "SYS"."SYS_IMPORT_FULL_01"
********************************************************************************
Dump file set for SYS.SYS_IMPORT_FULL_01 is:
  /u01/app/oracle/admin/orcl/dpdump/export_file.dmp
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Aug 17 10:51:01 2021 elapsed 0 00:04:50

4、查看导入的表。

$ sqlplus username/password@database
SQL> select * from table_name;

五、自定义导出内容

有时候我们只需要导出特定的表或数据，而不是整个数据库。下面我们将介绍如何使用expdp命令自定义导出内容。

1、连接到Oracle数据库。

$ sqlplus username/password@database

2、使用expdp命令自定义导出内容。

$ expdp username/password@database tables=table_name directory=dir_name dumpfile=dump_file.dmp logfile=log_file.log

其中，table_name是要导出的表名，dir_name是导出文件的目录名，dump_file.dmp是导出数据的文件名，log_file.log是导出过程的日志文件名。

3、等待导出完成。