一 描述

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

具有exp_full_database权限的用户可以导出其他用户下面的表。不具有exp_full_database权限的用户仅能导出本身表跟表相关的objects.

exp的表模式导出中并不包含cluster table定义，如果用户中有相应的cluster定义的表，导出的dmp文件中的表定义信息也只为非cluster表定义。

exp的表模式下分三个小实验:

Example 1: DBA Exporting Tables for Two Users
Example 2: User Exports Tables That He Owns
Example 3: Using Pattern Matching to Export Various Tables

二 操作环境

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参数文件内容
3.1.1 parameter_table1.dat的内容,scott用户的emp表与hr用户的locations表。
userid=scott
FILE=expdat.dmp
TABLES=(scott.emp,hr.locations)
GRANTS=y
INDEXES=y

3.1.2 parameter_table2.dat的内容，hr用户无exp_full_database权限，因此只能导自己的表。
userid=hr
FILE=hr_tables.dmp
TABLES=(locations,jobs)
ROWS=y
COMPRESS=y

3.1.3 parameter_table3.dat的内容，使用通配符选出符合条件的表。
userid=scott
FILE=misc.dmp
TABLES=(scott.%P%,hr.%,scott.%S%)


3.2 exp命令
3.2.1
> exp  PARFILE=params_table1.dat
3.2.2
> exp  PARFILE=params_table2.dat
3.2.3
> exp  PARFILE=params_table3.dat

3.3 exp日志信息

四 详细步骤操作

4.1 Example 1: DBA Exporting Tables for Two Users
4.1.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 19:39:08 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.1.2 创建parameter_table1.dat文件(略)
userid=scott
FILE=expdat.dmp
TABLES=(scott.emp,hr.locations)
GRANTS=y
INDEXES=y
4.1.3 执行exp操作
C:\oracle\product\9.2.0\db_1\db_script>exp parfile=parameter_table1.dat

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 19:43:06 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 tables via Conventional Path ...
. . exporting table                            EMP          6 rows exported
Current user changed to HR
. . exporting table                      LOCATIONS         23 rows exported
Export terminated successfully without warnings.

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

4.2 Example 2: User Exports Tables That He Owns
4.2.1 权限授予(无操作)
4.2.2 创建parameter_table2.dat文件(略)
userid=hr
FILE=hr_tables.dmp
TABLES=(locations,jobs)
ROWS=y
COMPRESS=y

4.2.3 执行exp操作
C:\oracle\product\9.2.0\db_1\db_script>exp parfile=parameter_table2.dat

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 19:44:05 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 tables via Conventional Path ...
. . exporting table                      LOCATIONS         23 rows exported
. . exporting table                           JOBS         19 rows exported
Export terminated successfully without warnings.

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

4.3 Example 3: Using Pattern Matching to Export Various Tables
4.3.1 权限授予
4.3.2 创建parameter_table3.dat文件(略)
userid=scott
FILE=misc.dmp
TABLES=(scott.%P%,hr.%,scott.%S%)

4.3.3 执行exp操作
C:\oracle\product\9.2.0\db_1\db_script>exp parfile=parameter_table3.dat

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 19:45:24 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 tables via Conventional Path ...
. . 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                           PROJ         20 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
Current user changed to HR
. . 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 table                      COUNTRIES         25 rows exported
Current user changed to SCOTT
. . exporting table                          BONUS          0 rows exported
. . exporting table                      CUSTOMERS          2 rows exported
. . exporting table                         ORDERS          2 rows exported
. . exporting table                       SALGRADE          5 rows exported
. . exporting table                       TESTLOAD         20 rows exported
. . exporting table                  TESTLOAD_CLOB          9 rows exported
Export terminated successfully without warnings.

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

五 个人总结

如果导出表模式的参数文件中这样写:

>exp SYSTEM/password TABLES=(a, scott.b, c, mary.d)

则导出的a表为执行用户system的表,c表为其前一个schema scott的表. 注意导出顺序.

其他需要注意的导出其他用户下的表是否具有exp_full_database权限.

六 资料参考引用

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