record count of every table

Dear All,

I need to find out the no of records in each table under a particular user and there are around 3000 tables under that user. Now what is the best way to get the tablename and the record count of every table under that user.

Any hints in this regard.....

Thanks,

Regards,

K.Diwakar

a long way of doing it, and probba;y not very efficient either, but it works

set serveroutput on size 200000
declare
tabname varchar2(30);
tempcount number(10);
cursor tabs is select table_name from tabs;
tab_record tabs%ROWTYPE;
begin
for tab_record in tabs loop
fetch tabs into tabname;
exit when tabs%NOTFOUND;
dbms_output.put_line('Counting from ' ||tabname);
execute immediate 'select count(*) from '||tabname into tempcount;
dbms_output.put_line('Count = ' || tempcount);
dbms_output.put_line('.....................................................');
end loop;
end;
/

Code:

---

Set Head off
Set Feedback off
Set Trimspool on

Spool C:\Apps\Base\Count_In_Table.sql

Select
  'Select Count(*) from '||Table_Name||';'
From
  DBA_TABLES
Where
  Owner  =  'SCHEMA_NAME'
;

Spool off

Set Head on
Set Feedback on

Spool C:\Apps\Logs\Count_In_Table.log

@C:\Apps\Base\Count_In_Table.sql

Spool off

---

Abhay.

Dear abhaysk and davey23uk,

Thanks for your reply.

It was very usefull indeed.

Regards,

K.Diwakar

the one problem is that wont give you the table name, just a count from it

A better way is to query user_tables.

This will solve the issue completely.

Just got that idea..

Regards,

K.Diwakar

If you Analyze all your tables with COMPUTE STATISTICS (rather than ESTIMATE) then you can look at the NUM_ROWS field in your USER_TABLES (or DBA_TABLES) to see the table_name and the row count. If you are using CBO it might even help your overall performance to have current statistics.

Better still, use DBMS_STATS and do it in parallel:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MY_SCHEMA', degree=>10, cascade=>TRUE);

SELECT table_name, num_rows

FROM   user_tables

ORDER BY table_name;

Cheers

I wrote something similar that runs nightly on our databases. It gathers counts, number of extents, size and then stores it all to a table so you have a nice history.

It's not rocket science but can be usefull for spotting trends and figuring out when a table increased in size.

Feel free to use it and hack it to death.

Chris

-- Author. Chris Watson
-- Date. 11/10/02
-- File. count_all_tables.sql
-- Usage run as normal procedure (execute )
-- Propose. PLSQL package to run a count(*) on all tables in a users schema.
-- Needs a table called TBL_COUNTS to store all the results in.
-- Also collects extent numbers and segment size (it shows segments less that
-- 1/2 Mb as 0 (due to the round function)).
--
-- Enhancments. 1. Tablespaces as variable.
-- 2. Table owner for systems with multiple schemas.
-- 3. Timestamp renamed to datastamp
--
-- Change control
-- NAME DATE WHAT
-- Chris Watson 270203 Added table spaces to object creation and SQL to submit DBMS_JOB.
-- for automatic running (3am every day)
-- Chris Watson 190303 2 new columns on the table (EXTENTS and SIZE_MB)
-- variable all changed (v prefix)
-- cursor changed (c prefix)
--


-- Create table syntax for tbl_counts
-- CREATE TABLE TBL_COUNTS (
-- DBNAME VARCHAR2(10) not null,
-- TABLE_NAME VARCHAR2(30) not null,
-- ROW_COUNT NUMBER,
-- TIMESTAMP VARCHAR2(8) not null,
-- EXTENTS NUMBER,
-- SIZE_MB NUMBER
-- )
-- TABLESPACE &TABLE_TABLESPACE;
--
-- Unique index on tbl_counts.
-- CREATE UNIQUE INDEX TBL_COUNTS_UNIQUE
-- ON TBL_COUNTS(DBNAME, TABLE_NAME, TIMESTAMP)
-- TABLESPACE &INDEX_TABLESPACE;


/* Create the PROCEDURE...... */
create or replace procedure count_all_tables
is
/* define variables */
vDBNAME tbl_counts.dbname%TYPE;
vTBLNAME tbl_counts.table_name%TYPE;
vROW_CNT tbl_counts.row_count%TYPE;
vTODAY tbl_counts.timestamp%TYPE;
vEXTENTS tbl_counts.extents%TYPE;
vSIZE_MB tbl_counts.size_mb%TYPE;
/* cursor declaration */
CURSOR cTABLES is select table_name from user_tables where IOT_TYPE is null;
begin
/* set variables */
Select name into vDBNAME from v$database;
Select to_char((sysdate),'yyyymmdd') into vTODAY from sys.dual;
/* start cursor loop */
open cTABLES;
loop
fetch cTABLES into vTBLNAME;
exit when cTABLES%NOTFOUND;
/* count tables */
execute immediate 'Select count(*) from '||vTBLNAME into vROW_CNT;
execute immediate 'Select count(segment_name), round(sum(bytes)/(1024*1024)) from user_extents where segment_name='''||vTBLNAME||''' group by segment_name' into vEXTENTS, vSIZE_MB;
insert into TBL_COUNTS values (vDBNAME,vTBLNAME,vROW_CNT,vTODAY,vEXTENTS,vSIZE_MB);
commit;
end loop;
end;


-- Show any errors in the creating of the procedure
SHOW err;


-- Create a job to run procedure every day at 3 am (thats the 3/24 bit!)

DECLARE
my_jobno number;
BEGIN
-- Run the job at 2am every day
DBMS_JOB.SUBMIT(my_jobno,'COUNT_ALL_TABLES;',trunc(sysdate+1,'DD')+3/24,'trunc(sysdate+1,''DD'')+3/24',false);
END;
/

COMMIT;

-- View the new job for this package.

set line 200

COLUMN job heading JOB
COLUMN log_user FORMAT A9
COLUMN priv_user FORMAT A10
COLUMN schema_user FORMAT A12
COLUMN last_date FORMAT A10
COLUMN last_sec FORMAT A10
COLUMN this_date FORMAT A10
COLUMN this_sec FORMAT A10
COLUMN next_date FORMAT A10
COLUMN next_sec FORMAT A10
COLUMN broken FORMAT A8
COLUMN interval FORMAT A30
COLUMN what FORMAT A30

SELECT what,
job,
LOG_USER,
PRIV_USER,
SCHEMA_USER,
NEXT_DATE,
NEXT_SEC,
LAST_DATE,
LAST_SEC,
THIS_DATE,
THIS_SEC,
INTERVAL,
BROKEN
FROM DBA_JOBS;

clear columns;

-- run job now.

-- DECLARE
-- BEGIN
-- DBMS_JOB.RUN(xx);
-- END;
-- /