RE: Senior moment

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 7 Dec 2006 11:00:40 -0500
Message-ID: <02C2FA1C9961934BB6D16DE35707B27B025C8B10@jax-mbh-01.jax.crowley.com>

The other schema does not own the table. It could create it's own table and it's name would take precedence over the public synonym. (I'm quite sure).

The privilege you most likely need is an 'ANY' type... like 'CREATE ANY TABLE', CREATE ANY INDEX. This is so you can manipulate objects that you do not own.

SELECT DISTINCT(PRIVILEGE) from dba_sys_privs where privilege like '%ANY%'; Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day Sent: Thursday, December 07, 2006 10:33 AM To: Oracle-L Freelists
Subject: Senior moment

Oracle 9.2 on AIX

I've created a table (table a) under one schema with a public synonym and granted select to a role.

In another schema, with that role, I've created another schema. I can select count(*) from table a in this second schema but when I try to create a foreign key or a materialized view log I get -

ORA-00942: table or view does not exist

I'm sure that I'm missing something obvious, I just can't think what it is. All suggestions welcome.

SQL> CREATE MATERIALIZED VIEW LOG ON ADDRESS_CDS tablespace dss_DATA   2 WITH SEQUENCE(
  3 ADC_DESC
  4 ,ADC_CLOSE_DT
  5 ) INCLUDING NEW VALUES
  6 /
CREATE MATERIALIZED VIEW LOG ON ADDRESS_CDS tablespace dss_DATA *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> desc ADDRESS_CDS
Name
Null? Type

----------------- ADC_CD NOT NULL VARCHAR2(2) ADC_DESC NOT NULL VARCHAR2(80) ADC_CLOSE_DT DATE

SQL> select count(*) from ADDRESS_CDS;

COUNT(*)

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 07 2006 - 10:00:40 CST