Linux under oracle11g Import export operation details

Anonymous Login with DBA

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.1.0 Production on Wed 8 16:39:53 2013

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

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

Switch User Login @enfodw

Sql> Conn Enfo_ods/[email protected]

Connected.

View Catalog Table

sql> desc dba_directories;

This user does not have permission to view

ERROR:

Ora-04043:object "SYS". " Dba_directories "does not exist

Log on to the DBA user

Sql> Conn sys/oracle001;

ERROR:

Ora-28009:connection as SYS should be as SYSDBA or sysoper

Warning:you is no longer connected to ORACLE.

Sql> Conn sys/oracle001 as SYSDBA;

Connected.

You can see the table contents

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

Delete Directory

sql> Drop directory Exp_dir;

Directory dropped.

Sql> select * from Dba_directories;

Exit the Execute Export command

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

[Email protected] ~]$ EXPDP enfo_ods/[email protected] Directory=dmpdir dumpfile=tsubject.dmp Logfile=tsubject.log Tables=tsubject;

Export:release 11.2.0.1.0-production on Wed 8 16:48:59 2013

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

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

Insufficient user rights, need to give enfo_ods export directory read and Write permissions

Sql> Conn Sys/[email protected] as SYSDBA;

Connected.

Because directory permissions are unclear, create a new directory as a DBA

Sql> Create directory Expdp_dir as '/HOME/ORACLE/WANGXJ ';

Directory created.

Sql> Grant Write,read on the 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

Execute Export command

[Email protected] ~]$ EXPDP enfo_ods/[email protected] Directory=expdp_dir dumpfile=tsubject.dmp Logfile=tsubject.log Tables=tsubject;

Export:release 11.2.0.1.0-production on Wed 8 16:57:05 2013

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

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

From the above information can be seen export success

[Email protected] ~]$ Sqlplus/nolog

Sql*plus:release 11.2.0.1.0 Production on Wed 8 16:59:48 2013

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

Sql> conn Enfo_ods/[email protected];

Connected.

Delete the table you just went to.

sql> drop table Tsubject;

Table dropped.

Execute Import command

[Email protected] ~]$ IMPDP enfo_ods/[email protected] Directory=expdp_dir dumpfile=tsubject.dmp Logfile=tsubject.log Tables=tsubject;

Sql> Select COUNT (1) from Tsubject;

COUNT (1)

----------

39495

Sql>

Export by user

[Email protected] ~]$ EXPDP enfo_ods/[email protected] Directory=expdp_dir dumpfile=enfo_ods.dmp Logfile=enfo_ods.log Owner=enfo_ods;

Export:release 11.2.0.1.0-production on Wed June 5 11:10:17 2013

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

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
. . 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 rows
. . Exported "Enfo_ods". " Tabletimestamp "7.460 KB 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 and rows
. . Exported "Enfo_ods". " Tglcpinfo "12.94 KB 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 rows
. . Exported "Enfo_ods". " Tpoolinfo "37.44 KB 158 rows
. . Exported "Enfo_ods". " tpoolinfo_s "26.18 KB and 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 and rows
. . Exported "Enfo_ods". " Trpt_balance "6.265 KB to Rows
. . Exported "Enfo_ods". " Trpt_company "5.476 KB 1 rows
. . Exported "Enfo_ods". " Trpt_data "10.75 KB
. . Exported "Enfo_ods". " Trpt_element "13.76 KB
. . Exported "Enfo_ods". " Trpt_file "7.101 KB 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, 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

Linux under oracle11g Import export operation details