oracle之EXP匯出表空間錯誤解決

匯出表空間
第一 以DBA的許可權登入
第二 檢查表空間是否自我包容，檢查結果將被放到檢視transport_set_violations
SQL> execute dbms_tts.transport_set_check('USERS',TRUE);

第三 查詢檢視
SQL> select * from transport_set_violations;

no rows selected
如果查詢結果為0，說明表空間是自我包容的

第四 把要被匯出的表空間設定成只讀
SQL> alter tablespace users read only;

第五 匯出表空間（transport_tablespace=y 表示啟用表空間匯出,但是無法匯出資料，所以要想匯出資料就不要新增該引數 constraints=y 表示匯出約束）
C:\Documents and Settings\Administrator> exp 'sys/admin as sysdba'  tablespaces=users file=D:\Oracle\ffdata\newtabsp.dmp constraints=y

Export: Release 11.2.0.1.0 - Production on 星期三 2月 22 19:33:55 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

連線到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
伺服器使用 AL32UTF8 字符集 (可能的字符集轉換)
注: 將不匯出表資料 (行)
即將匯出可傳輸的表空間後設資料...
對於表空間 USERS...
. 正在匯出簇定義
. 正在匯出表定義
. . 正在匯出表                            DEPT
EXP-00091: 正在匯出有問題的統計資訊。

說明：當匯出統計資訊的時候，如果客戶端字符集和伺服器端字符集不匹配，就會產生這種，解決辦法如下：
首先：檢視伺服器端字符集
SQL> select * from v$nls_parameters where parameter=
  2  'NLS_CHARACTERSET';

PARAMETER         VALUE
-------------     ---------------------------------------

NLS_CHARACTERSET  AL32UTF8

其次：設定客戶端字符集，使之與伺服器端字符集匹配EX

C:\Documents and Settings\Administrator>SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8

再次進行匯出
C:\Documents C:\Documents and Settings\Administrator> exp 'sys/admin as sysdba' transport_tab
lespace=y tablespaces=users file=D:\Oracle\ffdata\newtabsp.dmp constraints=y

Export: Release 11.2.0.1.0 - Production on Wed Feb 22 19:41:46 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           DEPT
. . exporting table                            EMP
. . exporting table                       SALGRADE
. . exporting table                  PURCHASEORDER
. . exporting table                 CATEGORIES_TAB
. . exporting table                       SID_INFO
. . exporting table                   QUALITY_INFO
. . exporting table                     CALL_DIREC
. . exporting table                      PARA_DATA
. . exporting table                      PARA_INFO
. . exporting table                    PARA_DETAIL
. . exporting table                    SIGNAL_INFO
. . exporting table                        APP_VER
. . exporting table                     PHONE_DATA
. . exporting table                       USERINFO
. . exporting table                       ROLEINFO
. . exporting table                         REGION
. . exporting table                      BASE_CELL
. . exporting table                       COMP_SUG
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

第六 恢復資料庫表空間可讀寫
SQL> alter tablespace users read write;

Tablespace altered.