data dump for sequence

最新推荐文章于 2024-08-30 18:11:39 发布

原创最新推荐文章于 2024-08-30 18:11:39 发布 · 790 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#data dump

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了使用Oracle的expdp和impdp工具进行数据库对象和数据的导出与导入过程，特别关注了序列号的变化情况。通过实际操作演示，展示了在不同导出选项下（仅元数据或包含数据）对序列号的影响，为数据库迁移和维护提供了实用指南。

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:33:59 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select table_name from user_tables;

no rows selected

SQL> create table t (id number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID
----------
1

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
32

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
24

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux26 imp]$ expdp system/oracle schemas=exp1 directory=imp content=metadata_only dumpfile=exp1.dmp logfile=exp1.log

Export: Release 11.2.0.1.0 - Production on Tue Sep 3 19:38:15 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=exp1 directory=imp content=metadata_only dumpfile=exp1.dmp logfile=exp1.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/imp/exp1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:39:17

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:39:23 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID
----------
         1
         2

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
32

SQL> select seq_test.nextval from dual; ###做完expdp 序列的变化 content=metadata_only

NEXTVAL
----------
32

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
42

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
33

Export: Release 11.2.0.1.0 - Production on Tue Sep 3 19:41:31 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=exp1 directory=imp content=metadata_only dumpfile=exp2.dmp logfile=exp2.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/imp/exp2.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:42:14

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:42:21 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
42

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
42

SQL> insert into t values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID
----------
         1
         2
         3

SQL> select seq_test.currval from dual;

CURRVAL
----------
42

Export: Release 11.2.0.1.0 - Production on Tue Sep 3 19:43:57 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=exp1 directory=imp content=data_only dumpfile=exp3.dmp logfile=exp3.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "EXP1"."T" 5.015 KB 3 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/imp/exp3.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:44:06

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:44:13 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
52

SQL> select seq_test.nextval from dual; ###做完expdp 序列的变化 content=data_only

NEXTVAL
----------
43

SQL> conn / as sysdba
Connected.
SQL> drop user exp1 cascade;

User dropped.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux26 imp]$ impdp system/oracle directory=imp dumpfile=exp2.dmp logfile=exp2imp.log

Import: Release 11.2.0.1.0 - Production on Tue Sep 3 19:47:17 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=imp dumpfile=exp2.dmp logfile=exp2imp.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 19:47:22

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:47:32 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t;

no rows selected

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
42

SQL> select seq_test.nextval from dual; ###做完impdp 序列的变化 content=metadata_only

NEXTVAL
----------
42

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux26 imp]$ impdp system/oracle directory=imp dumpfile=exp3.dmp logfile=exp3imp.log

Import: Release 11.2.0.1.0 - Production on Tue Sep 3 19:48:58 2013

[oracle@linux26 imp]$ sqlplus exp1/exp1

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 3 19:49:12 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t;

        ID
----------
         1
         2
         3

SQL> select LAST_NUMBER from user_sequences;

LAST_NUMBER
-----------
52

SQL> select seq_test.nextval from dual; ###做完impdp 序列的变化 content=data_only

NEXTVAL
----------
43

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

expdp 导出只要涉及metadata都会影响序列的变化，impdp在导入metadata的时候已经导入，导入数据不会影响序列。