Re: view not found

From: <burrell.john_at_yahoo.com>
Date: Fri, 18 Jan 2008 09:49:49 -0800 (PST)
Message-ID: <2511c7b2-f5dd-481f-99ca-c98dabb43904@u10g2000prn.googlegroups.com>

On 18 Jan, 15:58, gazzag <gar..._at_jamms.org> wrote:
> On 18 Jan, 15:53, burrell.j..._at_yahoo.com wrote:
>
>
>
>
>
> > Hello,
> > I have created, and run, as sysdba this script to create an user and a
> > view.
> > drop view ADNAM;
> > create view ADNAM as select Serial from reporter.status;
>
> > drop user ADNAM;
> > create user ADNAM identified by ADNAM;
> > GRANT CONNECT TO ADNAM;
> > GRANT RESOURCE TO ADNAM;
> > GRANT SELECT ON ADNAM to  ADNAM;
> > GRANT UNLIMITED TABLESPACE TO ADNAM;
>
> > But when I log in a ADNAM/ADNAM
>
> > SQL> connect ADNAM/ADNAM
> > Connected.
> > SQL> select * from ADNAM;
> > select * from ADNAM
> >               *
> > ERROR at line 1:
> > ORA-00942: table or view does not exist
>
> > Clearly view adnam does exist - but user ADNAM is not referencing it
> > correctly?
> > Please help me out!
>
> > J
>
> Why are you doing this as SYS?
>
> I would perform the following:
>
> 1.  Log in as SYSTEM
>
> 2.  Create your user:
>
> drop user ADNAM;
> create user ADNAM identified by ADNAM;
> GRANT CONNECT TO ADNAM;
> GRANT RESOURCE TO ADNAM;
> GRANT SELECT ON ADNAM to  ADNAM;
> GRANT UNLIMITED TABLESPACE TO ADNAM;
>
> 3.  Create your view as follows:
>
> drop view ADNAM.ADNAM;
> create view ADNAM.ADNAM as select Serial from reporter.status;
>
> The convention <schema_name>.<object_name> needs to be used to create
> objects in a schema other than your own.  Of course, you're not making
> life too easy for yourself by naming objects and schemas as the same
> thing!
>
> HTH
>
> -g- Hide quoted text -
>
> - Show quoted text -

Thanks for that!
Not quite there yet. Still some problems. I created the reporter tablespaces as sysdba - not quite sure why! If I invoke as sysdba
I get this:
SQL> @adduser
User dropped.
User created.

Grant succeeded.
Grant succeeded.
Grant succeeded.
drop view ADNAM.AVIEW

*
ERROR at line 1:
ORA-00942: table or view does not exist
View dropped.
View created.
create view ADNAM.AVIEW as select Serial from reporter.status ERROR at line 1:
ORA-01031: insufficient privileges

If i invoke as system I get this:
SQL> @adduser
User dropped.
User created.

Grant succeeded.
Grant succeeded.
Grant succeeded.
drop view ADNAM.AVIEW

*
ERROR at line 1:
ORA-00942: table or view does not exist
drop view AVIEW
*
ERROR at line 1:
ORA-00942: table or view does not exist
create view AVIEW as select Serial from reporter.status ERROR at line 1:
ORA-01031: insufficient privileges
create view ADNAM.AVIEW as select Serial from reporter.status

                                                       *

ERROR at line 1:
ORA-00942: table or view does not exist
But I can "describe" reporter.status
$ cat adduser.sql
drop user ADNAM;
create user ADNAM identified by ADNAM;
GRANT CONNECT TO ADNAM;
GRANT RESOURCE TO ADNAM;
GRANT UNLIMITED TABLESPACE TO ADNAM;
drop view ADNAM.AVIEW;
drop view AVIEW;
create view AVIEW as select Serial from reporter.status; create view ADNAM.AVIEW as select Serial from reporter.status;

Can you spot the problem? Received on Fri Jan 18 2008 - 11:49:49 CST