CODEWITHSUNDEEP
sqloracle-databaseoracle11g
devoured elysium
devoured elysium

Reputation: 105197

Getting a "user or role '' does not exist" when attempting to create a sequence on a newly created user on Oracle SQL

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

Upvotes: 2

Views: 30572

Answers (2)

Maninder
Maninder

Reputation: 1919

Please execute this query to sys user

alter pluggable database orcl open;

orcl is service|SID name

It is working on my side

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191425

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.

Upvotes: 2

Related Questions