一 描述

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

导出分区表时,可以指定分区表的分区或子分区进行导出.

exp分区表分以下三个小实验:

Example 1: Exporting a Table Without Specifying a Partition
Example 2: Exporting a Table with a Specified Partition
Example 3: Exporting a Composite Partition

二 操作环境

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_part1.dat的内容,scott用户的lineitem表为range分区表,导出时不指定分区参数,则导出全表。
userid=scott
TABLES=(lineitem)
ROWS=y

3.1.2 parameter_part2.dat的内容，导出scott用户lineitem表的ship_q1分区数据.
userid=scott
TABLES=(lineitem:ship_q1)
ROWS=y

3.1.3 parameter_part3.dat的内容，导出复合分区.假设emp表包含两个分区m和z.且都是复合分区,m有两个子分区sp1和sp2,z有两个子分区sp3和sp4
userid=scott
TABLES=(emp:m,emp:sp4)
ROWS=y

(注:此过程未做详细操作)


3.2 exp命令
3.2.1
> exp  PARFILE=params_part1.dat
3.2.2
> exp  PARFILE=params_part2.dat
3.2.3
> exp  PARFILE=params_part3.dat

3.3 exp日志信息

四 详细步骤操作

4.1 Example 1: Exporting a Table Without Specifying a Partition
4.1.1 权限授予(无)
4.1.2 创建parameter_table1.dat文件(略)
userid=scott
TABLES=(lineitem)
ROWS=y

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

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 20:46:41 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                       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
Export terminated successfully without warnings.

C:\oracle\product\9.2.0\db_1\db_script>
C:\oracle\product\9.2.0\db_1\db_script>dir
 驱动器 C 中的卷没有标签。
 卷的序列号是 5899-E11B

 C:\oracle\product\9.2.0\db_1\db_script. 的目录

2012-08-22  20:46              .
2012-08-22  20:46              ..
2012-08-21  17:09        85,348,352 dba.dmp
2012-08-21  17:09            31,978 dba.log
2012-08-22  20:46             8,192 expdat.dmp
2012-08-22  19:44            14,336 hr_tables.dmp
2012-08-22  19:45            69,632 misc.dmp
2012-08-22  16:25                71 parameter_full.dat
2012-08-21  17:08                75 parameter_full_d.dat
2012-08-22  20:46                39 parameter_part1.dat
2012-08-22  19:41                83 parameter_table1.dat
2012-08-22  19:42                74 parameter_table2.dat
2012-08-22  19:42                62 parameter_table3.dat
2012-08-22  16:53                74 parameter_user.dat
2012-08-22  16:53            86,016 scott.dmp
2012-08-21  15:56             1,750 sqlnet.log
              14 个文件     85,560,734 字节
               2 个目录 28,186,898,432 可用字节

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

4.2 Example 2: Exporting a Table with a Specified Partition
4.2.1 权限授予(无)
4.2.2 创建parameter_table2.dat文件(略)
userid=scott
TABLES=(lineitem:ship_q1)
ROWS=y

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

Export: Release 9.0.1.1.1 - Production on Wed Aug 22 20:55:03 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                       LINEITEM
. . exporting partition                        SHIP_Q1          3 rows exported
Export terminated successfully without warnings.

C:\oracle\product\9.2.0\db_1\db_script>
C:\oracle\product\9.2.0\db_1\db_script>dir
 驱动器 C 中的卷没有标签。
 卷的序列号是 5899-E11B

 C:\oracle\product\9.2.0\db_1\db_script. 的目录

2012-08-22  20:54              .
2012-08-22  20:54              ..
2012-08-21  17:09        85,348,352 dba.dmp
2012-08-21  17:09            31,978 dba.log
2012-08-22  20:55             6,144 expdat.dmp
2012-08-22  19:44            14,336 hr_tables.dmp
2012-08-22  19:45            69,632 misc.dmp
2012-08-22  16:25                71 parameter_full.dat
2012-08-21  17:08                75 parameter_full_d.dat
2012-08-22  20:46                39 parameter_part1.dat
2012-08-22  20:54                47 parameter_part2.dat
2012-08-22  19:41                83 parameter_table1.dat
2012-08-22  19:42                74 parameter_table2.dat
2012-08-22  19:42                62 parameter_table3.dat
2012-08-22  16:53                74 parameter_user.dat
2012-08-22  16:53            86,016 scott.dmp
2012-08-21  15:56             1,750 sqlnet.log
              15 个文件     85,558,733 字节
               2 个目录 28,186,898,432 可用字节

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

(注:example2中的expdat.dmp已被无提示的覆盖)

五 个人总结

1.当参数文件中并未指定file参数时,默认导出文件名为expdat.dmp

2.导出的文件如果目录下存储相同名称文件则被覆盖,且无提示.切记并注意.

六 资料参考引用

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

分区表创建脚本参见: http://space.itpub.net/11780477/viewspace-740327