Thursday, November 15, 2018

ORA-01720: grant option does not exist for SCHEMA.TABLE

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You are getting ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE while trying to execute CREATE OR REPLACE VIEW V_MY_VIEW because you added the new table SOME_TABLE residing on a different schema and you lack privileges on SOME_TABLE. The you go and ask for SELECT privilege on the table SOME_TABLE with GRANT OPTION.
After obtaining the privilege you retry the operation but you still get:

ORA-01720: grant option does not exist for SCHEMA.SOME_TABLE


This happens because since version 11g you cannot simply replace a view containing a newly added table even if you have the SELECT privilege WITH GRANT OPTION.

You need to DROP VIEW V_MY_VIEW first and then re-create it.

The error message is misleading to say the least, a more specific error should be raised instead in these situations.

See message translations for ORA-01720 and search additional resources.

Friday, October 26, 2018

When SDO_GEOM.SDO_AREA returns a negative value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If function SDO_GEOM.SDO_AREA returns a negative number chances are that your geometry is invalid.

For instance I checked the geometry with function SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT and it returned:

13349 [Element <1>] [Ring <1>][Edge <2>][Edge <1>]

which means that the polygon boundary crosses itself.
The same applies when code 13356 is returned (adjacent points in a geometry are redundant).

Please note that the absolute value is still correct.
In most cases you should be able to fix the geometry by applying function SDO_GEOM.SDO_SELF_UNION.

This happens on Oracle 12.1

See message translations for ORA-13349, ORA-13356 and search additional resources.

Friday, July 20, 2018

The importance of being aliased

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I was writing a query returning the indexes to be monitored for a list of schemas for those tables having fewer than 5 indexes (not counting LOB indexes) and I was surprised to see that it returned no rows.
After checking what's wrong I realized that I had specified the wrong column for the included subquery, but how could it be that Oracle wasn't complaining about a non existing column?

with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select owner, index_name
                                     from included
                              )
  and (a.owner, a.index_name) not in (select owner, index_name
                                        from excluded 
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';
 
DDL
===
(no rows) 

Soon I realized not only that I had specified the wrong column (I should have written table_name instead) but also that I hadn't specified table aliases and index_name was not a value coming from included, but from the outer view dba_indexes, which is just fine from Oracle's point of view but totally wrong for my purposes.

In order to prevent these mistakes, the SQL above needs to be written using ALWAYS table aliases in the subqueries and applying them to each relevant column:

  with excluded as (select /*+ MATERIALIZE */ b.owner, b.index_name
                                      from dba_constraints b
                                     where b.constraint_type in ('P','U')
                                       and b.owner in ('A','B','C')
                                     ),
       included as (select /*+ MATERIALIZE */ owner, table_name
                                from dba_indexes
                               where owner in ('A','B','C') 
                                 and index_type != 'LOB' 
                               group by owner, table_name
                               having count(*) > 5
                            )
  select 'ALTER INDEX ' || owner ||'.' || index_name || ' MONITORING USAGE;' as ddl
  from dba_indexes a
 where (a.owner, a.table_name) in (select i.owner, i.table_name
                                     from included i
                              )
  and (a.owner, a.index_name) not in (select e.owner, e.index_name
                                        from excluded e
                                     )
  and a.owner in ('A','B','C')
  and a.index_type != 'LOB';


In this case it was easy to guess that something was wrong because the query returned no results and I knew it was nonsense, but in complex queries made up possibly of several subqueries one can easily overlook the problem should some results being returned.

Table aliases must be used at all times!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles