I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes:
DECLARE
v_owner varchar2(40);
v_table_name varchar2(40);
cursor get_tables is
select distinct table_name,user
from user_tables
where lower(user) = 'SCHEMA_NAME';
begin
open get_tables;
fetch get_tables into v_table_name,v_owner;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM v_table_name;
CLOSE get_tables;
END;
This should do it:
declare
v_count integer;
begin
for r in (select table_name, owner from all_tables
where owner = 'SCHEMA_NAME')
loop
execute immediate 'select count(*) from '
|| r.owner || '.' || r.table_name
into v_count;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;
I removed various bugs from your code.
Note: For the benefit of other readers, Oracle does not provide a table called STATS_TABLE
, you would need to create it.