一 描述

将官档中exp实验一一转为自己的内容.

user只有dba角色或exp_full_database角色才能将数据库全部导出.下面的例子将全库数据导出到dba.dmp文件.

二 操作环境

OS info

windowsserver2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL> col value for a50
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.0.1.1.1

20 rows selected.

SQL>

other

脚本文件存储目录:C:\oracle\product\9.2.0\db_1\db_script

三 过程设计

3.1 exp参数文件parameter_full.dat的内容
userid=scott
FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y

3.2 exp命令
> exp PARFILE=params.dat

3.3 exp日志信息

四 详细步骤操作

4.1 权限授予

C:\oracle\product\9.2.0\db_1\db_script>set ORACLE_SID=testc

C:\oracle\product\9.2.0\db_1\db_script>sqlplus "/ as sysdba

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Aug 21 15:57:25 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> grant exp_full_database to scott;

Grant succeeded.

SQL>

4.2 创建parameter_full.dat文件(略)

4.3 执行exp操作

C:\oracle\product\9.2.0\db_1\db_script>exp parfile=parameter_full.dat

Export: Release 9.0.1.1.1 - Production on Tue Aug 21 16:01:00 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions    ----------> it gets stuck over here

4.4 导出操作停住不动,按照http://www.dbforums.com/oracle/1630336-exporting-object-type-definitions-got-stuck-during-export.html 提供的解决方式编译失效objects.(中间可能涉及停库与起库)

C:\oracle\product\9.2.0\db_1\db_script>sqlplus "/ as sysdba

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Aug 21 16:27:11 2012

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> @?/rdbms/admin/utlrp

PL/SQL procedure successfully completed.

SQL>

4.5 重做exp导出操作

C:\oracle\product\9.2.0\db_1\db_script>exp parfile=parameter_full.dat

...

. about to export AURORA$ORB$UNAUTHENTICATED's tables via Conventional Path ...
. about to export WKSYS's tables via Conventional Path ...
EXP-00079: Data in table "WK$ATTRIBUTE" is protected. Conventional path may only be exporting partia
. . exporting table                   WK$ATTRIBUTE          0 rows exported
EXP-00079: Data in table "WK$ATTR_MAPPING" is protected. Conventional path may only be exporting par
. . exporting table                WK$ATTR_MAPPING          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     WK$CHARSET         57 rows exported
EXP-00079: Data in table "WK$CRAWLER_CONFIG" is protected. Conventional path may only be exporting p
. . exporting table              WK$CRAWLER_CONFIG          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table      WK$CRAWLER_CONFIG_DEFAULT         33 rows exported
EXP-00079: Data in table "WK$CRAWLER_SCHED" is protected. Conventional path may only be exporting pa
. . exporting table               WK$CRAWLER_SCHED          0 rows exported
EXP-00079: Data in table "WK$CRAWLER_STAT" is protected. Conventional path may only be exporting par
. . exporting table                WK$CRAWLER_STAT          0 rows exported
EXP-00079: Data in table "WK$DATA_SOURCE" is protected. Conventional path may only be exporting part
. . exporting table                 WK$DATA_SOURCE          0 rows exported
EXP-00079: Data in table "WK$GROUP_DS_MAPPING" is protected. Conventional path may only be exporting
. . exporting table            WK$GROUP_DS_MAPPING          0 rows exported
. . exporting table                    WK$INSTANCE          0 rows exported
. . exporting table                  WK$INST_ADMIN          0 rows exported

....

. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.

C:\oracle\product\9.2.0\db_1\db_script>

(注:EXP-00079 由于conventional path方式导出.数据表部分会加锁,导致只能导出部分表.解决方式为全库导出时添加directory=y参数.)

4.6 解决EXP-00079错误使用的参数文件parameter_full_d.dat的内容

userid=scott
FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y
direct=y
log=dba.log

4.7 第三次重做exp导出操作(略)

4.8 查看导出日志
Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Direct Path ...
. . exporting table            AQ$_INTERNET_AGENTS          0 rows exported
. . exporting table       AQ$_INTERNET_AGENT_PRIVS          0 rows exported
Table DEF$_AQCALL will be exported in conventional path.
. . exporting table                    DEF$_AQCALL          0 rows exported
Table DEF$_AQERROR will be exported in conventional path.
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
... ...

... ...

. . exporting table               DEF$_DEFAULTDEST          0 rows exported

. . exporting table                  TESTLOAD_CLOB          9 rows exported
. 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 without warnings.

(注:正常,无错误警告产生)

五 个人总结

由于此档操作环境为windows+9.0环境.做全库导出时遇到两个问题

1.做exp全库导出时由于object的失效,致使exp操作hang住不动.查询无效object可通过 dba_objects 数据字典.利用详细步骤中的重新编译全部object解决hang问题.

2.全库导出时exp-00079错误可以通过direct=y 参数解决

六 资料参考引用

Oracle9i Database Utilities
Release 1 (9.0.1)
Part Number A90192-01

http://www.dbforums.com/oracle/1630336-exporting-object-type-definitions-got-stuck-during-export.html