Re: How to identiy tables without indexes

Give the following query in order to identify all the tables which doesnt have the index.

"select distinct table_name, owner from dba_tables dt where not exists (select table_name from dba_indexes where table_name=dt.table_name)"

Regards
shailaja.

James Lorenzen <james_lorenzen_at_allianzlife.com> wrote in message news:7j3q8t$p45$1_at_nnrp1.deja.com...
> Other than a minor syntax error in thequery, it will do what you want.
> It will be slow.
>
> James
>
> In article <7j3l8r$t2f$1_at_news.inet.tele.dk>,
> "Soeren Dalby" <dalby_data_at_post3.tele.dk> wrote:
> > Hi Guys,
> >
> > I am in a situation where I need to identify all the tables in an
> Oracle
> > database which doesnt have an index (at least one is expected for all)
> and
> > this actually turns into a SQL question because I have a theory:
> select all
> > the tables in DBA_TABLES which is not represented in DBA_INDEXES. It
> could
> > be something like
> >
> > select distinct table_name, owner from dba_tables where not in (select
> > table_name, table_owner from dba_indexes )
> >
> > Is this way correct ?? I do not have access to the database while
> writing
> > this so the fieldnames may be wrong
> >
> > Any input is mostly welcomed
> >
> > Thanks in advance.
> >
> > --
> >
> > Sincerely yours
> >
> > Dalby Data
> > Sneppevej 15, st. th.
> > 2400 Copenhagen NV
> >
> > Telephone +45 40 96 00 89
> > Telefax +45 31 86 09 20
> >
> > Mail: dalby_data_at_post3.tele.dk
> > Site: http://home3.inet.tele.dk/dalbydat
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jun 15 1999 - 16:26:25 CDT