分类目录归档：Oracle

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语句)，而不会真正的在数据库中执行