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