1

I've just created a new user on an Oracle SQL database. I think the sequence of commands on SQL+ is was something such as:

> create user testuser identified by mypassword;
> grant all privileges to testuser identified by mypassword;

I then connected to that user through SQL Developer and attempted to run the following code:

   CREATE SEQUENCE  "ABC"."DEF_SEQ"  MINVALUE 10 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 250 CACHE 20 NOORDER  NOCYCLE ;

but I get a threatening

Error at Command Line : 1 Column : 18
Error report -
SQL Error: ORA-01917: user or role '' does not exist
01917. 00000 -  "user or role '%s' does not exist"
*Cause:    There is not a user or role by that name.
*Action:   Re-specify the name.

If I attempt to run a CREATE TABLE everything seems alright, though:

CREATE TABLE abc(my_id int primary key);

What am I doing wrong? Thanks

devoured elysium
  • 101,373
  • 131
  • 340
  • 557
  • 2
    Have you just confused the names for posting? You created user `testuser` but are trying to create the sequence in schema `abc`. Or are you trying to create a sequence that is part of *table* `abc`? (Which isn't how sequences work...) – Alex Poole Feb 25 '16 at 09:50
  • You solved my issue. The original db from where I took this was called "ABC" and this one doesn't. But the error message could have been a bit more clear about this. – devoured elysium Feb 25 '16 at 09:58

2 Answers2

2

The CREATE SEQUENCE "ABC"."DEF_SEQ" statement you are using will try to create a sequence called DEF_SEQ in schema ABC. You created user/schema TESTUSER, not ABC.

To create it under your actual user you can do:

CREATE SEQUENCE "TESTUSER"."DEF_SEQ" ...

but the schema prefix is only needed if you're logged in as a different user with enough privileges, such as SYS, and you shouldn't generally be doing anything under that account. If you're connected as testuser you don't need the prefix, just:

CREATE SEQUENCE "DEF_SEQ" ...

It looks like you might be trying to create a sequence under the table ABC. That isn't how sequences work; they are independent objects, not tied to any table. The same sequence can be used to generate the primary keys for multiple tables, for example.

If you want a sequence that auto-generates primary key values for table ABC then you need a trigger that sets the key column from the sequence, e.g. in 11g:

create trigger abc_id_trig
before insert on abc
for each row
begin
    :new.my_id := def_seq.nextval;
end;
/

From 12c you can hide that with the identity clause.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Yep, that was it. But it is good that I have posted this question on SO, so that next time someone falls to the same problem they can google this. There were virtually no results to this "user or role '' does not exist" search string. – devoured elysium Feb 25 '16 at 10:02
  • 1
    @devouredelysium - the error makes sense if you understand [the object reference syntax](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements009.htm), but even then it would help if the message referred to schema rather than user - they're the same thing but that causes [its own confusion sometimes](http://stackoverflow.com/q/14355053/266304). People coming from other databases sometimes think schema = database which isn't how it works in Oracle either. – Alex Poole Feb 25 '16 at 10:07
0

Please execute this query to sys user

alter pluggable database orcl open;

orcl is service|SID name

It is working on my side

Maninder
  • 1,539
  • 1
  • 10
  • 12