【exp】使用exp工具的rows选项完成结构迁移

在某些情况下，我们 仅需要一套完整的数据库Schema结构，而暂时不关注 表数据本身。使用exp工具的rows选项可以很便捷的达到这个目的。

1.在sec用户中创建样例表t
sec@ora10g> create table t as select * from all_objects;

Table created.

sec@ora10g> desc t;
 Name                        Null?    Type
 --------------------------- -------- ----------------------
 OWNER                       NOT NULL VARCHAR2(30)
 OBJECT_NAME                 NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                       VARCHAR2(30)
 OBJECT_ID                   NOT NULL NUMBER
 DATA_OBJECT_ID                       NUMBER
 OBJECT_TYPE                          VARCHAR2(19)
 CREATED                     NOT NULL DATE
 LAST_DDL_TIME               NOT NULL DATE
 TIMESTAMP                            VARCHAR2(19)
 STATUS                               VARCHAR2(7)
 TEMPORARY                            VARCHAR2(1)
 GENERATED                            VARCHAR2(1)
 SECONDARY                            VARCHAR2(1)

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

  COUNT(*)
----------
     11878

2.使用“rows=n”选项导出sec用户下的结构
ora10g@secdb /home/oracle$ exp sec/sec file=sec.dmp rows=n

Export: Release 10.2.0.3.0 - Production on Mon Oct 11 22:07:35 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported

About to export specified users ...
. 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                              T
. 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.

从导出记录中可以看到，此时T表中的数据没有被导出。此时转储文件中仅仅记录了sec用户下的结构。

3.使用生成的转储文件完成向另外一个用户secooler的导入
ora10g@secdb /home/oracle$ imp secooler/secooler file=sec.dmp full=y

Import: Release 10.2.0.3.0 - Production on Mon Oct 11 22:10:19 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SEC, not by you

import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SECOOLER
Import terminated successfully without warnings.

4.验证导入结果
sys@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> desc t;
 Name                        Null?    Type
 --------------------------- -------- ----------------------
 OWNER                       NOT NULL VARCHAR2(30)
 OBJECT_NAME                 NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                       VARCHAR2(30)
 OBJECT_ID                   NOT NULL NUMBER
 DATA_OBJECT_ID                       NUMBER
 OBJECT_TYPE                          VARCHAR2(19)
 CREATED                     NOT NULL DATE
 LAST_DDL_TIME               NOT NULL DATE
 TIMESTAMP                            VARCHAR2(19)
 STATUS                               VARCHAR2(7)
 TEMPORARY                            VARCHAR2(1)
 GENERATED                            VARCHAR2(1)
 SECONDARY                            VARCHAR2(1)

secooler@ora10g> select count(*) from t;

  COUNT(*)
----------
         0

OK，表T的结构已经完成迁移，注意不包含数据，这也是我们的目的。

5.小结
这里仅以T表为例，展示了Schema结构的迁移过程，从整个导出和导入过程中可以看到，我们完成了原有sec用户下所有的数据库对象结构向secooler用户的迁移。


Good luck.

secooler
10.10.11

-- The End --

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

转载于:http://blog.itpub.net/519536/viewspace-675761/