Re: SELECT Statement help required

Jon Winchester wrote:
>
> James Petts wrote:
> >
> > I'm trying to write a SELECT statement which will return the
> > number of rows in each of a set of tables selected by owner
> > from DBA_TABLES.
> >
>
> I use this SQL to select the number of rows from USER_TABLES :
>
> column table_name format A16
> column num_rows format 999,999,999 heading 'Rows' ;
> column blocks format 999,999 heading 'Blocks' ;
> column empty_blocks format 999,999 heading 'Empty Blocks' ;
> compute sum of empty_blocks on report
> compute sum of blocks on report
> break on report
>
> select table_name, num_rows, blocks, empty_blocks, avg_row_len
> from user_tables
> order by table_name;

That assumes that the NUM_ROWS column is being populated. None of the dozen or so Oracle instances I have encountered have had this column populated.

Gary's solution is more universal:

>select 'select count(*) from '||table_name||';'
>from dba_tables;
>
>spool this out to a file, and then execute that file.
>set term off and heading off so you don't get extra crap in the output.

The only change needed is to include the owner name:

set heading off
spool countem.sql
SELECT
  'SELECT '''||owner||' '||table_name||': ''||     to_char(count(*)) from '||owner||'.'||table_name||';' FROM
  sys.dba_tables
WHERE
  owner IN ('OWNER1','OWNER2','WHATEVER') ;
spool off
start countem.sql

You will probably want to include the specification of the owner in the where clause like this, otherwise, all the system tables will be included. Specifying the table name in the form of owner.tablename is important since the user who runs the query won't necessarily be able to see the other users tables without specifying the schema in this way. You may also want to do some RPAD-ing or LPAD-ing to make the names and counts line up more nicely. This can also be done by tweaking the "to_char(count(*))" part of the statement.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||