Hi

I am using below for counting rows, but it's throwing table or view does not exist at line 7. Can you please guide me a better way without creation of fucntion or stored procedure on DB?

declare
v_count integer;
begin

for r in (select table_name,owner from all_tables at WHERE table_name not like'%$%' )
loop
execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;
INSERT INTO ROWCOUNT_TBL(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;

end;

Regards
Hari

execute immediate 'select count(*) from ' || r.TABLE_NAME into v_count;

The first reply by Tom in this discussion, in which he created a user defined function can now in 12c be done in single SQL without creating function but using the same function in WITH clause

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables

right?

What you hope to achieve? Can you please explain?

One thing is performance is better than creating the udf and calling it.

May be to see whichbtables are large enough to consider for partitioning

A valid reason to do it might be to validate an external ETL process that claims to have inserted a precise number of rows into a set of tables, and then stopped. If that process is the only one that can insert rows, the counts can be used to validate that claim. I can't think of another way to do it.

SQL%ROWCOUNT may have your answer.

The first solution Tom gave, and the solution I (Asim) gave on 8th August 2022 ( which is just copying the tom's function in the WITH clause), and the video of connor, none of us considered or said anything about the transaction isolation level, which means we are assuming default ie statement level, due to which the solution may produce read consistency issues of SQL calling PL/SQL calling SQL mentioned here

https://blogs.oracle.com/sql/the-problem-with-sql-calling-plsql-calling-sql


Here is the query for ready referrence

WITH
function get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;
select user, table_name,
get_rows(user||'.'||table_name) cnt
from user_tables


So, I think we should set the transaction isolation level to serializeable or read only to get read consistency, otherwise if some other users change the no. of rows (delete or insert) in any of the tables during execution of above query, the results will not be of same point in time

Hi Tom,
pls help me i have a requirement ,
in my database there are 50 tables i have to validate 10 tables only for count, without writing select count(*) from table...each time
I have to place required table names in single file and parameterize that file, so whatever the table names in that file that count should i need to get
pls put your comments asap

I am running Change Data Capture (Differential Extract) from Oracle to Microsoft Sql Server.
It has faults.
In order to troubleshoot, I compare the count of record on Oracle with the result count of record on MSSQL.
This gives me an idea what is wrong.

You can use the below query to view all rows without using count
-----------------------------------------------------------------------
SELECT TABLE_NAME, NUM_ROWS
FROM user_tables
WHERE TABLE_NAME IN (SELECT TABLE_NAME
FROM all_tables
WHERE OWNER = 'MISMIG' AND STATUS = 'VALID')

declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables WHERE owner <>'SYS')
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
10 /
declare
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at line 6


declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables )
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
10 /
declare
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 6


declare
v_count number(20);
begin
for r in (select table_name,owner from dba_tables where owner='SCOTT')
loop
execute immediate 'select count(1) from '|| r.owner||'.'||r.table_name into v_count;
INSERT INTO ROWCOUNT_TBL(table_name,schema_name,record_count,created) values (r.table_name,r.owner,v_count,SYSDATE);
8 end loop;
end;
10 /

PL/SQL procedure successfully completed.

SYS@nv_1<2023-12-01 10:35:46>DESC ROWCOUNT_TBL;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------
TABLE_NAME VARCHAR2(32)
SCHEMA_NAME VARCHAR2(32)
RECORD_COUNT NUMBER(20)
CREATED DATE

SYS@nv_1<2023-12-01 10:36:00>


why?

i AM USINF BELOW, WHAT I AM DOING WRONG ?

CREATE OR REPLACE FUNCTION OITLEAPUVVUN0.COUNT_ROWS (
p_TABLE_NAME IN VARCHAR2,
p_OWNER IN VARCHAR2 DEFAULT NULL,
p_EXPORT_SCN IN NUMBER)
RETURN NUMBER
AUTHID CURRENT_USER
IS
NUM_ROWS NUMBER;
STMT VARCHAR2(2000);
BEGIN
IF p_OWNER IS NULL THEN
STMT := 'SELECT /*+ PARALLEL(4) */ COUNT(*) FROM "' || p_TABLE_NAME || '" AS OF SCN ' || p_EXPORT_SCN;
ELSE
STMT := 'SELECT /*+ PARALLEL(4) */ COUNT(*) FROM "' || p_OWNER || '"."' || p_TABLE_NAME || '" AS OF SCN ' || p_EXPORT_SCN;
END IF;
EXECUTE IMMEDIATE STMT INTO NUM_ROWS;
RETURN NUM_ROWS;
END; -- FUNCTION COUNT_ROWS
/


>> SELECT OWNER, TABLE_NAME, COUNT_ROWS(TABLE_NAME,OWNER,11052875780992) ROW_COUNT
FROM DBA_TABLES
WHERE OWNER NOT IN (SELECT USERNAME
FROM DBA_USERS
WHERE ORACLE_MAINTAINED='Y'
OR USERNAME LIKE 'APEX%'
OR USERNAME in ('CRPGGADMIN'))
AND EXTERNAL = 'NO' ORDER BY OWNER
*
Error at line 1
ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at "OITLEAPUVVUN0.COUNT_ROWS", line 16
ORA-06512: at line 1

Never mind Figured out

SELECT OWNER, TABLE_NAME, COUNT_ROWS(TABLE_NAME,OWNER,##############) ROW_COUNT
FROM DBA_TABLES
WHERE OWNER NOT IN (SELECT USERNAME
FROM DBA_USERS
WHERE ORACLE_MAINTAINED='Y'
OR USERNAME LIKE 'APEX%'
OR USERNAME in ('CRPGGADMIN'))
AND EXTERNAL = 'NO'
AND (IOT_TYPE IS NULL OR IOT_TYPE != 'IOT_OVERFLOW');

I need to export a dump of a list of tables and provide the count for each table.

I require an exact count. I don't have the permissions to run stats.

Getting the DBA involved requires submitting a ticket that may take several days to fulfill.

A current pass through query selects all the records from a BIG table which is causing a performance issue as it is eating volatile memory. We need NUM_ROWS (or estimate) with the idea to tweak the pass through query into multiple pass through queries each with a limited amount of records thus way that it wont cause a performance (memory) issue.