exp导出数据库的实际操作

[oracle@odb01 ~]$  exp \''sys/"qwe!123"'@orcl as sysdba\' full=y file=/home/oracle/backup/orcl_full.dmp

使用oracle用户运行，不是在sqlplus里运行。

因为密码包含特殊字符，密码前后需要一对双引号"qwe!123"

用户名/密码前后一对单引号 'sys/"qwe!123"'

'用户名/"密码"'@实例名 as sysdba前后也要加上一对单引号，并用\转义，如下

\''sys/"qwe!123"'@orcl as sysdba\'

运行后效果如下

EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                             XS          2 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                        YONGHUI          1 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

成功完成了，但是有很多EXP-00091的警告，查了一下，nls_lang参数和字符集不匹配的原因

解决方法有三种：

首先，是调整nls_lang为正确的参数取值；其次，是使用statistics=none，显示的不进行统计量导出；最后就是直接使用Data Pump工具。

首先查看服务端的nls_lang环境变量

SQL> select * from nls_database_parameters;

Nls_lang环境变量的参数格式包括nls_language、nls_territory和nls_characterset

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN


NLS_TERRITORY
AMERICA


NLS_CURRENCY
$




PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA


NLS_NUMERIC_CHARACTERS
.,


NLS_CHARACTERSET
AL32UTF8

再查看客户端的nls_lang环境变量

win:  echo %NLS_LANG%

linux:  env|grep NLS_LANG

设置方法

win: set NLS_LANG=american_america.al32utf8

linux: export NLS_LANG=american_america.al32utf8

重新运行exp命令，就不会报错了

. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.

第二种方法，就是exp后面跟参数

statistics=none