一 描述

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

exp的用户模式常用来备份一个或多个数据库用户.此档中的scott环境为前篇SQLLDR系列做基础.

二 操作环境

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_user.dat的内容
userid=scott
FILE=scott.dmp
OWNER=scott,hr
GRANTS=y
ROWS=y
COMPRESS=y

3.2 exp命令
> exp  PARFILE=params_user.dat

3.3 exp日志信息

四 详细步骤操作

4.1 权限授予
C:\oracle\product\9.2.0\db_1\db_script>set nls_lang=american_america.zhs16gbk

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 Wed Aug 22 16:37:49 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> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

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

4.2 创建parameter_user.dat文件(略)

userid=scott
FILE=scott.dmp
OWNER=scott,hr
GRANTS=y
ROWS=y
COMPRESS=y

4.3 执行exp操作

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

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 16:39:26 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 specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting foreign function library names for user HR
. exporting object type definitions for user SCOTT
. exporting object type definitions for user HR
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                      CUSTOMERS          2 rows exported
. . exporting table                           DEPT          6 rows exported
. . exporting table                       DEPT_P10          4 rows exported
. . exporting table                     DEPT_P10_1          0 rows exported
. . exporting table                     DEPT_P10_2          1 rows exported
. . exporting table                     DEPT_P10_3          0 rows exported
. . exporting table                     DEPT_P10_4          3 rows exported
. . exporting table                       DEPT_P2W          3 rows exported
. . exporting table                       DEPT_P2X          1 rows exported
. . exporting table                        DEPT_P3          0 rows exported
. . exporting table                        DEPT_P4          1 rows exported
. . exporting table                        DEPT_P5          4 rows exported
. . exporting table                        DEPT_P6          5 rows exported
. . exporting table                        DEPT_P7          2 rows exported
. . exporting table                        DEPT_P8          4 rows exported
. . exporting table                       DUP_TEST          0 rows exported
. . exporting table                            EMP          6 rows exported
. . exporting table                       LINEITEM
. . exporting partition                        SHIP_Q1          3 rows exported
. . exporting partition                        SHIP_Q2          3 rows exported
. . exporting partition                        SHIP_Q3          0 rows exported
. . exporting partition                        SHIP_Q4          0 rows exported
. . exporting table                         ORDERS          2 rows exported
. . exporting table                           PROJ         20 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                        TAB_P11          2 rows exported
. . exporting table                      TAB_P11_1          2 rows exported
. . exporting table                      TAB_P11_2          2 rows exported
. . exporting table                         TAB_P9          6 rows exported
. . exporting table                       TESTLOAD         20 rows exported
. . exporting table                  TESTLOAD_CLOB          9 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table                      COUNTRIES         25 rows exported
. . exporting table                    DEPARTMENTS         27 rows exported
. . exporting table                      EMPLOYEES        107 rows exported
. . exporting table                           JOBS         19 rows exported
. . exporting table                    JOB_HISTORY         10 rows exported
. . exporting table                      LOCATIONS         23 rows exported
. . exporting table                        REGIONS          4 rows exported
. 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 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.

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

五 个人总结

按用户导出,需要注意的地方为参数文件中userid指定的用户必须要有exp_full_database权限才能导出其他用户.

六 资料参考引用

Oracle9iDatabase Utilities
Release 1 (9.0.1)
Part Number A90192-01