Re: How to identiy tables without indexes

Hi Soeren,

Select table_name, owner from dba_tables t where not exists
(select 'x' from dba_indexes i
 where i.owner = t.owner
 and i.table_name = t.table_name)

On the same line (as this will be your next question) select table_name, owner from dba_tables t where not exists
(select 'x' from dba_constraints c
 where c.owner = t.owner

 and     c.table_name = t.table_name
 and     c.constraint_type = 'P' -- primary key

Hth,
Sybrand Bakker, Oracle DBA

Soeren Dalby wrote in message <7j3l8r$t2f$1_at_news.inet.tele.dk>...
>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
>
>
>
Received on Wed Jun 02 1999 - 11:59:32 CDT