手把手教你怎样导入oracle10g的dmp格式数据.doc

网上有很多关于oracle导入的日志，但写的都不详细，新手需要的一步一步指导。本文是oracle10g版本的导入dmp格式的数据。超详细的步骤，新手只要照着做就好了。Microsoft Windows XP 版本 5.1.2600(C) 版权所有 1985-2001 Microsoft Corp.C:Documents and SettingsAdministratorimpImport: Release .0 - Production on 星期三 9月 26 23:36:31 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.用户名: system(指的是你导出oracle数据的用户名)口令: (指的是你导出oracle数据的密码，dos下不会显示)连接到: Oracle Database 10g Release .0 - Production导入文件: EXPDAT.DMP d:a.dmp(最好把导出的数据放在根目录下)输入插入缓冲区大小 (最小为 8192) 307201024（不要管提示，就写1024）经由常规路径由 EXPORT:V10.02.01 创建的导出文件警告: 这些对象由 FLOWERUSER 导出, 而不是当前用户已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入只列出导入文件的内容 (yes/no): no yes导入整个导出文件 (yes/no): no yes. 正在将 FLOWERUSER 的对象导入到 FLOWER BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=SYS_CONTEXT(USERENV, CURRENT_SCHEMA), export_db_name=ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM, i nst_scn=2758006); COMMIT; END; CREATE SEQUENCE SEQ_UID MINVALUE 1 MAXVALUE 999999999999999999999999999 I NCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE CREATE SEQUENCE MEMBERS_S MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE CREATE SEQUENCE ORDER2_S MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE CREATE SEQUENCE FLOWER_S MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE CREATE TABLE COMMEN (COMMENTID NUMBER NOT NULL ENABLE, ORDERID NUMBER , FLOWERID NUMBER, MEMBERID NUMBER, RANK VARCHAR2(100), COMM VARCHA R2(500), COMMENTTIME DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) T ABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 COMMEN CREATE UNIQUE INDEX COMMEN_PK ON COMMEN (COMMENTID ) PCTFREE 10 INIT RANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUF FER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE COMMEN ADD CONSTRAINT COMMEN_PK PRIMARY KEY (COMMENTID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELI STS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING EN ABLE CREATE TABLE FLOWER (FLOWERID NUMBER NOT NULL ENABLE, FLOWERNAME VARC HAR2(100), TYPEID NUMBER, MATERIAL VARCHAR2(200), PACKAGE VARCHAR2(50 0), FLOWERLA VARCHAR2(500), SEND VARCHAR2(500), EXPLAIN VARCHAR2(500) , IMG VARCHAR2(50), PRICE VARCHAR2(20), CURR NUMBER(9, 2), KEY VARC HAR2(500), PRODUCT CLOB, SALES NUMBER, TRAFFIC NUMBER, ISCOMMENT NU MBER(1, 0) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS LOB (PRODUCT) STORE AS (TABLESPACE FLOWER ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65 536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT). . 正在跳过表 FLOWER CREATE UNIQUE INDEX FLOWER_PK ON FLOWER (FLOWERID ) PCTFREE 10 INITR ANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFF ER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE FLOWER ADD CONSTRAINT FLOWER_PK PRIMARY KEY (FLOWERID) U SING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ENA BLE CREATE TABLE MANAGER (MANAGEID VARCHAR2(20) NOT NULL ENABLE, MANAGEPWD VARCHAR2(20) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INIT IAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FL OWER LOGGING NOCOMPRESS. . 正在跳过表 MANAGER CREATE TABLE MEMBER (MEMBERID NUMBER NOT NULL ENABLE, EMAIL VARCHAR2( 50) NOT NULL ENABLE, USERNAME VARCHAR2(50) NOT NULL ENABLE, USERPWD VAR CHAR2(20) NOT NULL ENABLE, SEX VARCHAR2(2) NOT NULL ENABLE, TELEPHONE V ARCHAR2(20), ADDRESS VARCHAR2(50), REGTIME DATE NOT NULL ENABLE, USERS TATUS VARCHAR2(10) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT RANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE FAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 MEMBER CREATE UNIQUE INDEX MEMBER_PK ON MEMBER (MEMBERID ) PCTFREE 10 INITR ANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFF ER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE MEMBER ADD CONSTRAINT MEMBER_PK PRIMARY KEY (MEMBERID) U SING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS TS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ENA BLE CREATE TABLE MESSAGE (USERNAME VARCHAR2(20), TEL VARCHAR2(20), MAIL VARCHAR2(50), SUBJECT VARCHAR2(200), CONTENT VARCHAR2(500) PCTFREE 1 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 MESSAGE CREATE TABLE NEW (NEWID NUMBER NOT NULL ENABLE, TYPENAME VARCHAR2(50) , TITLE VARCHAR2(150), PUBLISHTIME DATE, CONTENT BLOB, IMGURL VARCH AR2(50), KEY VARCHAR2(500) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25 5 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS LOB (CONTENT) STORE AS (TABLESPAC E FLOWER ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT). . 正在跳过表 NEW CREATE UNIQUE INDEX NEW_PK ON NEW (NEWID ) PCTFREE 10 INITRANS 2 MAX TRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D EFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE NEW ADD CONSTRAINT NEW_PK PRIMARY KEY (NEWID) USING INDE X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREE LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ENABLE CREATE TABLE ORDER2 (ORDERID NUMBER NOT NULL ENABLE, MEMBERID NUMBER, CONSIGNORNAME VARCHAR2(20), CONSIGNORTEL VARCHAR2(20), CONSIGNORADR VARCHAR2(50), RECEIVINGNAME VARCHAR2(20), CADRESS VARCHAR2(50), CTEL VARCHAR2(20), DATETIME DATE, ISPAY NUMBER(1, 0), O_STATE VARCHAR2(20) , TOTAL NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(IN ITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 ORDER2 CREATE UNIQUE INDEX ORDER2_PK ON ORDER2 (ORDERID ) PCTFREE 10 INITRA NS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFE R_POOL DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE ORDER2 ADD CONSTRAINT ORDER2_PK PRIMARY KEY (ORDERID) US ING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ENAB LE CREATE TABLE ORDER2_DETAIL (DETAIL_ID NUMBER NOT NULL ENABLE, ORDERID NUMBER, FLOWERID NUMBER, price NUMBER, NUM NUMBER) PCTFREE 10 PCTU SED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST G ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 ORDER2_DETAIL CREATE UNIQUE INDEX ORDER2_DETAIL_PK ON ORDER2_DETAIL (DETAIL_ID ) P CTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIS T GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE ORDER2_DETAIL ADD CONSTRAINT ORDER2_DETAIL_PK PRIMARY KEY (DETAIL_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIA L 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOW ER LOGGING ENABLE CREATE TABLE TYPE (TYPEID NUMBER NOT NULL ENABLE, TYPENAME VARCHAR2(5 0), PID NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(IN ITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING NOCOMPRESS. . 正在跳过表 TYPE CREATE UNIQUE INDEX TYPE_PK ON TYPE (TYPEID ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO L DEFAULT) TABLESPACE FLOWER LOGGING ALTER TABLE TYPE ADD CONSTRAINT TYPE_PK PRIMARY KEY (TYPEID) USING I NDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE FLOWER LOGGING ENABLE ALTER SESSION SET _LOAD_WITHOUT_COMPILE = PLSQL ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1 NLS_LENGTH_SEMANTICS = BYTE PL SQL_CODE_TYPE = INTERPRETED PLSQL_DEBUG = TRUE PLSQL_WARNINGS = DISABLE: ALLPLSQL_CCFLAGS = CREATE procedure proc_add( id in manager.id%type, password in manager.password%type) as begin insert into manager values(id,password); dbms_output.put_line(Hello world!); end; ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2 NLS_LENGTH_SEMANTICS = BYTE PL SQL_DEBUG = FALSE PLSQL_CODE_TYPE = INTERPRETED PLSQL_WARNINGS = DISABLE:A LL PLSQL_CCFLAGS = ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1 NLS_LENGTH_SEMANTICS = BYTE PL SQL_CODE_TYPE = INTERPRETED PLSQL_DEBUG = TRUE PLSQL_WARNINGS = DISABLE: ALLPLSQL_CCFLAGS = CREATE function fun_str return varchar2 is str varchar2(500); begin for x in (select id from manager) loop str:=str|x.id|,; end loop; str:=substr(str,1,length(str)-1);-支掉最后的,号 return str; end; ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2 NLS_LENGTH_SEMANTICS = BYTE PL SQL_DEBUG = FALSE PLSQL_CODE_TYPE = INTERPRETED PLSQL_WARNINGS = DISABLE:A LL PLSQL_CCFLAGS = ALTER SESSION SET _LOAD_WITHOUT_COMPILE = NONE ALTER PROCEDURE PROC_ADD COMPILE REUSE SETTINGS TIMESTAMP 2011-09-18:10: 36:07 ALTER FUNCTION FUN_STR COMPILE REUSE SETTINGS TIMESTAMP 2011-09-18:17:28 :22 ALTER TABLE ORDER2 ADD CONSTRAINT ORDER2_MEMBER_FK1 FOREIGN KEY (MEMBE RID) REFERENCES MEMBER (MEMBERID) ENABLE NOVALIDATE ALTER TABLE ORDER2_DETAIL ADD CONSTRAINT ORDER2_DETAIL_MEMBER_FK1 FOREI GN KEY (FLOWERID) REFERENCES FLOWER (FLOWERID) ENABLE NOVALIDATE ALTER TABLE ORDER2_DETAIL ADD CONSTRAINT ORDER2_DETAIL_ORDER2_FK1 FOREI GN KEY (ORDERID) REFERENCES ORDER2 (ORDERID) ENABLE NOVALIDATE ALTER TABLE FLOWER ADD CONSTRAINT FLOWER_TYPE_FK1 FOREIGN KEY (TYPEID ) REFERENCES TYPE (TYPEID) ENABLE NOVALIDATE ALTER TABLE COMMEN ADD CONSTRAINT COMMEN_FLOWER_FK1 FOREIGN KEY (FLOWE RID) REFERENCES FLOWER (FLOWERID) ENABLE NOVALIDATE ALTER TABLE COMMEN ADD CONSTRAINT COMMEN_MEMBER_FK1 FOREIGN KEY (MEMBE RID) REFERENCES MEMBER (MEMBERID) EN