This PL/SQL snippet will populate a table named TABCOUNTS with the name and
rowcount for each table owned by the current user. Standard disclaimer:
there are ways to do this more efficiently (without all the EXECUTE
IMMEDIATE calls) but they require more typing! <g> There's the beauty of
free help for you ... modify as needed, but I hope this will be a good
starting point for you.
<snip>
DECLARE
CURSOR CTAB IS
SELECT TABLE_NAME
FROM USER_TABLES;
NROWS NUMBER;
BEGIN
SELECT COUNT(*) INTO NROWS
FROM USER_TABLES
WHERE TABLE_NAME = 'TABCOUNTS';
IF NROWS = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE TABCOUNTS (TABLE_NAME VARCHAR2(30),
NROWS NUMBER)';
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE TABCOUNTS';
END IF;
FOR TAB IN CTAB LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || TAB.TABLE_NAME INTO NROWS;
EXECUTE IMMEDIATE 'INSERT INTO TABCOUNTS VALUES (''' || TAB.TABLE_NAME
|| ''', ' || TO_CHAR(NROWS) || ')';
END LOOP;
END;
/
</snip>
--
Hope this helps,
Chris
___________________________________
Chris Leonard, The Database Guy
http://www.databaseguy.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com
MCSE, MCDBA, MCT, OCP, CIW
___________________________________
"Odd Morten Sveås" <odd.morten.sveas_at_accenture.com> wrote in message
news:4306a83.0210040808.202f8691_at_posting.google.com...
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:<u86n9.37759$dp1.97044_at_rwcrnsc52.ops.asp.att.net>...
> > Why not have SQL create SQL? I too am too lazy to type 100 or more
commands
> > to get this info.
> > spool mycommands.sql
> > select 'select table_name,count(*) from table_name;' from user_tables
order
> > by table_name;
> > spool off
> > @mycommands.sql
>
> > It is possible to create some pl/sql stored procedure with dynamic sql
that
> > would issue the 100 statements. It could put the info into a table and
then
> > report on that. But the use sql to write sql is a tried and true
method.
> > Jim
> > "shinwar" <jshinwar_at_yahoo.com> wrote in message
> > news:77e5857.0210031319.47f28818_at_posting.google.com...
> > > Hi, Oracle Gurus
> > >
> > >
> > > Before this I have posted one question. Now, I have one more question
> > > on how to count total number rows of each user table using a single
> > > query statement. I really do not know if it can be done in a single
> > > SQL statement. Let's say. I have 100 user tables. I do not want to
> > > issue 100 SQL commands for this job.
> > >
> > > Instead, using a single SQL which uses user_ind_columns.table_name, I
> > > like to find total number of rows of all my tables. The output should,
> > > for example, be in this format:
> > >
> > > user-tables total_no_of_rows
> > > ----------- -----------------
> > > table1 99999
> > > table2 99999
> > > ....
> > > ....
> > >
> > > Appreciate any of your advise. Thank you for your valuable time.
>
> Hi
> You surely can use a PL/SQL procedure or the spool method. An other
> way is to make a view that counts the tablerows.
>
> If your number of tables is rather constant this migth be a good ide.
>
> You would like to make an sql that generates the view. You can use the
> spool method here too.
>
> something like this: (not tested)
>
> spool 'create.view.sql';
> select 'create or replace view table_count (table_name ,
> number_of_rows)
> as select to_char(null),to_number(null)' from dual;
>
> select 'union select '''||table_name ||''', count(1) from ' ||
> table_name
> from user_tables;
> /
>
> In this way you can easly check the rowcounts. Also you can use
> ordinary select statments (eg sum , where...)
Received on Mon Oct 07 2002 - 10:06:23 CDT
