Finding Total Table Count in a Schema in Oracle:
SELECT COUNT(*) Total_Tables FROM DBA_TABLES WHERE OWNER = 'EGRD_HC_2A';
Tables with Row Count:
1) SELECT TABLE_NAME, NUM_ROWS, TABLESPACE_NAME, AVG_ROW_LEN, COMPRESSION FROM DBA_TABLES WHERE OWNER = 'EGRD_HC_2A' ORDER BY NUM_ROWS desc;
2) SELECT TABLE_NAME, NUM_ROWS, TABLESPACE_NAME, AVG_ROW_LEN, COMPRESSION FROM USER_TABLES WHERE TABLESPACE_NAME IN('SYSTEM','EGRD_HC_2A') ORDER BY NUM_ROWS desc;
3)
DECLARE
val NUMBER;
BEGIN
FOR I IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || i.table_name INTO val;
DBMS_OUTPUT.PUT_LINE('Table: ' ||i.table_name || ', Rows: ' || val );
END LOOP;
END;
/
Monday, September 18, 2017
Row Count in Table wise in Oracle
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment