Oracle
將數據庫從 Oracle 11g Enterprise 遷移/導出到 Oracle 11g Express
所以我是一個 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
這是一個普遍的想法,因此請根據您的環境和需求進行調整。