exp oracle 表空间,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 SettingsAdministrator> exp 'sys/admin as sysdba'  tablespaces=users file=D:Oracleffdatanewtabsp.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 SettingsAdministrator>SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8

再次进行导出

C:Documents C:Documents and SettingsAdministrator> exp 'sys/admin as sysdba' transport_tab

lespace=y tablespaces=users file=D:Oracleffdatanewtabsp.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.