ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【IMP】使用imp工具的show选项获取超大备份文件中的SQL语句将非常耗时

【IMP】使用imp工具的show选项获取超大备份文件中的SQL语句将非常耗时

原创 Linux操作系统 作者:secooler 时间:2011-03-31 23:04:38 0 删除 编辑
相信大家都知道imp工具除了可以将备份文件中的数据导入到数据库中之外,还可以只获取备份文件中包含的SQL语句。
不是很熟悉的朋友可以参考文章《【IMPDP】【IMP】SQL脚本尽收眼底——SHOW参数与SQLFILE参数对比》(http://space.itpub.net/519536/viewspace-631290)。

本文希望给大家澄清一个事实:在备份文件很大的情况下,获取SQL语句与真实的数据导入之间时间差距虽然是悬殊的,但获取SQL过程的时间成本同样不容忽视!

1.在sec用户下模拟创建两张大表
sec@ora10g> create table t1 as select * from all_objects;
sec@ora10g> insert into t1 select * from t1;
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /
sec@ora10g> /

2603584 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t1;

  COUNT(*)
----------
   5207168

sec@ora10g> create table t2 as select * from t1;

Table created.

2.生成sec用户的备份数据
ora10g@secdb /db_backup$ exp sec/sec file=t.dmp log=t.log

Export: Release 10.2.0.1.0 - Production on Fri Apr 1 21:49:32 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC
About to export SEC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC's tables via Conventional Path ...
. . exporting table                             T1    5207168 rows exported
. . exporting table                             T2    5207168 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.

ora10g@secdb /db_backup$ du -sm t.dmp
1096    t.dmp

备份文件大小1G。

3.真实导入数据所需要的时间
1)删除sec用户中T1和T2表中的数据
sec@ora10g> truncate table t1;

Table truncated.

sec@ora10g> truncate table t2;

Table truncated.

2)准备导入脚本
ora10g@secdb /db_backup$ cat imp_t_DATA.sh
date
imp sec/sec file=t.dmp full=y ignore=y
date

3)导入数据
ora10g@secdb /db_backup$ sh imp_t_DATA.sh
Fri Apr  1 21:54:19 CST 2011

Import: Release 10.2.0.1.0 - Production on Fri Apr 1 21:54:19 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. . importing table                           "T1"    5207168 rows imported
. . importing table                           "T2"    5207168 rows imported
Import terminated successfully without warnings.
Fri Apr  1 21:57:37 CST 2011


总用时3分18秒!

4.仅获取备份文件中SQL语句的时间
1)清理T1和T2表数据
sec@ora10g> truncate table t1;

Table truncated.

sec@ora10g> truncate table t2;

Table truncated.

2)准备生成SQL的脚本
ora10g@secdb /db_backup$ cat imp_t_SQL.sh
date
imp sec/sec file=t.dmp full=y ignore=y show=y
date

3)获取备份文件中SQL脚本
ora10g@secdb /db_backup$ sh imp_t_SQL.sh
Fri Apr  1 22:03:20 CST 2011

Import: Release 10.2.0.1.0 - Production on Fri Apr 1 22:03:20 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ORA10G', inst_scn=>'1243333');"
 "COMMIT; END;"
 "CREATE TABLE "T1" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VARC"
 "HAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER"
 " NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CRE"
 "ATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAM"
 "P" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED""
 " VARCHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
 "XTRANS 255 STORAGE(INITIAL 603979776 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T1"

 "CREATE TABLE "T2" ("OWNER" VARCHAR2(30) NOT NULL ENABLE, "OBJECT_NAME" VARC"
 "HAR2(30) NOT NULL ENABLE, "SUBOBJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER"
 " NOT NULL ENABLE, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(19), "CRE"
 "ATED" DATE NOT NULL ENABLE, "LAST_DDL_TIME" DATE NOT NULL ENABLE, "TIMESTAM"
 "P" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED""
 " VARCHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
 "XTRANS 255 STORAGE(INITIAL 603979776 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
 "OOL DEFAULT) TABLESPACE "TBS_SEC_D" LOGGING NOCOMPRESS"
. . skipping table "T2"

Import terminated successfully without warnings.
Fri Apr  1 22:03:40 CST 2011


比较开始和结束的时间,总用时20秒!

5.小结
20秒与3分18秒进行比较是比较悬殊的。这是显然的,因为数据导入的过程是真正的将数据写入到了数据库,而获取备份文件中的SQL脚本的过程只是全面地读取了一遍备份文件而已。
但是,这里要强调的是:这里的20秒是可以被无限放大的,随着备份文件越来越大获取SQL的整个过程也将是一个非常可观的数值。不可小视。在使用这种方法读取备份文件信息时请充分评估所需的时间。这不是一蹴而就便能完成的事情。

Good luck.

secooler
11.03.31

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-691425/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8184909