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

Here is the oracle version i am using.

Oracle9i Enterprise Edition Release - Production
PL/SQL Release - Production
CORE       Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - 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
Messages: 1848
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:
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
Messages: 283
Registered: April 2008
Senior Member
Thank you.
Next Topic: Insert data in diff schema
Goto Forum:

Current Time: Thu Aug 24 02:01:58 CDT 2017

Total time taken to generate the page: 0.11839 seconds