Toad World® Forums

ORA-01039


#1

SQL Optimizer for Oracle 9.2.2

SELECT owner,
synonym_name,
table_owner,
table_name
FROM dba_synonyms dba_syns
WHERE table_owner = user
AND (NOT EXISTS – no target table/view
(SELECT 0
FROM user_views uv
WHERE uv.view_name = dba_syns.table_name
UNION
SELECT 0
FROM user_tables ut
WHERE ut.table_name = dba_syns.table_name
UNION
SELECT 0
FROM user_objects uo
WHERE uo.object_name = dba_syns.table_name)
OR NOT EXISTS – no grant for target table/view
(SELECT 0
FROM table_privileges tp
WHERE tp.grantee = dba_syns.owner
AND tp.table_name = dba_syns.table_name));

returns ORA-01039 while getting plan

The statement runs absolutely well when executed in TOAD for Oracle 12.12.

Any ideas?


#2

Hi,

In order to EXPLAIN a query based on a view, you need SELECT privileges on the underlying tables as well as the view.
To work around, either grant the privileges on all underlying objects of the view to the user doing the Explain Plan, or use a user with privileges to access all underlying tables.

You can find more information in this link:
www.toadworld.com/…/5011

Regards,
Ken


#3

thx!