在某些情況下，我們僅需要一套完整的資料庫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 --

【exp】使用exp工具的rows選項完成結構遷移

相關文章