1.exp导出数据
[oracle@node1 ~]$ exp "'/ as sysdba'" owner=ECPUUM file=/tmp/ECPUUM.dmp log=/tmp/E.log
Export: Release 10.2.0.5.0 - Production on Wed Jan 18 16:32:34 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ECPUUM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ECPUUM
About to export ECPUUM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ECPUUM's tables via Conventional Path ...
. . exporting table TAB_UUM_COMPANY_DETAIL 263 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
2.创建新用户
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:39:17 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> drop user xifenfei cascade;
User dropped.
SQL> create user xifenfei identified by xff;
User created.
SQL> grant dba to xifenfei;
Grant succeeded.
SQL> exit
3.imp+show=y导入
[oracle@node1 ~]$ imp "'/ as sysdba'" fromuser=ECPUUM touser=xifenfei \
>file=/tmp/ECPUUM.dmp log=/tmp/ECPUUM.log show=y
Import: Release 11.2.0.3.0 - Production on Wed Jan 18 16:41:52 2012
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ECPUUM's objects into XIFENFEI
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'ECP', inst_scn=>'20152169');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "XIFENFEI""
"CREATE SEQUENCE "SEQ_UUM_COMPANY_ID" MINVALUE 1 MAXVALUE 999999999999999999"
"9999999 INCREMENT BY 1 START WITH 462 CACHE 20 NOORDER NOCYCLE"
"CREATE GLOBAL TEMPORARY TABLE "HT_TAB_UUM_USERS" ("USER_ID" NUMBER(19, 0) N"
"OT NULL ENABLE) ON COMMIT DELETE ROWS "
"CREATE TABLE "TAB_UUM_COMPANY_DETAIL" ("COMPANY_ID" NUMBER(7, 0) NOT NULL E"
"NABLE, "COMPANY_NAMEALIAS" VARCHAR2(64) NOT NULL ENABLE, "COMPANY_TYPE" NUM"
"BER, "COMPANY_COUNTRY" NUMBER, "COMPANY_PROVICE" NUMBER, "COMPANY_CITY" NUM"
"BER, "COMPANY_AREA" NUMBER, "COMPANY_ADDRESS" VARCHAR2(256), "COMPANY_POSTC"
"ODE" NUMBER(6, 0), "COMPANY_PHONE" VARCHAR2(13), "COMPANY_WEBSITE" VARCHAR2"
"(128), "COMPANY_TRADEBIG" NUMBER, "COMPANY_SIZE" NUMBER, "COMPANY_PAYMENT" "
"NUMBER, "COMPANY_EXTEND1" VARCHAR2(32), "COMPANY_EXTEND2" VARCHAR2(32), "CO"
"MPANY_EXTEND3" VARCHAR2(32), "COMPANY_TRADESMALL" NUMBER, "COMPANY_REG_CAPI"
"TAL" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
" 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
"ESPACE "TS_PUB_BASE" LOGGING NOCOMPRESS"
. . skipping table "TAB_UUM_COMPANY_DETAIL"
"CREATE UNIQUE INDEX "PK_TAB_UUM_COMPANY_DETAIL" ON "TAB_UUM_COMPANY_DETAIL""
" ("COMPANY_ID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
"NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "
""TS_PUB_BASE" LOGGING"
"ALTER SESSION SET CURRENT_SCHEMA= "XIFENFEI""
"ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
"M_COMPANY_51784" ("COMPANY_ID") ALWAYS"
"ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
"M_COMPANY_52725" ("COMPANY_ID") ALWAYS"
"ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
"M_COMPANY_52946" ("COMPANY_ID") ALWAYS"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_ID" IS '企业ID'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_NAMEALIAS" IS '公司名称"
"简称'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_TYPE" IS '公司类型:单选"
"，选择：1 有限责任公司、2 股份有限公司、3 国有独资公司、4个人独资企业、5个体"
"工商户、6合伙企业、7外商投资企业、8私营企业、9其他；'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_COUNTRY" IS '公司所在地"
":从列表选择，分四项：国家、省份、地市、区县，其中国家一项默认值为：中国；'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PROVICE" IS '省'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_CITY" IS '市'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_AREA" IS '地区'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_ADDRESS" IS '公司详细地"
"址: 手工录入，100个汉字；'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_POSTCODE" IS '邮编'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PHONE" IS '公司电话'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_WEBSITE" IS '公司网址'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_SIZE" IS '公司规模'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PAYMENT" IS '付款方式'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND1" IS '扩展字段1"
"'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND2" IS '扩展字段1"
"'"
"COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND3" IS '扩展字段3"
"'"
. . skipping table "TAB_UUM_COMPANY_DETAIL"
Import terminated successfully without warnings.
4.查询imp结果
[oracle@node1 ~]$ sqlplus xifenfei/xff
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:45:10 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select count(*) from TAB_UUM_COMPANY;
select count(*) from TAB_UUM_COMPANY
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tab;
no rows selected
通过实验知道，show=y表示展示imp导入的时候，会执行的相关语句(不包括insert语句)，而不会真正的在数据库中执行
