Linux下oracle11g 导入导出操作详细

浏览数：22 / 时间：2015年06月12日

//用dba匿名登录

[oracle@enfo212 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 8 16:39:53 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

//切换用户登录 @enfodw

SQL> conn enfo_ods/enfo_ods@enfodw

Connected.

//查看目录表

SQL> desc dba_directories;

//此用户没权限查看

ERROR:

ORA-04043: object "SYS"."DBA_DIRECTORIES" does not exist

//登录dba用户

SQL> conn sys/Oracle001;

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Warning: You are no longer connected to ORACLE.

SQL> conn sys/Oracle001 as sysdba;

Connected.

//可以看到表内容

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS EXP_DIR

/u01/app/oracle/product/11.2.0/db1/network/admin

/ dbexp

SYS XMLDIR

/ade/b/1191423112/oracle/rdbms/xml

SYS DMPDIR

/home/oracle

OWNER DIRECTORY_NAME

------------------------------ ------------------------------

DIRECTORY_PATH

--------------------------------------------------------------------------------

SYS DATA_PUMP_DIR

/u01/app/oracle/admin/xintuo/dpdump/

SYS ORACLE_OCM_CONFIG_DIR

/u01/app/oracle/product/11.2.0/db1/ccr/state

//删除目录

SQL> drop directory exp_dir;

Directory dropped.

SQL> select * from dba_directories;

//退出执行导出命令

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@enfo212 ~]$ expdp enfo_ods/enfo_ods@enfodw directory=dmpdir dumpfile=tsubject.dmp logfile=tsubject.log tables=tsubject;

Export: Release 11.2.0.1.0 - Production on Wed May 8 16:48:59 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name DMPDIR is invalid

//用户权限不够，需要给enfo_ods 导出目录的读写权限

SQL> conn sys/Oracle001@enfodw as sysdba;

Connected.

//由于目录权限不清楚，下面就用dba的身份新建一个目录

SQL> create directory expdp_dir as ‘/home/oracle/wangxj‘;

Directory created.

SQL> grant write,read on directory expdp_dir to enfo_ods;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

//执行导出命令

[oracle@enfo212 ~]$ expdp enfo_ods/enfo_ods@enfodw directory=expdp_dir dumpfile=tsubject.dmp logfile=tsubject.log tables=tsubject;

Export: Release 11.2.0.1.0 - Production on Wed May 8 16:57:05 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ENFO_ODS"."SYS_EXPORT_TABLE_01": enfo_ods/********@enfodw directory=expdp_dir dumpfile=tsubject.dmp logfile=tsubject.log tables=tsubject

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 10 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ENFO_ODS"."TSUBJECT" 8.075 MB 39495 rows

Master table "ENFO_ODS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ENFO_ODS.SYS_EXPORT_TABLE_01 is:

/home/oracle/wangxj/tsubject.dmp

Job "ENFO_ODS"."SYS_EXPORT_TABLE_01" successfully completed at 16:57:12

//从以上信息可以看出导出成功

