Re: Wield create view error, need help !!!!!

On 23 Oct 2002 16:14:23 -0700, aaa <leed_at_shell.cais.com> wrote:

>
>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
>
>
>
>SQL> select u.name, o.name, s.owner, s.name, s.node, o.ctime, o.status
> 2 from sys.user$ u, sys.syn$ s, sys.obj$ o
> 3 where o.obj# = s.obj#
> 4 and o.type#=5
> 5 and o.owner#=u.user#
> 6 .
>SQL> /
>
>NAME NAME
>------------------------------ ------------------------------
>OWNER NAME
>------------------------------ ------------------------------
>NODE
>--------------------------------------------------------------------------------
>CTIME STATUS
>--------- ----------
>PUBLIC DUAL
>SYS DUAL
>
>20-SEP-02 1
>
>Does anyone know why?
>
>
>
>Thanks.

Privilege by means of a role (ignored during compilation of stored objects) instead of direct privileges, as has been posted here numerous times before.
Also I don't know why you want to create your own views on the datadictionary, especially if the view name differs only one position from the official one. Looks like you're going to get havoc.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Oct 23 2002 - 23:42:25 CDT