Oracle11 expdp0734,oracle 11g expdp impdp 跨平台迁移数据
以下只在AIX 6.1 和RedHat 5.4上實驗成功
遷出環境：AIX 6.1 ORACLE 11.2.0.1
遷入環境：REDHAT 5.4 ORACLE 11.2.0.3
一.導出用戶greenlive的數據
1.創建green_back目錄用于存放導出來的數據
create directory green_back as '/oradata';
2.為green_back賦于目錄讀寫權限
grant read, write on directory green_back to greenlive;
3.檢查下創建的目錄是否成功
select * from dba_directories;
上述操作代碼如下示：SQL> create directory green_back as '/oradata';
Directory created.
SQL> grant read, write on directory green_back to greenlive;
Grant succeeded.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS GREEN_BACK /oradata
4.執行導出操作
expdp usename/password directory=green_back dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
$ expdp greenlive/******** directory=green_back dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
Export: Release 11.2.0.1.0 - Production on Sun Jun 16 14:04:54 2013
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "GREENLIVE"."SYS_EXPORT_SCHEMA_01": greenlive/******** directory=green_back dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.840 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "GREENLIVE"."C_IMAGE" 2.692 GB 5708 rows
. . exported "GREENLIVE"."MEMBERINFO" 11.04 MB 100787 rows
. . exported "GREENLIVE"."MEMBERINFO_1206" 10.86 MB 94757 rows
. . exported "GREENLIVE"."G_GOODSTOCNO" 6.454 MB 125992 rows
. . exported "GREENLIVE"."G_IMAGE" 5.301 MB 781 rows
. . exported "GREENLIVE"."USERTEST" 6.905 MB 43129 rows
. . exported "GREENLIVE"."G_ACCCHGDETAIL" 5.275 MB 83079 rows
. . exported "GREENLIVE"."G_ACCOUNT" 4.586 MB 197072 rows
. . exported "GREENLIVE"."IMAGE" 4.066 MB 90 rows
. . exported "GREENLIVE"."C_FAMILYINFO" 2.914 MB 79829 rows
. . exported "GREENLIVE"."C_CARBONHISTORY" 2.129 MB 30553 rows
. . exported "GREENLIVE"."C_MEMBERSCHOOL" 2.353 MB 79831 rows
. . exported "GREENLIVE"."MMB" 2.234 MB 51327 rows
. . exported "GREENLIVE"."G_MEMBERTOPROJECT" 1.265 MB 36696 rows
. . exported "GREENLIVE"."T1" 1.033 MB 43089 rows
. . exported "GREENLIVE"."NEWS" 567.2 KB 8 rows
. . exported "GREENLIVE"."C_SHOOLINFO" 714.0 KB 19478 rows
. . exported "GREENLIVE"."C_PACKER" 325.5 KB 5033 rows
. . exported "GREENLIVE"."C_TRENDS" 234.3 KB 90 rows
. . exported "GREENLIVE"."ANNOUNCEMENT" 88.75 KB 2 rows
. . exported "GREENLIVE"."C_MEMBERPAPER" 268.2 KB 14994 rows
. . exported "GREENLIVE"."TB_COMPANY" 126.0 KB 4 rows
. . exported "GREENLIVE"."C_NEWS" 104.5 KB 39 rows
. . exported "GREENLIVE"."C_KNOWLEDGE" 34.63 KB 11 rows
. . exported "GREENLIVE"."CITY" 16.67 KB 426 rows
. . exported "GREENLIVE"."C_ADMININFO" 10.03 KB 2 rows
. . exported "GREENLIVE"."C_DOWNLOAD" 18.41 KB 6 rows
. . exported "GREENLIVE"."C_OPTIONS" 16.48 KB 225 rows
. . exported "GREENLIVE"."C_PROMISE" 8.570 KB 7 rows
. . exported "GREENLIVE"."C_QANDA" 11.50 KB 53 rows
. . exported "GREENLIVE"."C_TESTPAPER" 8 KB 2 rows
. . exported "GREENLIVE"."GROUPS" 5.898 KB 2 rows
. . exported "GREENLIVE"."G_ADDRESSLIST" 14.78 KB 73 rows
. . exported "GREENLIVE"."G_CLIST" 6.632 KB 31 rows
. . exported "GREENLIVE"."G_GOODSINFO" 21.96 KB 9 rows
. . exported "GREENLIVE"."G_GOODSTOPROJECT" 7.179 KB 3 rows
. . exported "GREENLIVE"."G_LOGISTICS" 10.01 KB 8 rows
. . exported "GREENLIVE"."G_ORDERINFO" 45.98 KB 481 rows
. . exported "GREENLIVE"."G_ORDERLIST" 20.47 KB 485 rows
. . exported "GREENLIVE"."G_POSTCOMPANY" 7.929 KB 1 rows
. . exported "GREENLIVE"."G_POSTFEE" 17.52 KB 426 rows
. . exported "GREENLIVE"."G_PROJECTINFO" 8.085 KB 1 rows
. . exported "GREENLIVE"."G_SHOPCART" 26.64 KB 988 rows
. . exported "GREENLIVE"."G_SUPPLIERS" 7.609 KB 1 rows
. . exported "GREENLIVE"."G_SYSPARM" 7.968 KB 2 rows
. . exported "GREENLIVE"."PROVINCE" 6.078 KB 34 rows
. . exported "GREENLIVE"."RESOURCEGROUP" 16.75 KB 537 rows
. . exported "GREENLIVE"."RESOURCES" 9.679 KB 85 rows
. . exported "GREENLIVE"."TB_STAR" 10.56 KB 6 rows
. . exported "GREENLIVE"."USERGROUP" 5.976 KB 2 rows
. . exported "GREENLIVE"."USERLOG" 7.75 KB 12 rows
. . exported "GREENLIVE"."USERS" 12.14 KB 2 rows
. . exported "GREENLIVE"."BANNER" 0 KB 0 rows
. . exported "GREENLIVE"."C_BANNER" 0 KB 0 rows
. . exported "GREENLIVE"."C_POINTS_RULE" 0 KB 0 rows
. . exported "GREENLIVE"."C_PRIZE" 0 KB 0 rows
. . exported "GREENLIVE"."G_MEMBER_COMMUNITY" 0 KB 0 rows
. . exported "GREENLIVE"."G_MEMBER_SZ" 0 KB 0 rows
. . exported "GREENLIVE"."NEWSIMAGES" 0 KB 0 rows
Master table "GREENLIVE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GREENLIVE.SYS_EXPORT_SCHEMA_01 is:
/oradata/greenlive20130616.dmp
Job "GREENLIVE"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:09:20
二.導入數據
1.創建數據用戶“greenlive”
可以用命令行也可以用諸如plsqldev工具創建
2.創建greenlive 目錄用于存放復制來的數據
create directory greenlive as '/opt/oracle/oradata';
3.為greenlive賦于目錄讀寫權限
grant read, write on directory greenlive to greenlive;
4.復制數據到/opt/oracle/oradata(注意復制來的數據權屬于oracle用戶)
SQL> create directory greenlive as '/opt/oracle/oradata';
Directory created.
SQL> grant read, write on directory greenlive to greenlive;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@host1 oradata]$ ls
orcl
[oracle@host1 oradata]$ cp /soft/greenlive20130616/greenlive20130616.log /opt/oracle/oradata/
[oracle@host1 oradata]$ ls
greenlive20130616.dmp greenlive20130616.log orcl
[oracle@host1 oradata]$ ll
總計 2898476
-rwxr-xr-x 1 oracle oinstall 2965127168 06-17 08:50 greenlive20130616.dmp
-rwxr-xr-x 1 oracle oinstall 6164 06-17 08:50 greenlive20130616.log
drwxr-x--- 2 oracle oinstall 4096 05-13 10:48 orcl
5.執行導入操作
impdp username/password@orcl directory=greenlive dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
[oracle@host1 oradata]$ impdp greenlive/********@orcl directory=greenlive dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
Import: Release 11.2.0.3.0 - Production on Mon Jun 17 08:54:37 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "GREENLIVE"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "GREENLIVE"."SYS_IMPORT_SCHEMA_01": greenlive/********@orcl directory=greenlive dumpfile=greenlive20130616.dmp logfile=greenlive20130616.log schemas=greenlive
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"GREENLIVE"."T1" failed to create with error:
ORA-00959: tablespace 'SLCARD_TABS01' does not exist
Failing sql is:
CREATE TABLE "GREENLIVE"."T1" ("T_NUM" NUMBER(3,0), "T_MAIL" VARCHAR2(100 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SLC
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "GREENLIVE"."C_IMAGE" 2.692 GB 5708 rows
. . imported "GREENLIVE"."MEMBERINFO" 11.04 MB 100787 rows
. . imported "GREENLIVE"."MEMBERINFO_1206" 10.86 MB 94757 rows
. . imported "GREENLIVE"."G_GOODSTOCNO" 6.454 MB 125992 rows
. . imported "GREENLIVE"."G_IMAGE" 5.301 MB 781 rows
. . imported "GREENLIVE"."USERTEST" 6.905 MB 43129 rows
. . imported "GREENLIVE"."G_ACCCHGDETAIL" 5.275 MB 83079 rows
. . imported "GREENLIVE"."G_ACCOUNT" 4.586 MB 197072 rows
. . imported "GREENLIVE"."IMAGE" 4.066 MB 90 rows
. . imported "GREENLIVE"."C_FAMILYINFO" 2.914 MB 79829 rows
. . imported "GREENLIVE"."C_CARBONHISTORY" 2.129 MB 30553 rows
. . imported "GREENLIVE"."C_MEMBERSCHOOL" 2.353 MB 79831 rows
. . imported "GREENLIVE"."MMB" 2.234 MB 51327 rows
. . imported "GREENLIVE"."G_MEMBERTOPROJECT" 1.265 MB 36696 rows
. . imported "GREENLIVE"."NEWS" 567.2 KB 8 rows
. . imported "GREENLIVE"."C_SHOOLINFO" 714.0 KB 19478 rows
. . imported "GREENLIVE"."C_PACKER" 325.5 KB 5033 rows
. . imported "GREENLIVE"."C_TRENDS" 234.3 KB 90 rows
. . imported "GREENLIVE"."ANNOUNCEMENT" 88.75 KB 2 rows
. . imported "GREENLIVE"."C_MEMBERPAPER" 268.2 KB 14994 rows
. . imported "GREENLIVE"."TB_COMPANY" 126.0 KB 4 rows
. . imported "GREENLIVE"."C_NEWS" 104.5 KB 39 rows
. . imported "GREENLIVE"."C_KNOWLEDGE" 34.63 KB 11 rows
. . imported "GREENLIVE"."CITY" 16.67 KB 426 rows
. . imported "GREENLIVE"."C_ADMININFO" 10.03 KB 2 rows
. . imported "GREENLIVE"."C_DOWNLOAD" 18.41 KB 6 rows
. . imported "GREENLIVE"."C_OPTIONS" 16.48 KB 225 rows
. . imported "GREENLIVE"."C_PROMISE" 8.570 KB 7 rows
. . imported "GREENLIVE"."C_QANDA" 11.50 KB 53 rows
. . imported "GREENLIVE"."C_TESTPAPER" 8 KB 2 rows
. . imported "GREENLIVE"."GROUPS" 5.898 KB 2 rows
. . imported "GREENLIVE"."G_ADDRESSLIST" 14.78 KB 73 rows
. . imported "GREENLIVE"."G_CLIST" 6.632 KB 31 rows
. . imported "GREENLIVE"."G_GOODSINFO" 21.96 KB 9 rows
. . imported "GREENLIVE"."G_GOODSTOPROJECT" 7.179 KB 3 rows
. . imported "GREENLIVE"."G_LOGISTICS" 10.01 KB 8 rows
. . imported "GREENLIVE"."G_ORDERINFO" 45.98 KB 481 rows
. . imported "GREENLIVE"."G_ORDERLIST" 20.47 KB 485 rows
. . imported "GREENLIVE"."G_POSTCOMPANY" 7.929 KB 1 rows
. . imported "GREENLIVE"."G_POSTFEE" 17.52 KB 426 rows
. . imported "GREENLIVE"."G_PROJECTINFO" 8.085 KB 1 rows
. . imported "GREENLIVE"."G_SHOPCART" 26.64 KB 988 rows
. . imported "GREENLIVE"."G_SUPPLIERS" 7.609 KB 1 rows
. . imported "GREENLIVE"."G_SYSPARM" 7.968 KB 2 rows
. . imported "GREENLIVE"."PROVINCE" 6.078 KB 34 rows
. . imported "GREENLIVE"."RESOURCEGROUP" 16.75 KB 537 rows
. . imported "GREENLIVE"."RESOURCES" 9.679 KB 85 rows
. . imported "GREENLIVE"."TB_STAR" 10.56 KB 6 rows
. . imported "GREENLIVE"."USERGROUP" 5.976 KB 2 rows
. . imported "GREENLIVE"."USERLOG" 7.75 KB 12 rows
. . imported "GREENLIVE"."USERS" 12.14 KB 2 rows
. . imported "GREENLIVE"."BANNER" 0 KB 0 rows
. . imported "GREENLIVE"."C_BANNER" 0 KB 0 rows
. . imported "GREENLIVE"."C_POINTS_RULE" 0 KB 0 rows
. . imported "GREENLIVE"."C_PRIZE" 0 KB 0 rows
. . imported "GREENLIVE"."G_MEMBER_COMMUNITY" 0 KB 0 rows
. . imported "GREENLIVE"."G_MEMBER_SZ" 0 KB 0 rows
. . imported "GREENLIVE"."NEWSIMAGES" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"GREENLIVE"."T1" creation failed
Job "GREENLIVE"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 08:57:05
這里有兩個報錯ORA-39083 ORA-00959，關于ORA-39083這個報錯網上有一大堆處理的辦法，
我這里主要是因為有個表空間沒建好，我們這個數據比較特殊，所以這個報錯沒影響，就沒去處理
___________________________________________________________________________
當導出庫與導入庫表空間不一致時，可以使用remap_tablespace 參數，如下示
impdp slwebtest/slwebtest directory=slwebtest dumpfile=slwebtest0828.dmp logfile=slwebtest0828.log schemas=slwebtest remap_tablespace=slwebtest_tabs01:users remap_tablespace=slweb_tabs01:users
總結
以上是生活随笔為你收集整理的Oracle11 expdp0734,oracle 11g expdp impdp 跨平台迁移数据的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 250万的房子,男方付首付,女方供尾款,
- 下一篇： 男孩子好听的名字单字