[oracle@enfo212 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 8 16:59:48 2013

SQL> conn enfo_ods/enfo_ods@enfodw;

Connected.

//删除刚才到出过的表

SQL> drop table tsubject;

Table dropped.

//执行导入命令

[oracle@enfo212 ~]$ impdp enfo_ods/enfo_ods@enfodw directory=expdp_dir dumpfile=tsubject.dmp logfile=tsubject.log tables=tsubject;

SQL> select count(1) from tsubject;

COUNT(1)

----------

39495

SQL>

按照用户导出

[oracle@enfo212 ~]$ expdp enfo_ods/enfo_ods@enfodw directory=expdp_dir dumpfile=enfo_ods.dmp logfile=enfo_ods.log owner=enfo_ods;

Export: Release 11.2.0.1.0 - Production on Wed Jun 5 11:10:17 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "owner=enfo_ods" Location: Command Line, Replaced with: "schemas=enfo_ods"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "ENFO_ODS"."SYS_EXPORT_SCHEMA_01": enfo_ods/********@enfodw directory=expdp_dir dumpfile=enfo_ods.dmp logfile=enfo_ods.log schemas=enfo_ods reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 372.2 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
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/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/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ENFO_ODS"."TASSACTDETAILYE" 65.38 MB 519074 rows
. . exported "ENFO_ODS"."TASSTACTPOSTLIST" 66.54 MB 654527 rows
. . exported "ENFO_ODS"."TUNPOSTLIST" 58.37 MB 408377 rows
. . exported "ENFO_ODS"."TSTOCKDETAIL" 42.85 MB 220893 rows
. . exported "ENFO_ODS"."TUNPOSTWARRANT" 15.31 MB 98568 rows
. . exported "ENFO_ODS"."HHQINFO" 7.891 MB 116272 rows
. . exported "ENFO_ODS"."TSUBJECT" 8.075 MB 39495 rows
. . exported "ENFO_ODS"."HLRB" 5.877 MB 156096 rows
. . exported "ENFO_ODS"."HDEBTDETAIL" 4.925 MB 28583 rows
. . exported "ENFO_ODS"."TCONTRACT" 4.768 MB 10841 rows
. . exported "ENFO_ODS"."TCUSTOMERINFO" 4.735 MB 11131 rows
. . exported "ENFO_ODS"."HZCFZB" 3.651 MB 81180 rows
. . exported "ENFO_ODS"."HBENIFITOR" 3.123 MB 21534 rows
. . exported "ENFO_ODS"."HNAVPRICEINFO" 3.176 MB 15178 rows
. . exported "ENFO_ODS"."THTZJCHANGES" 2.318 MB 10259 rows
. . exported "ENFO_ODS"."TRPT_BALANCEINFO" 1.181 MB 7688 rows
. . exported "ENFO_ODS"."TENTCUSTINFO" 607.9 KB 1502 rows
. . exported "ENFO_ODS"."TPRODUCTSALE" 585.0 KB 6218 rows
. . exported "ENFO_ODS"."TDICTPARAM" 529.3 KB 8000 rows
. . exported "ENFO_ODS"."TSTOCKINFO" 477.8 KB 4552 rows
. . exported "ENFO_ODS"."TPRODUCT" 327.1 KB 344 rows
. . exported "ENFO_ODS"."TRPT_RAISEINFO" 318.3 KB 1480 rows
. . exported "ENFO_ODS"."MONEY_TPRODUCTINFO" 298.2 KB 863 rows
. . exported "ENFO_ODS"."TBONDINFO113003" 186.6 KB 1343 rows
. . exported "ENFO_ODS"."TINDUSTRYCATEGORY" 175.7 KB 1199 rows
. . exported "ENFO_ODS"."TINVEST" 137.3 KB 202 rows
. . exported "ENFO_ODS"."T1104_004" 111 KB 344 rows
. . exported "ENFO_ODS"."TBENCHANGES" 77.52 KB 172 rows
. . exported "ENFO_ODS"."TCAPITALINFO" 95.46 KB 241 rows
. . exported "ENFO_ODS"."TDKDBCUSTINFO" 53.45 KB 608 rows
. . exported "ENFO_ODS"."TE_XTXMXX" 115.2 KB 136 rows
. . exported "ENFO_ODS"."TINVESTCHANGE" 50.44 KB 359 rows
. . exported "ENFO_ODS"."TRAISEINFO_S" 95.34 KB 592 rows
. . exported "ENFO_ODS"."TRPT_CREDITDETAILS" 60.10 KB 187 rows
. . exported "ENFO_ODS"."TRPT_LOG" 87.79 KB 796 rows
. . exported "ENFO_ODS"."HCURRENCYRATE" 9.515 KB 4 rows
. . exported "ENFO_ODS"."MONEY_TFUNCTYPE" 10.11 KB 131 rows
. . exported "ENFO_ODS"."MONEY_TMENUINFO" 15.39 KB 50 rows
. . exported "ENFO_ODS"."MONEY_TOPERATOR" 6.304 KB 2 rows
. . exported "ENFO_ODS"."MONEY_TOPROLE" 5.843 KB 2 rows
. . exported "ENFO_ODS"."MONEY_TROLE" 6.328 KB 3 rows
. . exported "ENFO_ODS"."MONEY_TROLERIGHT" 8.570 KB 98 rows
. . exported "ENFO_ODS"."STUDENT1" 5.453 KB 2 rows
. . exported "ENFO_ODS"."STUDENT2" 5.453 KB 2 rows
. . exported "ENFO_ODS"."TABLEPART" 10.54 KB 125 rows
. . exported "ENFO_ODS"."TABLETIMESTAMP" 7.460 KB 60 rows
. . exported "ENFO_ODS"."TAUTOSUBCONFIG" 30.11 KB 295 rows
. . exported "ENFO_ODS"."TCURRENCY" 7.265 KB 5 rows
. . exported "ENFO_ODS"."TESTPRODUCT" 5.507 KB 6 rows
. . exported "ENFO_ODS"."TFUNDACCT" 42.03 KB 77 rows
. . exported "ENFO_ODS"."TGLCPINFO" 12.94 KB 19 rows
. . exported "ENFO_ODS"."TINTEGERPARAM" 18.98 KB 337 rows
. . exported "ENFO_ODS"."TNV_SELF004" 6.265 KB 1 rows
. . exported "ENFO_ODS"."TNV_SELF005" 31.03 KB 356 rows
. . exported "ENFO_ODS"."TNV_SELF005_ASS" 10.59 KB 29 rows
. . exported "ENFO_ODS"."TPOOLINFO" 37.44 KB 158 rows
. . exported "ENFO_ODS"."TPOOLINFO_S" 26.18 KB 210 rows
. . exported "ENFO_ODS"."TPRODUCTADD" 8.718 KB 5 rows
. . exported "ENFO_ODS"."TPRODUCTADDINFO" 39.53 KB 658 rows
. . exported "ENFO_ODS"."TPRODUCTINFO_S" 35.02 KB 210 rows
. . exported "ENFO_ODS"."TRPT_BALANCE" 6.265 KB 31 rows
. . exported "ENFO_ODS"."TRPT_COMPANY" 5.476 KB 1 rows
. . exported "ENFO_ODS"."TRPT_DATA" 10.75 KB 105 rows
. . exported "ENFO_ODS"."TRPT_ELEMENT" 13.76 KB 99 rows
. . exported "ENFO_ODS"."TRPT_FILE" 7.101 KB 12 rows
. . exported "ENFO_ODS"."TRPT_FILE_D" 6.656 KB 9 rows
. . exported "ENFO_ODS"."TRPT_INFO" 6.242 KB 2 rows
. . exported "ENFO_ODS"."TRPT_PROD" 29.04 KB 293 rows
. . exported "ENFO_ODS"."TSUBPRODUCT" 34.60 KB 62 rows
. . exported "ENFO_ODS"."TSYSCONTROL" 37.03 KB 288 rows
. . exported "ENFO_ODS"."TSYSTEMINFO" 18.54 KB 1 rows
. . exported "ENFO_ODS"."TZQFXRINFO" 32.06 KB 584 rows
. . exported "ENFO_ODS"."USERINFO" 5.851 KB 2 rows
. . exported "ENFO_ODS"."HAUAMOUNT" 0 KB 0 rows
. . exported "ENFO_ODS"."HAUHQINFO" 0 KB 0 rows
. . exported "ENFO_ODS"."HAULIB" 0 KB 0 rows
. . exported "ENFO_ODS"."HSTOCKAMOUNT1" 0 KB 0 rows
. . exported "ENFO_ODS"."HSUBDETAILYE" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104INFO" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_004_SUB" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_005" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_005_ASS" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_006" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_006_GL" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_007" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_007_SUB" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_016" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_401" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_BASE01" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_BASE02" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_BASE03" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_BASE04" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_BASE05" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_DK" 0 KB 0 rows
. . exported "ENFO_ODS"."T1104_SUBCODE" 0 KB 0 rows
. . exported "ENFO_ODS"."TAUACCT" 0 KB 0 rows
. . exported "ENFO_ODS"."TBANKCREDIT" 0 KB 0 rows
. . exported "ENFO_ODS"."TBANKCREDITLIST" 0 KB 0 rows
. . exported "ENFO_ODS"."TBENIFITOR" 0 KB 0 rows
. . exported "ENFO_ODS"."TCOMMCREDIT" 0 KB 0 rows
. . exported "ENFO_ODS"."TCOMMTENANCY" 0 KB 0 rows
. . exported "ENFO_ODS"."TDEPARTMENT" 0 KB 0 rows
. . exported "ENFO_ODS"."TDEPLOY" 0 KB 0 rows
. . exported "ENFO_ODS"."TENTCUSTCARD" 0 KB 0 rows
. . exported "ENFO_ODS"."TGROUPCUSTRIGHT" 0 KB 0 rows
. . exported "ENFO_ODS"."TGROUPINFO" 0 KB 0 rows
. . exported "ENFO_ODS"."TITEM" 0 KB 0 rows
. . exported "ENFO_ODS"."TMODIINFO" 0 KB 0 rows
. . exported "ENFO_ODS"."TNUMBERPARAM" 0 KB 0 rows
. . exported "ENFO_ODS"."TNV_SELF006" 0 KB 0 rows
. . exported "ENFO_ODS"."TNV_SELF008" 0 KB 0 rows
. . exported "ENFO_ODS"."TNV_SUBCODE" 0 KB 0 rows
. . exported "ENFO_ODS"."TOPERATOR" 0 KB 0 rows
. . exported "ENFO_ODS"."TOPTOGROUP" 0 KB 0 rows
. . exported "ENFO_ODS"."TPREITEM" 0 KB 0 rows
. . exported "ENFO_ODS"."TPRODUCTCITY" 0 KB 0 rows
. . exported "ENFO_ODS"."TPRODUCTNVINFO" 0 KB 0 rows
. . exported "ENFO_ODS"."TRPT_STOCKDETAILS" 0 KB 0 rows
. . exported "ENFO_ODS"."TRPT_SUBCODE" 0 KB 0 rows
. . exported "ENFO_ODS"."TS31_01" 0 KB 0 rows
. . exported "ENFO_ODS"."TS31_02" 0 KB 0 rows
. . exported "ENFO_ODS"."TS31_11" 0 KB 0 rows
. . exported "ENFO_ODS"."TS31_12" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_11" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_12" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_31" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_32" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_41" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_411" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_412" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_42" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_43" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_431" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_51" 0 KB 0 rows
. . exported "ENFO_ODS"."TS32_52" 0 KB 0 rows
. . exported "ENFO_ODS"."TS33_01" 0 KB 0 rows
. . exported "ENFO_ODS"."TS33_02" 0 KB 0 rows
. . exported "ENFO_ODS"."TS33_11" 0 KB 0 rows
. . exported "ENFO_ODS"."TS34_11" 0 KB 0 rows
. . exported "ENFO_ODS"."TS34_12" 0 KB 0 rows
. . exported "ENFO_ODS"."TS34_13" 0 KB 0 rows
. . exported "ENFO_ODS"."TS34_21" 0 KB 0 rows
. . exported "ENFO_ODS"."TSECUDEPART" 0 KB 0 rows
. . exported "ENFO_ODS"."TSECUMONEYDETAIL" 0 KB 0 rows
. . exported "ENFO_ODS"."TSECURITYACCT" 0 KB 0 rows
. . exported "ENFO_ODS"."TSUB_CONFIG" 0 KB 0 rows
Master table "ENFO_ODS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ENFO_ODS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/wangxj/enfo_ods.dmp
Job "ENFO_ODS"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:10:43