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、等待導出完成。