ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > oracle 10g expdp test after primary key rely disable

oracle 10g expdp test after primary key rely disable

原创 Linux操作系统 作者：is.x 时间：2011-09-18 14:48:32 0 删除 编辑

1． 创建用户和表

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table t (num number,num1 number,num2 number);

Table created.

SQL> insert into t values (1,2,3);

1 row created.

SQL> insert into t values (2,3,4);

1 row created.

SQL> insert into t values (3,4,5);

1 row created.

SQL> insert into t values (4,5,6);

1 row created.

SQL> insert into t values (5,6,7);

1 row created.

SQL> commit;

Commit complete.

 

SQL> select * from t;

 

       NUM       NUM1       NUM2

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

         1          2          3

         2          3          4

         3          4          5

         4          5          6

         5          6          7

 

2． 创建主键约束并导出

SQL> alter table t add constraint num1_pk primary key(num1);

Table altered.

 

oracle@/oracle>expdp test/test directory=dump_dir dumpfile=test.dmp logfile=test.log tables=t

Export: Release 10.2.0.3.0 - Production on Monday, 02 August, 2010 14:43:07

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dump_dir dumpfile=test.dmp logfile=test.log tables=t

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

. . exported "TEST"."T"                                  5.570 KB       5 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /oracle/test.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 14:43:15

 

oracle@/oracle>impdp test/test directory=dump_dir dumpfile=test.dmp logfile=test.log sqlfile=test1.sql

Import: Release 10.2.0.3.0 - Production on Monday, 02 August, 2010 14:48:09

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** directory=dump_dir dumpfile=test.dmp logfile=test.log sqlfile=test1.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 14:48:13

 

oracle@/oracle>cat test1.sql

-- CONNECT TEST

-- new object type path is: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "TEST"."T"

   (    "NUM" NUMBER,

        "NUM1" NUMBER,

        "NUM2" NUMBER

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSTEM" ;

 

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX

CREATE UNIQUE INDEX "TEST"."NUM1_PK" ON "TEST"."T" ("NUM1")

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSTEM" PARALLEL 1 ;

 

  ALTER INDEX "TEST"."NUM1_PK" NOPARALLEL;

 

-- new object type path is: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ALTER TABLE "TEST"."T" ADD CONSTRAINT "NUM1_PK" PRIMARY KEY ("NUM1")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSTEM"  ENABLE;

 

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

DECLARE IND_NAME VARCHAR2(60);

   IND_OWNER VARCHAR2(60);

  BEGIN

   DELETE FROM "SYS"."IMPDP_STATS";

   IND_NAME := 'NUM1_PK';   IND_OWNER := 'TEST';

   INSERT INTO "SYS"."IMPDP_STATS" (type, version, flags, c1, c2, c3, c5,

               n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, d1)

       VALUES ('I', 4, 0, IND_NAME, NULL, NULL, 'TEST', 5, 1, 5, 1, 1, 1, 0, 5, NULL, NULL, NULL, NULL, TO_DATE('2010-08-02 14:40:10', 'YYYY-MM-DD:HH24:MI:SS'));

 

   DBMS_STATS.IMPORT_INDEX_STATS( '"' || ind_owner || '"', '"' || ind_name || '"', NULL, '"IMPDP_STATS"', NULL, '"SYS"');

   DELETE FROM "SYS"."IMPDP_STATS";

 END;

/

 

3． 创建rely disable主键约束并导出

SQL> alter table t drop constraint num1_pk ;

Table altered.

SQL> alter table t add constraint num2_pk primary key(num2) rely disable;

Table altered.

 

oracle@/oracle>expdp test/test directory=dump_dir dumpfile=test2.dmp logfile=test2.log tables=t       

Export: Release 10.2.0.3.0 - Production on Monday, 02 August, 2010 14:49:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** directory=dump_dir dumpfile=test2.dmp logfile=test2.log tables=t

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "TEST"."T"                                  5.570 KB       5 rows

Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /oracle/test2.dmp

Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 14:49:29

 

oracle@/oracle>impdp test/test directory=dump_dir dumpfile=test2.dmp logfile=test2.log sqlfile=test2.sql

Import: Release 10.2.0.3.0 - Production on Monday, 02 August, 2010 14:50:18

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** directory=dump_dir dumpfile=test2.dmp logfile=test2.log sqlfile=test2.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 14:50:21

 

oracle@/oracle>cat test2.sql

-- CONNECT TEST

-- new object type path is: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "TEST"."T"

   (    "NUM" NUMBER,

        "NUM1" NUMBER,

        "NUM2" NUMBER

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSTEM" ;

 

 

4． 结论

Oracle 10g在使用expdp导出表数据时，如果有rely disable的主键，oracle是不会导出的。

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/20750200/viewspace-707812/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 数据库实例异常终止 ORA-00600 [kcbget_37]
下一篇： 关于Oracle字符集简单说明
请登录后发表评论 登录
全部评论
is.x

注册时间：2011-04-27

  • 博文量
    73
  • 访问量
    293999

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593