DISABLE VALIDATE与SQL*Loader

clevernby

<<Oracle 11gR2 SQL Language Reference>>

1. 
   
2. DISABLE VALIDATE  disables the constraint and drops the index on the constraint,
   
3. but keeps the constraint valid. This feature is most useful in data warehousing
   
4. situations, because it lets you load large  amounts of data while also saving space
   
5. by not having an index. This  setting lets you load data from a nonpartitioned table
   
6. into a partitioned table using the  exchange_partition_subpart  clause of the
   
7. ALTER TABLE statement or using SQL*Loader. All other modifications to the table
   
8. (inserts, updates, and deletes) by other SQL statements are disallowed.
   

复制代码

如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据”

clevernby

努力召唤各路高手关注

Liu Maclean(刘相兵

请具体给出原文地址

clevernby

<<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）

Liu Maclean(刘相兵

如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据”


==> 这个我没有明白， 往一个具有DISABLE VALIDATE约束的表使用SQL*LOADER加载数据，在我看来就是普通的SQL*LOADER使用

clevernby

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是什么关系？

clevernby

Liu Maclean(刘相兵 发表于 2014-3-28 16:25
如何实现文中所述的“使用SQL*Loader往具有DISABLE VALIDATE约束的表中加载数据”

执行SQL*Loader会导致ORA-25128错误

clevernby

数据库 11.2.0.2.0 Linux x86-64
操作系统  RHEL 5.5 x86-64
实验步骤

1. 
   
2. [oracle@ora11202 ~]$ sqlplus system/oracle@192.168.2.21/orcl
   
3. 
   
4. SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 16:49:25 2014
   
5. 
   
6. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. SQL> create tablespace tbs_nby datafile '/u01/oradata/orcl/tbs_nby.dbf' size 8M autoextend on next 8M maxsize 1024M;
    
13. 
    
14. Tablespace created.
    
15. 
    
16. SQL> create user nby identified by nby default tablespace tbs_nby;
    
17. 
    
18. User created.
    
19. 
    
20. SQL> grant create session,resource,unlimited tablespace to nby;
    
21. 
    
22. Grant succeeded.
    
23. 
    
24. SQL> conn nby/nby
    
25. Connected.
    
26. SQL> CREATE TABLE nby.t_part(ID number,Name varchar2(10),CONSTRAINT pk_part PRIMARY KEY(ID))
    
27.   2  partition by range(id)(
    
28.   3  partition p1 values less than ('11'),
    
29.   4  partition p2 values less than ('21'),
    
30.   5  partition p3 values less than ('31'));
    
31. 
    
32. Table created.
    
33. 
    
34. SQL> INSERT INTO t_part VALUES(1,'aaa');
    
35. 
    
36. 1 row created.
    
37. 
    
38. SQL> INSERT INTO t_part VALUES(2,'aaa');
    
39. 
    
40. 1 row created.
    
41. 
    
42. SQL> INSERT INTO t_part VALUES(3,'aaa');
    
43. 
    
44. 1 row created.
    
45. 
    
46. SQL> INSERT INTO t_part VALUES(4,'aaa');
    
47. 
    
48. 1 row created.
    
49. 
    
50. SQL> COMMIT;
    
51. 
    
52. Commit complete.
    
53. 
    
54. SQL> ALTER TABLE t_part MODIFY CONSTRAINT pk_part DISABLE VALIDATE;
    
55. 
    
56. Table altered.
    
57. 
    
58. SQL> quit
    
59. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    
60. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
61. [oracle@ora11202 ~]$ cat test.dat
    
62. 11,nby
    
63. 12,nby
    
64. 13,nby
    
65. 14,nby
    
66. [oracle@ora11202 ~]$ cat test.ctl
    
67. Load DATA
    
68. INFILE 'test.dat'
    
69. BADFILE 'test.bad'
    
70. APPEND
    
71. INTO TABLE T_PART
    
72. FIELDS TERMINATED BY ","
    
73. (ID ,Name)
    
74. [oracle@ora11202 ~]$ sqlldr nby/nby@192.168.2.21/orcl control=test.ctl
    
75. 
    
76. SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 28 16:52:08 2014
    
77. 
    
78. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
79. 
    
80. SQL*Loader-643: error executing INSERT statement for table T_PART
    
81. ORA-25128: No insert/update/delete on table with constraint (NBY.PK_PART) disabled and validated
    
82. SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
    
83. [oracle@ora11202 ~]$
    
84. [oracle@ora11202 ~]$ sqlldr nby/nby@192.168.2.21/orcl control=test.ctl direct=true
    
85. 
    
86. SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 28 16:52:12 2014
    
87. 
    
88. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
89. 
    
90. SQL*Loader-951: Error calling once/load initialization
    
91. ORA-02373: Error parsing insert statement for table NBY.T_PART.
    
92. ORA-25128: No insert/update/delete on table with constraint (NBY.PK_PART) disabled and validated
    

复制代码

Liu Maclean(刘相兵

more info :
[oracle@mlab2 parnassus_dbinfo_DB_20140328044616]$ 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

Liu Maclean(刘相兵

need to diag more info

clevernby

文中提到的exchange partition并不难理解实现起来也简单。但实在想不出使用sql*loader加载数据后，DBMS拿什么保证数据的唯一性来对得起VALIDATE一词。DISABLE VALIDATE会导致对应索引删除，索引都没有了拿什么来实施约束。

clevernby

求高手们关注

clevernby

唉，沉底了
这个实验来其实自于一道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中也被提及，详见楼上
这个到底应该怎么实现呢？