-
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
Last edited by diwakar; 09-24-2003 at 04:17 AM.
-
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.
funky...
"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"
-
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;
-- /
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|