Solved

Public Sequence ??

Posted on 2004-09-23
7
1,752 Views
Last Modified: 2012-06-21
Hello,

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 ?

PT
0
Comment
Question by:ptreves
7 Comments
 
LVL 6

Expert Comment

by:morphman
ID: 12138326
You should be able to create a public synonym for a sequence...

'9,7218' scott@XPC>connect scott/tiger
Connected.
'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>
0
 
LVL 7

Expert Comment

by:BobMc
ID: 12138812
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

HTH
Bob
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 12139181
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].
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ptreves
ID: 12139782
Hello,

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 ...

PT
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 12139982
hi

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.

regards
annamalai
0
 
LVL 6

Accepted Solution

by:
Jankovsky earned 500 total points
ID: 12141313
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.

Bob
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12141738
Ptreves:

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





0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now