Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews2.google.com!not-for-mail
From: karistom@hotmail.com (Christine)
Newsgroups: comp.databases.oracle
Subject: Re: Tables without primary key
Date: 8 Sep 2004 18:51:52 -0700
Organization: http://groups.google.com
Lines: 35
Message-ID: <693b7163.0409081751.2d8068@posting.google.com>
References: <99f00224.0409080001.2142293e@posting.google.com> <9MydnRI6YN6LS6PcRVn-uw@comcast.com>
NNTP-Posting-Host: 150.48.245.162
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1094694743 14972 127.0.0.1 (9 Sep 2004 01:52:23 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 9 Sep 2004 01:52:23 +0000 (UTC)
Xref: dp-news.maxwell.syr.edu comp.databases.oracle:28665

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<9MydnRI6YN6LS6PcRVn-uw@comcast.com>...
> "Michael Maier" <maier_michael2003@yahoo.de> wrote in message
> news:99f00224.0409080001.2142293e@posting.google.com...
> | Hi,
> |
> | for an OnlinerReorg (SAP/ORACLE) i have to know the name of the tables
> | without primary key.
> |
> | Where can i find it with sqlplus or abap?
> |
> | THX
> |
> | Best Regards,
> | Micha
> 
> assuming SAP uses database constraints, these are all listed in the
> DBA_CONSTRAINTS (or USER_CONSTRAINTS) data dictionary view, CONSTRAINT_TYPE
> = 'P'
> 
> you can write a query outer joining this to DBA_TABLES (or USER_TABLES) and
> check for NULL constraint names, or use a MINUS type query
> 
> are you familiar with these techniques?
> 
> ++ mcs

Here you are. ^_^

select owner, table_name 
from dba_tables 
where table_name not in 
   (select TABLE_NAME 
    from dba_constraints 
    where CONSTRAINT_TYPE='P')
and owner not in ('SYS', 'SYSTEM');