Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: [Q] wield create view error, need help?

RE: [Q] wield create view error, need help?

From: dist cash <mccdba_at_hotmail.com>
Date: Thu, 24 Oct 2002 12:14:38 -0800
Message-ID: <F001.004F2F4B.20021024121438@fatcity.com>


Thank you for your help. I follow your instruction and granr privilege. It fixed me a lot of errors. Currently, I have another problem still create view problem.

SQL> CREATE OR REPLACE VIEW WWV_DBA_SGA ( COMPONENT_GROUP,   2 MEMORY_SIZE_IN_BYTES ) AS select name component_group, bytes memory_size_in_bytes from v$sgastat
  3 .
SQL> /
MEMORY_SIZE_IN_BYTES ) AS select name component_group, bytes memory_size_in_bytes from v$sgastat

*
ERROR at line 2:
ORA-00942: table or view does not exist

I tried to grant privilege and got error:

SQL> grant select on sys.v$sgastat to portal30; grant select on sys.v$sgastat to portal30

                    *

ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Do you have ideal how to fix this problem?

Thanks.

>From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: [Q] wield create view error, need help?
>Date: Wed, 23 Oct 2002 17:08:28 -0800
>
>see answer below
>
> > -----Original Message-----
> > From: dist cash [mailto:mccdba_at_hotmail.com]
> >
> > I have ORACLE 8.1.7 on SUn Server. I have wield problem on
> > create view. If
> > I only use "select ..", I don't have error. But if I use
> > "createor replace
> > view ..select ...", I got error and I have DBA right. The
> > statement are:
> >
> >
> > SQL> CREATE OR REPLACE VIEW DBE_SYNONYMS ( OWNER,
> > 2 SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK,
> > 3 CREATED, STATUS ) AS select u.name, o.name, s.owner,
> > s.name, s.node,
> > o.ctime,
> > 4 from sys.user$ u, sys.syn$ s, sys.obj$ o
> > 5 where o.obj# = s.obj#
> > 6 and o.type#=5
> > 7 and o.owner#=u.user#
> > 8 .
> > SQL> /
> > from sys.user$ u, sys.syn$ s, sys.obj$ o
> > *
> > ERROR at line 4:
> > ORA-00942: table or view does not exist
> >
> > ....
>
>To create the view, the owner of the view will need SELECT access granted
>DIRECTLY, not via a role. If the owner of the view wants to grant SELECT on
>the view to other users, then the owner of the view will need SELECT ...
>with grant option
>
>i.e.
>grant select on sys.obj$ to x ;
>connect x/password
>create view my_obj as select * from sys.obj$ ; <--- OK
>grant select on my_obj to y ; <--- will fail: x does not have right access
>
>
>grant select on sys.obj$ to x with grant option
>connect x/password
>create view my_obj as select * from sys.obj$ ; <--- OK
>grant select on my_obj to y ; <--- OK
>
>If instance parameter O7_DICTIONARY_ACCESSIBILITY = FALSE then only a
>SYSDBA
>user will be able to grant the privileges to user X.
>
>Finally, if you were using Oracle 9.x, you could grant user X "select any
>dictionary" system privilege (directly, not via a role) instead of granting
>select access on each SYS table you want to use.



Surf the Web without missing calls! Get MSN Broadband. http://resourcecenter.msn.com/access/plans/freeactivation.asp
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: dist cash
  INET: mccdba_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Oct 24 2002 - 15:14:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US