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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Record count of all tables...

Re: Record count of all tables...

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Thu, 17 Sep 1998 18:30:15 +0200
Message-ID: <6trdc1$39t$1@pascal.a2000.nl>


>How can I get a record count of all the tables in the database ?

The spool file examples will do of course. Doing it the hard way, using dynamic SQL:

Below are two scripts. One checks a column last_update_date, which is normally present in Oracle Applications (like Oracle Financials) tables. The other simply counts all tables.

Good luck,
Arjan.



select table_name, column_name, check_result from avbentem_last_updates
where check_result = 'All older';

select table_name, column_name, check_result from avbentem_last_updates
where check_result != 'All older';

select check_result, count(*)
from avbentem_last_updates
group by check_result;



set serveroutput on size 10000
!echo Oracle-instance ORACLE_SID is $ORACLE_SID show user
declare

  l_cursor integer;               -- cursor pointer
  l_source varchar2(1000);        -- SQL statement
  l_rows   integer;
  l_count number(10);
  l_total number(12) := 0;

  --
  cursor c_tabs is
    select table_name
    from user_tables
    order by table_name;

begin

   dbms_output.put_line( 'Count of all user tables');    for r_tab in c_tabs loop

      l_cursor := dbms_sql.open_cursor;
      l_source := 'SELECT COUNT(*) FROM ' || r_tab.table_name;
      dbms_sql.parse( l_cursor, l_source, dbms_sql.NATIVE);
      --
      dbms_sql.define_column(l_cursor, 1, l_count);
      l_rows := dbms_sql.execute_and_fetch(l_cursor, false);
      dbms_sql.column_value(l_cursor, 1, l_count);
      dbms_sql.close_cursor( l_cursor);
      --
      if l_count > 0 then
        dbms_output.put_line( rpad( r_tab.table_name, 30, '.')
                              || to_char( l_count));
        l_total := l_total + l_count;
      end if;

   end loop;
   dbms_output.put_line( rpad( 'Total: ', 30)||to_char( l_total)); end;
/

Received on Thu Sep 17 1998 - 11:30:15 CDT

Original text of this message

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