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.
a long way of doing it, and probba;y not very efficient either, but it works
set serveroutput on size 200000
cursor tabs is select table_name from tabs;
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);
Set Head off
Set Feedback off
Set Trimspool on
'Select Count(*) from '||Table_Name||';'
Owner = 'SCHEMA_NAME'
Set Head on
Set Feedback on
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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.
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.
-- 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
/* define variables */
/* cursor declaration */
CURSOR cTABLES is select table_name from user_tables where IOT_TYPE is null;
/* set variables */
Select name into vDBNAME from v$database;
Select to_char((sysdate),'yyyymmdd') into vTODAY from sys.dual;
/* start cursor 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);
-- Show any errors in the creating of the procedure
-- Create a job to run procedure every day at 3 am (thats the 3/24 bit!)
-- Run the job at 2am every day
-- 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