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

  • Any triggers on the table? – a_horse_with_no_name Apr 19 '14 at 10:12
  • No, I no create no one triggers on the table. And also no tables in schema except this one table. – Oto Shavadze Apr 19 '14 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 Apr 21 '14 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! – cartbeforehorse Feb 10 '16 at 11:57
3

Seems to be a bug in Oracle 12.1.

Solution is simply:

SQL> purge recyclebin;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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