Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: select statement

Re: select statement

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 13 Jun 2001 15:24:45 +0100
Message-ID: <3b2777b4$0$15029$ed9e5944@reading.news.pipex.net>

"Bastiaan Schaap" <fake_address_at_nomail.com> wrote in message news:OyJV6.20$wU3.813_at_psinet-eu-nl...
> Obviously some people here like to take the long way around ;-))
>
> I'd simply use:
>
> select table_name, count(column_name) from dba_tab_columns where
 table_name
> IN
> (select table_name
> from dba_tables ) group by table_name
>
> Remember though that it'll take some time to complete!

It doesn't really take that long, and at the end of it you know exactly how many columns each table in the database has. Unfortunately the original request was for a way to generate a row count for each table. You can of course use PL/SQL as others have said.

Alternatively if you keep your stats up to date you can use

select owner,table_name,num_rows
from dba_tables
order by 1;

The figure for num_rows is not guaranteed to be accurate, in fact it is guaranteed to be approximate, however will be a good estimate in a well analysed system.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Wed Jun 13 2001 - 09:24:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US