导出oracle sequences,利用数据泵只导出序列

Oracle的数据泵导入导出功能比原有的导入导出工具(exp/imp)功能强很多。

利用数据泵我们可以只导出某一特定对象类型，并且可以指定过滤条件。这个功能的实现主要依靠expdp的include参数。联机文档对于参数的功能描述如下：

INCLUDE

Default: none

Purpose

Enables you to filter the metadata that is exported by specifying objects and object

types for the current export mode. The specified objects and all their dependent objectsare exported. Grants on these objects are also exported.

Syntax and Description

INCLUDE = object_type[:name_clause] [, ...]

Only object types explicitly specified in INCLUDE statements, and their dependent objects, are exported. No other object types, including the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported.

To see a list of valid object type path names for use with the INCLUDE parameter, youcan query the following views: DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.

The name_clause is optional. It allows fine-grained selection of specific objects within an object type. It is a SQL expression used as a filter on the object names of the type. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT). The optional name clause must be separated from the object type with a colon and

enclosed in double quotation marks, because single-quotation marks are required to delimit the name strings.

Oracle recommends that INCLUDE statements be placed in a parameter file; otherwise you might have to use operating system-specific escape characters on the command line before quotation marks. See Use of Quotation Marks On the Data Pump Command Line on page 2-6.

For example, suppose you have a parameter file named hr.par with the following

content:

SCHEMAS=HR

DUMPFILE=expinclude.dmp

DIRECTORY=dpump_dir1

LOGFILE=expinclude.log

INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

INCLUDE=PROCEDURE

INCLUDE=INDEX:"LIKE 'EMP%'"

You could then use the hr.par file to start an export operation, without having to

enter any other parameters on the command line:

> expdp hr/hr parfile=hr.par

Restrictions

■ The INCLUDE and EXCLUDE parameters are mutually exclusive.

■ Grants on objects owned by the SYS schema are never exported.

Example

The following example performs an export of all tables (and their dependent objects)in the hr schema:

>expdp hr/hr INCLUDE=TABLE DUMPFILE=dpump_dir1:exp_inc.dmp NOLOGFILE=y

SQL> show user

USER 为 "SYS"

SQL> conn admin/admin

已连接。

SQL> select sequence_name from user_sequences;

未选定行

SQL> create sequence s;

序列已创建。

SQL> select s.nextval from dual;

NEXTVAL

----------

SQL> select s.nextval from dual;

NEXTVAL

----------

SQL> select s.nextval from dual;

NEXTVAL

----------

SQL> create sequence s1;

序列已创建。

SQL> create sequence s2;

序列已创建。

SQL> create sequence s3;

序列已创建。

SQL> create sequence s4;

序列已创建。

SQL> create sequence a;

序列已创建。

SQL> create sequence a1;

序列已创建。

SQL> create sequence a2;

序列已创建。

SQL> create sequence a3;

序列已创建。

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME

------------------------------

已选择9行。

下面我们只讲序列前缀为S的序列导出。

C:\>type parfile.par

userid=admin/admin

dumpfile=test:sequence.dp

logfile=sequence.log

include=sequence:"like 'S%'"

C:\>expdp parfile=parfile.par

Export: Release 10.2.0.1.0 - Production on 星期二, 29 12月, 2009 11:21:18

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

自动启用 FLASHBACK 以保持数据库完整性。

启动 "ADMIN"."SYS_EXPORT_SCHEMA_01": parfile=parfile.par

正在使用 BLOCKS 方法进行估计...

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 0 KB

处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE

已成功加载/卸载了主表 "ADMIN"."SYS_EXPORT_SCHEMA_01"

******************************************************************************

ADMIN.SYS_EXPORT_SCHEMA_01 的转储文件集为:

E:\DATAPUMP\SEQUENCE.DP

作业 "ADMIN"."SYS_EXPORT_SCHEMA_01" 已于 11:21:21 成功完成

删除原有的序列

SQL> drop sequence s;

序列已删除。

SQL> drop sequence s1;

序列已删除。

SQL> drop sequence s2;

序列已删除。

SQL> drop sequence s3;

序列已删除。

SQL> drop sequence s4;

序列已删除。

SQL> drop sequence a;

序列已删除。

SQL> drop sequence a1;

序列已删除。

SQL> drop sequence a2;

序列已删除。

SQL> drop sequence a3;

序列已删除。

SQL> select sequence_name from user_sequences;

未选定行

导入序列

C:\>impdp userid=admin/admin dumpfile=test:sequence.dp include=sequence

Import: Release 10.2.0.1.0 - Production on 星期二, 29 12月, 2009 11:23:17

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

已成功加载/卸载了主表 "ADMIN"."SYS_IMPORT_FULL_01"

启动 "ADMIN"."SYS_IMPORT_FULL_01": userid=admin/******** dumpfile=test:sequence

.dp include=sequence

处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE

作业 "ADMIN"."SYS_IMPORT_FULL_01" 已于 11:23:19 成功完成

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME

------------------------------

SQL> select s.nextval from dual;

NEXTVAL

----------

注意我们的表数据未导出，此时导入表数据会报错。

C:\>impdp userid=admin/admin dumpfile=test:sequence.dp include=table

Import: Release 10.2.0.1.0 - Production on 星期二, 29 12月, 2009 11:38:01

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORA-39002: 操作无效

ORA-39168: 未找到对象路径 TABLE。