I am not sure I understand entirely what you are trying to do (find out every database
object that in not accessible to a particular user???), but I'll give it a shot
anyway. Try this query:
(SELECT table_name FROM dba_tables
UNION
SELECT name FROM v$parameter
UNION
SELECT username FROM all_users)
MINUS
SELECT object_name FROM all_objects
;
Hope this helps.
Michael Serbanescu
Scott Patterson wrote:
>
> I need a little help in my Oracle SQL education.
>
> My real problem is a little more contorted than this but this example uses
> all_objects which everyone will have.
>
> I have a list of objects that I need to check if they are in all_objects,
> only listing the items that are not found. This is easily done with the
> following statements but I would like to accomplish this without creating
> the tmptab table. With the SQLServers or Informix I would use a temp table.
> How should I be attacking this with Oracle?
>
> This will be running from a SQLPlus script.
>
> Thanks,
> Scott Patterson
> Trilogy Development Group
> scott_patterson_at_trilogy.com
>
> ------
> create table tmptab (obj_name varchar2(30));
> insert into tmptab values ('DBA_TABLES');
> insert into tmptab values ('V$PARAMETER');
> insert into tmptab values ('WILL_NOT_BE_FOUND');
> insert into tmptab values ('ALL_USERS');
> select obj_name from tmptab where obj_name not in (
> select obj_name from tmptab, all_objects where obj_name = object_name);
> drop table tmptab;
Received on Thu Feb 19 1998 - 00:00:00 CST