Public Sequence ??


I realise that the compilation problems I am having with my sequences, is because they belong to the ALCIE user. They are not public. I cannot create a SYNONYM for a SEQUENCE.

What do you advice ?
How can I make my sequences public ?

Who is Participating?
JankovskyConnect With a Mentor Commented:
Connect as system/manager and check an existence of ALICE user

select * from  DBA_USERS.

If it isn't, check if You're connected to proper schema (connect ALICE/<pw>@<tns alias>) according to previous post.

You should be able to create a public synonym for a sequence...

'9,7218' scott@XPC>connect scott/tiger
'9,7218' scott@XPC>create sequence test1;

Sequence created.

Elapsed: 00:00:00.00
'9,7218' scott@XPC>create public synonym test1 for scott.test1;

Synonym created.

Elapsed: 00:00:00.00
'9,7218' scott@XPC>
You dont necessarily need a synonym to access the sequence, you can always reference it as ALCIE.<sequence>.

What you need is privileges on it.

Log on as user ALCIE and
grant select on <sequence> to PUBLIC;

Any user should now be able to access it - in effect its public

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Mark GeerlingsDatabase AdministratorCommented:
Both of the other suggestions are correct: you can create public synonyms for sequences, but with or without a public synonym, you stll need to "grant select on [sequence_name] to public;" if you want other users to select from it.  A public synonym just allows anyone to refer to it by name only.  Without the synonym, any other user has to refer to as [schema].[sequence_name].
ptrevesAuthor Commented:

I have a strange situation with my database. User ALCIE exists but I cannot connect to the database under that user. I can only connect as the SYSTEM/MANAGER user.

I get some stange message like:

SP2-01.... Not connected

1) Anything I can do about this ?
I don't have a choice if I want to compile my forms/reports to create synonyms of all my tables and Sequences ...

Your toughts ...


why u cannot connect to the database using the alice user. u should be able to . r u trying to connect to the database using sql plus. if yes then u should specify a connect string also which u should find it in the tnsnames.ora. if no entry is created for that, then create one and then try it.

use net8 easy config from ur oracle program menu and then create a new tnsnames.ora entry.


This has something to do with your import previously.

Now try this to fix ALCIE connection problem:

from the DATABASE SERVER machine (your linux server where the database is hosted)

Make sure you log into the LINUX as Oracle databse user usually as "oracle"

$ sqlplus /nolog
SQL>connect / as sysdba
SQL>grant connect, resource, CREATE PUBLIC SYNONYM to alcie;

then you can log in as ALCIE to create public synonym for sequence

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.