Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Count all records in all tables
"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