Re: NEWBIE: SQL intro

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 16 Oct 2000 23:06:37 +0200
Message-ID: <971730295.13030.1.pluto.d4ee154e@news.demon.nl>

<oratune_at_aol.com> wrote in message news:8sfn50$1fq$1_at_nnrp1.deja.com...
> In article <971716147.2621.1.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> >
> > "Paul Bennett" <spacey845_at_my-deja.com> wrote in message
> > news:8sf9no$kp4$1_at_nnrp1.deja.com...
> > > In article <8sf913$k5r$1_at_nnrp1.deja.com>,
> > > oratune_at_aol.com wrote:
> > > > In article <8sf862$jfu$1_at_nnrp1.deja.com>,
> > > > Paul Bennett <spacey845_at_my-deja.com> wrote:
> > > > > Very stupid newbie question about SQL.
> > > > >
> > > >
> > > > Try this:
> > > >
> > > > select owner, table_name, tablespace_name
> > > > from all_tables;
> > > >
> > >
> > > Thanks! It's bloody obvious once I see it in print in front of me!
> > >
> > >
> > >
> > > ---
> > > Pb
> > > My opinions are my own and should not be taken as
> > > representing any other group or entity.
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> > Actually you should know there are three levels:
> > user_tables |indexes : your own tables etc.
> > all_tables|indexes: all the tables you have access to.
> > dba_tables|indexes: all tables in the database.
> >
> > This could mean the answer from mr. oratune is only partially correct.
> >
> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >

>

> It is true that there are three levels of results one could return; two
> are accessable by all users, whereas the third, dba_tables, is only
> accessable to those granted DBA access. Should Mr. Bennett not be
> granted the DBA role it would be impossible for him to access the DBA_
> views. I attempted to base my response upon general access outside of
> the DBA realm. If that makes my answer incorrect, so be it.

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Where as the third, dba_... is the *only one* containing *all* tables. Only users with the dba role (v7.3) or the select_catalog_role (8.0 and beyond) are allowed to see the dba_tables (and of course all those users with select any table privilege).
From the original post it is not clear he has dba privilege, if he hasn't all_tables won't allow him to see *all* tables (whether relevant or not (SYS?)) Sorry to argue with you. I wouldn't have done so, if I wouldn't have be convinced such an answer won't be considered correct for an OCP DBA test.

Regards,

Sybrand Bakker, Oracle DBA Received on Mon Oct 16 2000 - 16:06:37 CDT