将sh用户全的数据全部导入到oltp

本文介绍两种Oracle数据库迁移的方法：使用exp/imp工具和expdp/impdp工具。详细步骤包括创建表空间、用户权限配置、数据导出及导入，并对比了两种方法的不同之处。

第一种方式：exp/imp工具
1.新建用户oltp_user
(1).为新用户oltp_user建立表空间oltp
SQL>create tablespace oltp datafile '/u01/app/oracle/oradata/PROD1/oltp01.dbf' size 100M;
(2).新建用户oltp_user并为其分配50的表空间份额
SQL＞create uesr oltp_user default tablespace oltp quota 50M on tablespace oltp;
2.为sh，oltp_user用户赋导入导出的权限
SQL＞grant connect,resource,imp_full_database,exp_full_database to sh,oltp_user；
3.导出sh用户的数据
node101.ora11g.com-PROD1>exp sh/sh file=sh.dmp owner=sh log=sh.log
4.导入到oltp_user用户
node101.ora11g.com-PROD1>imp oltp_user/oracle file=sh.dmp fromuser=sh touser=oltp_user log=sh.log ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Oct 22 10:40:11 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
Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SH, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "CAL_MONTH_SALES_MV" 48 rows imported
. . importing table "CHANNELS" 5 rows imported
. . importing partition "COSTS":"COSTS_1995" 0 rows imported
. . importing partition "COSTS":"COSTS_1996" 0 rows imported
. . importing partition "COSTS":"COSTS_H1_1997" 0 rows imported
. . importing partition "COSTS":"COSTS_H2_1997" 0 rows imported
. . importing partition "COSTS":"COSTS_Q1_1998" 4411 rows imported
. . importing partition "COSTS":"COSTS_Q2_1998" 2397 rows imported
. . importing partition "COSTS":"COSTS_Q3_1998" 4129 rows imported
. . importing partition "COSTS":"COSTS_Q4_1998" 4577 rows imported
. . importing partition "COSTS":"COSTS_Q1_1999" 5884 rows imported
. . importing partition "COSTS":"COSTS_Q2_1999" 4179 rows imported
. . importing partition "COSTS":"COSTS_Q3_1999" 4336 rows imported
. . importing partition "COSTS":"COSTS_Q4_1999" 5060 rows imported
. . importing partition "COSTS":"COSTS_Q1_2000" 3772 rows imported
. . importing partition "COSTS":"COSTS_Q2_2000" 3715 rows imported
. . importing partition "COSTS":"COSTS_Q3_2000" 4798 rows imported
. . importing partition "COSTS":"COSTS_Q4_2000" 5088 rows imported
. . importing partition "COSTS":"COSTS_Q1_2001" 7328 rows imported
. . importing partition "COSTS":"COSTS_Q2_2001" 5882 rows imported
. . importing partition "COSTS":"COSTS_Q3_2001" 7545 rows imported
. . importing partition "COSTS":"COSTS_Q4_2001" 9011 rows imported
. . importing partition "COSTS":"COSTS_Q1_2002" 0 rows imported
. . importing partition "COSTS":"COSTS_Q2_2002" 0 rows imported
. . importing partition "COSTS":"COSTS_Q3_2002" 0 rows imported
. . importing partition "COSTS":"COSTS_Q4_2002" 0 rows imported
. . importing partition "COSTS":"COSTS_Q1_2003" 0 rows imported
. . importing partition "COSTS":"COSTS_Q2_2003" 0 rows imported
. . importing partition "COSTS":"COSTS_Q3_2003" 0 rows imported
. . importing partition "COSTS":"COSTS_Q4_2003" 0 rows imported
. . importing table "COUNTRIES" 23 rows imported
. . importing table "CUSTOMERS" 55500 rows imported
. . importing table "FWEEK_PSCAT_SALES_MV" 11266 rows imported
. . importing table "PRODUCTS" 72 rows imported
. . importing table "PROMOTIONS" 503 rows imported
. . importing partition "SALES":"SALES_1995" 0 rows imported
. . importing partition "SALES":"SALES_1996" 0 rows imported
. . importing partition "SALES":"SALES_H1_1997" 0 rows imported
. . importing partition "SALES":"SALES_H2_1997" 0 rows imported
. . importing partition "SALES":"SALES_Q1_1998" 43687 rows imported
. . importing partition "SALES":"SALES_Q2_1998" 35758 rows imported
. . importing partition "SALES":"SALES_Q3_1998" 50515 rows imported
. . importing partition "SALES":"SALES_Q4_1998" 48874 rows imported
. . importing partition "SALES":"SALES_Q1_1999" 64186 rows imported
. . importing partition "SALES":"SALES_Q2_1999" 54233 rows imported
. . importing partition "SALES":"SALES_Q3_1999" 67138 rows imported
. . importing partition "SALES":"SALES_Q4_1999" 62388 rows imported
. . importing partition "SALES":"SALES_Q1_2000" 62197 rows imported
. . importing partition "SALES":"SALES_Q2_2000" 55515 rows imported
. . importing partition "SALES":"SALES_Q3_2000" 58950 rows imported
. . importing partition "SALES":"SALES_Q4_2000" 55984 rows imported
. . importing partition "SALES":"SALES_Q1_2001" 60608 rows imported
. . importing partition "SALES":"SALES_Q2_2001" 63292 rows imported
. . importing partition "SALES":"SALES_Q3_2001" 65769 rows imported
. . importing partition "SALES":"SALES_Q4_2001" 69749 rows imported
. . importing partition "SALES":"SALES_Q1_2002" 0 rows imported
. . importing partition "SALES":"SALES_Q2_2002" 0 rows imported
. . importing partition "SALES":"SALES_Q3_2002" 0 rows imported
. . importing partition "SALES":"SALES_Q4_2002" 0 rows imported
. . importing partition "SALES":"SALES_Q1_2003" 0 rows imported
. . importing partition "SALES":"SALES_Q2_2003" 0 rows imported
. . importing partition "SALES":"SALES_Q3_2003" 0 rows imported
. . importing partition "SALES":"SALES_Q4_2003" 0 rows imported
. . importing table "SUPPLEMENTARY_DEMOGRAPHICS" 4500 rows imported
. . importing table "TIMES" 1826 rows imported
Import terminated successfully without warnings.
SH@ PROD1>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DIMENSION_EXCEPTIONS TABLE
DR$SUP_TEXT_IDX$I TABLE
DR$SUP_TEXT_IDX$K TABLE
DR$SUP_TEXT_IDX$N TABLE
DR$SUP_TEXT_IDX$R TABLE
FWEEK_PSCAT_SALES_MV TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PRODUCTS TABLE
PROFITS VIEW
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE
18 rows selected.
SH@ PROD1>conn oltp_user/oracle
Connected.
OLTP_USER@ PROD1>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DR$SUP_TEXT_IDX$I TABLE
DR$SUP_TEXT_IDX$K TABLE
DR$SUP_TEXT_IDX$N TABLE
DR$SUP_TEXT_IDX$R TABLE
FWEEK_PSCAT_SALES_MV TABLE
PRODUCTS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PROFITS VIEW
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE
17 rows selected.
少了一张表

