Re: Confused by dba_tab_privs

"Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca> wrote in message news:<M8Xr9.37096$ER5.2196323_at_news2.telusplanet.net>...
> select * ... gives 63 rows
> select distinct * ... gives 49 rows.
>
> So definitely I have the same owner, grantor, grantee etc.
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> news:2687bb95.0210180533.3393c7ff_at_posting.google.com...
> > "Terry Dykstra" <dontreply_tddykstra_at_forestoil.ca> wrote in message
> news:<sZDr9.18328$Sk6.1769262_at_news1.telusplanet.net>...
> > > I run the following query:
> > >
> > > select * from dba_tab_privs
> > > where grantee = 'PUBLIC'
> > > and table_name like 'DBMS%'
> > > order by owner, table_name
> > >
> > > In the result I notice that there are a lot of duplicate records, e.g.
> > > dbms_job, dbms_transaction. ( 63 rows retrieved, 49 distinct rows).
> What
> > > could be causing this? Is it a reason for concern?
> > > Oracle 8.1.7.2.7 on W2K
> >
> > Well, your query does not return duplicates on my system (AIX 4.3;
> > 8.1.7.4). I will admit my first thought was maybe the specification
> > and body each show but then I remember I run similar queries and only
> > one execute shows for a package. So take a second look at the owner
> > for each line; did someone run the catproc script under the wrong
> > (non-sys) ID? That will put a second, but probably unusable package
> > out there with a public grant.
> >
> > If so I would drop the wrong owner set to clear them out of the way.
> >
> > HTH -- Mark D Powell --

OK, just a couple of ideas:

Query dba_objects to see if you have any sys owned objects that have a status of "INVALID"? If so then fixing them might fix this problem.

Have you compared the code for the dictionary view in the $ORACLE_HOME/rdbms/admin cat* script with what is currently in use to be sure no one walked on it while trying to create their own view of the data?

HTH -- Mark D Powell -- Received on Fri Oct 18 2002 - 18:58:11 CDT