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

表数据本身。使用exp工具的rows选项可以很便捷的达到这个目的。1.在sec用户中创建样例表tsec@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 NUMBERDATA_OBJECT_ID                       NUMBEROBJECT_TYPE                          VARCHAR2(19)CREATED                     NOT NULL DATELAST_DDL_TIME               NOT NULL DATETIMESTAMP                            VARCHAR2(19)STATUS                               VARCHAR2(7)TEMPORARY                            VARCHAR2(1)GENERATED                            VARCHAR2(1)SECONDARY                            VARCHAR2(1)sec@ora10g> select count(*) from t;COUNT(*)----------118782.使用“rows=n”选项导出sec用户下的结构ora10g@secdb /home/oracle$ exp sec/sec file=sec.dmp rows=nExport: Release 10.2.0.3.0 - Production on Mon Oct 11 22:07:35 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setNote: table data (rows) will not be exportedAbout 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 SECAbout 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 statisticsExport terminated successfully without warnings.从导出记录中可以看到，此时T表中的数据没有被导出。此时转储文件中仅仅记录了sec用户下的结构。3.使用生成的转储文件完成向另外一个用户secooler的导入ora10g@secdb /home/oracle$ imp secooler/secooler file=sec.dmp full=yImport: Release 10.2.0.3.0 - Production on Mon Oct 11 22:10:19 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport file created by EXPORT:V10.02.01 via conventional pathWarning: the objects were exported by SEC, not by youimport done in AL32UTF8 character set and UTF8 NCHAR character set. importing SEC's objects into SECOOLERImport terminated successfully without warnings.4.验证导入结果sys@ora10g> conn secooler/secoolerConnected.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 NUMBERDATA_OBJECT_ID                       NUMBEROBJECT_TYPE                          VARCHAR2(19)CREATED                     NOT NULL DATELAST_DDL_TIME               NOT NULL DATETIMESTAMP                            VARCHAR2(19)STATUS                               VARCHAR2(7)TEMPORARY                            VARCHAR2(1)GENERATED                            VARCHAR2(1)SECONDARY                            VARCHAR2(1)secooler@ora10g> select count(*) from t;COUNT(*)----------0OK，表T的结构已经完成迁移，注意不包含数据，这也是我们的目的。5.小结这里仅以T表为例，展示了Schema结构的迁移过程，从整个导出和导入过程中可以看到，我们完成了原有sec用户下所有的数据库对象结构向secooler用户的迁移。Good luck.secooler10.10.11-- The End --