一、导出数据
导出数据是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、等待导出完成。
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.
