Home » SQL & PL/SQL » SQL & PL/SQL » Disable validate (oracle9i)
Disable validate [message #325790] Sun, 08 June 2008 20:10 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello,

Here is the oracle version i am using.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


I have good understanding of enable novalidate option in constraints. But i am not able to understand where/when exactly disable validate option is useful?

SQL> create table temp(no number);

Table created.

SQL>  alter table temp add primary key(no) disable validate;

Table altered.

SQL> insert into temp values(1);
insert into temp values(1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.SYS_C003052)
disabled and validated



When we disable the constraint with disable validate option, it would not let us to insert/update/delete the data in table. But what circumstances, it will be useful??

I would be pleased if any one could answer for this.
Re: Disable validate [message #325792 is a reply to message #325790] Sun, 08 June 2008 21:33 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
It is answered in the documentation, in http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2062567:
Quote:
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_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.
Re: Disable validate [message #325900 is a reply to message #325792] Mon, 09 June 2008 07:56 Go to previous message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thank you.
Previous Topic: views could not be updated :no-key reserved table
Next Topic: How to disable implicit commit in Oracle
Goto Forum:
  


Current Time: Wed Dec 11 22:17:27 CST 2024