2

In windows7 I Install oracle 12 C

Then I connect to database as sys user and create new user so:

CREATE USER C##USER
IDENTIFIED BY user_pass;

GRANT ALL PRIVILEGES TO C##USER;

Then I connect to database as C##USER and create table

CREATE TABLE autos (
    id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY UNIQUE,
    OWNER_NAME NVARCHAR2(50)
);

and when I am trying insert rows into table, I got problems:

INSERT INTO autos
(owner_name)
VALUES
('Tommy');

On this query, oracle returns: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column

When I connect as sys user, this error not occurred, this error happens only when I connect to database as new created user (in this case this is user: C##USER)

Can you tell me please what is wrong here?

P.S. This error returned both, in SQL Developer and in SQLPlus also.

Thanks

4
  • Any triggers on the table?
    – user1822
    Commented Apr 19, 2014 at 10:12
  • No, I no create no one triggers on the table. And also no tables in schema except this one table. Commented Apr 19, 2014 at 10:19
  • I think it might be a bug, but you can probably 'purge recyclebin' before you insert and most likely it will succeed. There is different behavior with identity columns (those identitys don't get dropped even if you drop table etc). So, purging recyclebin might help.
    – Raj
    Commented Apr 21, 2014 at 14:55
  • Just to confirm: similar problem for me was resolved by purge recyclebin;. This should be an answer, in fact, not a comment! Commented Feb 10, 2016 at 11:57

1 Answer 1

3

Seems to be a bug in Oracle 12.1.

Solution is simply:

SQL> purge recyclebin;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.