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: Count all records in all tables

Re: Count all records in all tables

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 14 Apr 2006 17:38:55 +0200
Message-ID: <443fc20f$0$31332$626a54ce@news.free.fr>

"Mark D Powell" <Mark.Powell_at_eds.com> a écrit dans le message de news: 1145027971.179068.324790_at_i39g2000cwa.googlegroups.com...
| Michael, I was unable to view your scrpts since registration is
| required to access the site. Due to past experience receiving bucket
| loads of spam from some sites I have registed with I avoid registering
| with sites I am unfamiliar with.
|
| -- Mark D Powell --
|

Mark i used this site for years and never received any message of any kind from it but the weekly newsletter as i registered for it too.

Here's the content of the page:

<quote>

The following two scripts gives you the number of rows in all the tables of a user. The first one for all the tables of the current user. The second one for all the tables of the user passed to the script (first parameter: &1).

The result is writing in the file count_<user>_<timestamp>.lst

First script (current user)


Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',

    to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u from user_tables
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off

Second script (user &1, needs to be connected as DBA):


Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||owner||'.'||table_name||';',

    to_char(sysdate, 'YYYYMMDDHH24MISS') d from dba_tables where owner=upper('&1')
order by table_name
/
Spool off
Spool count_&1._&date_
@tmp.LST
Spool off

</quote>

Regards
Michel Cadot Received on Fri Apr 14 2006 - 10:38:55 CDT

Original text of this message

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