Oracle

將數據庫從 Oracle 11g Enterprise 遷移/導出到 Oracle 11g Express

  • December 9, 2015

所以我是一個 DBA 新手,我有一個 Oracle Enterprise 伺服器在生產中執行,在“Your_Database_Settings”下的所有報告 -> 數據字典報告 -> 版本橫幅下提供以下資訊。

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production 
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

我想導出/複製我在伺服器上執行的數據庫並將其導入新的 Oracle 11g Express 安裝以執行一些沙盒測試。

有人可以幫助我詳細說明如何做到這一點嗎?

這是 HR 使用者(Oracle 數據庫中的範例使用者)和國家表的範例。我們將通過查詢提取幾行 - 其中 region_id=2(表有 25 行)。我創建了另一個具有相同結構的使用者 HR2 和表國家(但沒有 FK 限制以簡化導入)。從 HR 我們將導出 Country 表的子集,在 HR2 中我們將導入數據。

> [oracle@orcla ~]$ expdp system/password tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:\"where region_id=2\"

Export: Release 11.2.0.1.0 - Production on Wed Dec 9 23:51:33 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** tables=hr.countries dumpfile=testdump.dmp content=data_only query=hr.countries:"where region_id=2" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "HR"."COUNTRIES"                            5.921 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
 /u01/app/oracle/admin/orcl1/dpdump/testdump.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:51:35

[oracle@orcla ~]$ impdp system/password directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2
Import: Release 11.2.0.1.0 - Production on Wed Dec 9 23:52:04 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=testdump.dmp remap_schema=hr:hr2 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           5.921 KB       5 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 23:52:06

好的,現在來檢查結果。

sqlplus hr2/hr2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 00:02:39 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from countries;

 COUNT(*)
----------
        5

這是一個普遍的想法,因此請根據您的環境和需求進行調整。

引用自:https://dba.stackexchange.com/questions/123195