
-- This won't give latest record count
SELECT table_name,num_rows FROM all_tables WHERE owner = 'TRAINING';

--
create table TRAINING.NAMES
(
first_name varchar(10)
);
--DROP TABLE  TRAINING.NAMES

select count(*) from TRAINING.NAMES;

insert into TRAINING.NAMES (first_name) values ('WOT');
insert into TRAINING.NAMES (first_name) values ('TECH');

select count(*) from TRAINING.NAMES;

EXEC DBMS_STATS.gather_table_stats('TRAINING', 'NAMES');

SELECT table_name,num_rows FROM all_tables WHERE owner = 'TRAINING';

--METHOD-1:

CREATE TABLE TRAINING.STATS AS SELECT table_name, num_rows FROM all_tables WHERE 1=2;
-- DROP TABLE TRAINING.STATS
DESC TRAINING.STATS;

DECLARE
val integer;
BEGIN
for i in (SELECT table_name FROM all_tables WHERE owner = 'TRAINING')
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) from TRAINING.' || i.table_name INTO val;
INSERT INTO TRAINING.STATS VALUES (i.table_name,val);
END LOOP;
END;
/

SELECT * FROM TRAINING.STATS;

--METHOD-2:

SELECT table_name,
TO_NUMBER(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) cnt from '||owner||'.'||table_name)),'/ROWSET/ROW/CNT')) as count
FROM all_tables
WHERE owner = 'TRAINING';