第二种方式：expdp/impdp
node101.ora11g.com-PROD1>expdp sh/sh dumpfile=sh.dmp directory=DATA_PUMP_DIR
导入之前，目标库不能有oltp_user，否则导入会报错：
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 10:53:41

node101.ora11g.com-PROD1>impdp system/oracle dumpfile=sh.dmp directory=DATA_PUMP_DIR remap_schema=sh:oltp_user
导入成功之后，解锁用户
SYS@ PROD1>alter user oltp_user account unlock;
SYS@ PROD1>alter user oltp_user identified by oracle;
SYS@ PROD1>conn oltp_user/oracle
Connected.
OLTP_USER@ PROD1>select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CAL_MONTH_SALES_MV TABLE
CHANNELS TABLE
COSTS TABLE
COUNTRIES TABLE
CUSTOMERS TABLE
DIMENSION_EXCEPTIONS TABLE
DR$SUP_TEXT_IDX$I TABLE
DR$SUP_TEXT_IDX$K TABLE
DR$SUP_TEXT_IDX$N TABLE
DR$SUP_TEXT_IDX$R TABLE
FWEEK_PSCAT_SALES_MV TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PRODUCTS TABLE
PROFITS VIEW
PROMOTIONS TABLE
SALES TABLE
SALES_TRANSACTIONS_EXT TABLE
SUPPLEMENTARY_DEMOGRAPHICS TABLE
TIMES TABLE
18 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29653765/viewspace-1815553/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29653765/viewspace-1815553/