今天客户让我帮他导入一个exp导出的dmp文件,我问他要导入到哪个用户,客户说不知道%>_
cleardb$
cleardb$imp \" as sysdba\" file=jk.dmp
Import: Release 11.2.0.3.0 - Production on Tue May 19 16:32:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by JK, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully
cleardb$
提示说要嘛指定full=y,要嘛指定fromuser/touser,因为导入的库是一套监控用的生产库,不能指望用full=y的方式了,只能找到该dmp文件中对象的owner才有办法导入了。可以通过下面方法找到该dmp文件的owner
cleardb$strings jk.dmp|more
TEXPORT:V09.02.00
RUSERS
2048
1 11:29:7 2010jk.dmp
#G##
#G##
-07:00
BYTE
INTERPRETED
PROCACTION
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>'JK', export_db_name=>'*******', inst_scn=>'*****');
COMMIT; END;
可以看到用户名为JK,O(∩_∩)O哈!,为了防止还有其他用户存在,可以通过strings jk.dmp|grep "schema_name"来查看
重新导入dmp文件(此处省略了在库中创建相应用户、给予权限的步骤)成功
imp \" as sysdba\" file=jk.dmp fromuser=jk touser=jk
...
Import terminated successfully with warnings.
导入后客户说“给我看看表名就可以了,我只要表名”,再次~~o(>_
cleardb$strings jk.dmp|grep "CREATE TABLE "
CREATE TABLE "*******" ("ACCESSRIGHTSID" NUMBER(*,0), "ACCESSSHORTKEY" VARCHAR2(32), "ACCESSEXPRESSION" LONG RAW) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STATPUB" LOGGING NOCOMPRESS
CREATE TABLE "*******" ("RESOURCE_SID" NUMBER(9, 0), "NAME" VARCHAR2(128), "TYPE" VARCHAR2(64)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STATPUB" LOGGING NOCOMPRESS
CREATE TABLE "*******" ("RESOURCE_SID" NUMBER(9, 0), "CONTENTSIZE" NUMBER(9, 0), "CONTENT" LONG RAW) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STATPUB" LOGGING NOCOMPRESS
CREATE TABLE "*******" ("NAME" VARCHAR2(32), "DATADB" VARCHAR2(32), "UUID" CHAR(32), "DESCRIPTION" VARCHAR2(255), "UP" VARCHAR2(255), "JDBCURL" VARCHAR2(255), "JDBCURLATTRIBUTES" VARCHAR2(255), "JDBCDRIVER" VARCHAR2(255), "DBDRIVER" VARCHAR2(255), "DATECREATED" DATE, "DATEMODIFIED" DATE, "DEFAULTOBJECT" NUMBER(9, 0), "MINCONNS" NUMBER(9, 0), "MAXCONNS" NUMBER(9, 0), "SCHEMAVERSION" VARCHAR2(64), "SCHEMASIZE" NUMBER(9, 0), "DBSCHEMA" LONG RAW) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STATPUB" LOGGING NOCOMPRESS
CREATE TABLE "********" ("RESOURCE_SID" NUMBER(*,0), "RESOURCE_NAME" VARCHAR2(128), "PARENT_SID" NUMBER(9, 0), "RESOURCE_UUID" CHAR(32), "READACCESS" NUMBER(9, 0), "WRITEACCESS" NUMBER(9, 0), "PROTECTACCESS" NUMBER(9, 0), "SELECTACCESS" NUMBER(9, 0), "EXECUTEACCESS" NUMBER(9, 0), "ATTRIBUTES" LONG RAW) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "STATPUB" LOGGING NOCOMPRESS
cleardb$