DISABLE VALIDATE与SQL*Loader
<<Oracle 11gR2 SQL Language Reference>>DISABLE VALIDATE disables the constraint and drops the index on the constraint,
but keeps the constraint valid. This feature is most useful in data warehousing
situations, because it lets you load large amounts of data while also saving space
by not having an index. This setting lets you load data from a nonpartitioned table
into a partitioned table using the exchange_partition_subpart clause of the
ALTER TABLE statement or using SQL*Loader. All other modifications to the table
(inserts, updates, and deletes) by other SQL statements are disallowed.
如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据” 努力召唤各路高手关注 请具体给出原文地址 <<Online Document>>
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#i1002349
<<PDF>>
Oracle® Database SQL Language Reference 11g Release 2 (11.2) E41084-02 July 2013
8 Common SQL DDL Clauses -> constraint -> DISABLE Clause -> DISABLE VALIDATE
Page 8-16 （622页/1636） 如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据”
==> 这个我没有明白， 往一个具有DISABLE VALIDATE约束的表使用SQL*LOADER加载数据，在我看来就是普通的SQL*LOADER使用 This setting lets you load data from a nonpartitioned table
into a partitioned table using the exchange_partition_subpart clause of the
ALTER TABLE statement or using SQL*Loader
这句话该怎么解读呢？SQL*Loader与设置约束DISABLE VALIDATE是什么关系？ Liu Maclean(刘相兵 发表于 2014-3-28 16:25 static/image/common/back.gif
如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据”
执行SQL*Loader会导致ORA-25128错误 数据库 11.2.0.2.0 Linux x86-64
操作系统 RHEL 5.5 x86-64
实验步骤
$ sqlplus system/oracle@192.168.2.21/orcl
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 16:49:25 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace tbs_nby datafile '/u01/oradata/orcl/tbs_nby.dbf' size 8M autoextend on next 8M maxsize 1024M;
Tablespace created.
SQL> create user nby identified by nby default tablespace tbs_nby;
User created.
SQL> grant create session,resource,unlimited tablespace to nby;
Grant succeeded.
SQL> conn nby/nby
Connected.
SQL> CREATE TABLE nby.t_part(ID number,Name varchar2(10),CONSTRAINT pk_part PRIMARY KEY(ID))
2 partition by range(id)(
3 partition p1 values less than ('11'),
4 partition p2 values less than ('21'),
5 partition p3 values less than ('31'));
Table created.
SQL> INSERT INTO t_part VALUES(1,'aaa');
1 row created.
SQL> INSERT INTO t_part VALUES(2,'aaa');
1 row created.
SQL> INSERT INTO t_part VALUES(3,'aaa');
1 row created.
SQL> INSERT INTO t_part VALUES(4,'aaa');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ALTER TABLE t_part MODIFY CONSTRAINT pk_part DISABLE VALIDATE;
Table altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ cat test.dat
11,nby
12,nby
13,nby
14,nby
$ cat test.ctl
Load DATA
INFILE 'test.dat'
BADFILE 'test.bad'
APPEND
INTO TABLE T_PART
FIELDS TERMINATED BY ","
(ID ,Name)
$ sqlldr nby/nby@192.168.2.21/orcl control=test.ctl
SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 28 16:52:08 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-643: error executing INSERT statement for table T_PART
ORA-25128: No insert/update/delete on table with constraint (NBY.PK_PART) disabled and validated
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
$
$ sqlldr nby/nby@192.168.2.21/orcl control=test.ctl direct=true
SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 28 16:52:12 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-02373: Error parsing insert statement for table NBY.T_PART.
ORA-25128: No insert/update/delete on table with constraint (NBY.PK_PART) disabled and validated
more info :
$ sqlldr control=sys.tv.ctl direct=y skip_index_maintenance=true
Username:/ as sysdba
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Mar 28 04:49:57 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-951: Error calling once/load initialization
ORA-02373: Error parsing insert statement for table SYS.TV.
ORA-25128: No insert/update/delete on table with constraint (SYS.SYS_C0012793) disabled and validated need to diag more info 文中提到的exchange partition并不难理解实现起来也简单。但实在想不出使用sql*loader加载数据后，DBMS拿什么保证数据的唯一性来对得起VALIDATE一词。DISABLE VALIDATE会导致对应索引删除，索引都没有了拿什么来实施约束。 求高手们关注 唉，沉底了
这个实验来其实自于一道1z0-052的试题，题目如下
Examine the following statement that is used to modify the constraint on the SALES table:
SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE;
Which three statements are true regarding the above command? (Choose three.)
A.The constraint remains valid.
B.The index on the constraint is dropped.
C.It allows the loading of data into the table using SQL *Loader.
D.New data conforms to the constraint, but existing data is not checked.
E.It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.
Answer: ABC
其中的C选项在Oracle SQL Reference中也被提及，详见楼上
这个到底应该怎么实现呢？
页:
[1]