Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column (Oracle 12.1.0.1.0 Enterprise Edition 64bit on Windows 7 Professional)
ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604251] Thu, 26 December 2013 20:03 Go to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
I was doing some very simple table creations without any constraints and simple inserts, some complex queries using things like ora:view, ora:contains, and dbms_xmlgen.getxmltype, then dropping the tables. Now, the simplest insert into any table causes ORA-30667, as shown below. The same things did not cause a problem on 11g, so it appears to be a 12c bug, but I can't figure out what to do to get my 12c database back to normal. I am running Oracle 12.1.0.1.0 Enterprise Edition 64 bit on Windows 7 Professional. Does anybody have a clue?

The queries that I ran were related to the following thread:

https://community.oracle.com/thread/2616045?start=0&tstart=0

My 11g database ran the same code with no problem, but this is what happens with my 12c database now:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SCOTT@orcl12c> create table test (test number);

Table created.

SCOTT@orcl12c> insert into test values (1);
insert into test values (1)
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column


SCOTT@orcl12c>

[Updated on: Thu, 26 December 2013 20:09]

Report message to a moderator

Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604252 is a reply to message #604251] Thu, 26 December 2013 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
did you try cleaning out the recycle bin?
did you try stopping & restarting either the DB and/or Windows?
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604253 is a reply to message #604251] Thu, 26 December 2013 20:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Most likely a bug. Check this post.

SY.
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604254 is a reply to message #604252] Thu, 26 December 2013 21:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Blackswan,

Purging the recycle bin fixed it. Thanks!

Barbara
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604255 is a reply to message #604253] Thu, 26 December 2013 21:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

I found that post while searching before, but didn't read it, because I got the message below.

Barbara

"This Connection is Untrusted

You have asked Firefox to connect securely to forum.java.sun.com, but we can't confirm that your connection is secure.

Normally, when you try to connect securely, sites will present trusted identification to prove that you are going to the right place. However, this site's identity can't be verified.
What Should I Do?

If you usually connect to this site without problems, this error could mean that someone is trying to impersonate the site, and you shouldn't continue."
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604265 is a reply to message #604254] Fri, 27 December 2013 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara Boehmer wrote on Fri, 27 December 2013 04:05

Purging the recycle bin fixed it. Thanks!


Did you investigate and find the reason of this?


Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604276 is a reply to message #604255] Fri, 27 December 2013 04:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Barbara Boehmer wrote on Thu, 26 December 2013 22:07
Solomon,

I found that post while searching before, but didn't read it, because I got the message below.

"This Connection is Untrusted


Right, I got it too. Believe it or not, it is OTN forum link, so Oracle messed up their certificate. It is fixed now and you can follow that link.

SY.
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604277 is a reply to message #604276] Fri, 27 December 2013 04:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Solomon Yakobson wrote on Fri, 27 December 2013 05:07
It is fixed now and you can follow that link.


Oops, I spoke too soon. This happens because, for some reason, Oracle maintains two URLs for OTN forums:


https://community.oracle.com/message/11286776

and

https://forum.java.sun.com/thread/2608475

The later is the one I posted originally. Both URLs reference same certificate which is issued with the former URL website name. That's why the later URL violates certificate even though it is legid.

SY.
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604297 is a reply to message #604277] Fri, 27 December 2013 13:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

Thanks for the safe link. It looks like there are multiple things that can trigger this error, due to the bug. If anything that should have been deleted isn't, then you have to purge the recycle bin or use some other means to remove the remnants.

Barbara
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604298 is a reply to message #604297] Fri, 27 December 2013 13:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
I was going to try to post a simplified reproducible test case, but I can't seem to produce the error again. I ran several versions of code that I saved that create a table, insert, query, then drop the table. I ran each code twice, but none produced the error. So, I won't worry about it until or unless it happens again. The other post encountered the error with an identity column. I did not use any identity column, but perhaps somehow ora:view creates an identity column behind the scenes. Or, maybe the similarity is just failure to delete some remnant that occurs with identity columns and some other things.
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604300 is a reply to message #604298] Fri, 27 December 2013 13:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Test case in link I posted is reproducible.

SY.
Re: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column [message #604301 is a reply to message #604300] Fri, 27 December 2013 14:19 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
Solomon Yakobson wrote on Fri, 27 December 2013 11:59
Test case in link I posted is reproducible.

SY.


Yes, I know that, but that is because it uses an identity column. I didn't use an identity column. So, I was trying to reproduce the error using what I did use, to show that there is more than one way to cause the error. But I can't figure out what did cause it. I think it was some code with an error, but I saved the corrected versions, not the one's with minor errors in between that may have triggered the error. So, none of the code that I saved causes the error and I didn't save the code that did.
Previous Topic: TABLE TYPES with clustered
Next Topic: UPDATE MULTIPLE ROWS IN ONE UPDATE PASS
Goto Forum:
  


Current Time: Fri Sep 19 07:39:09 CDT 2014

Total time taken to generate the page: 0.09451 seconds