Monday, September 18, 2017

Row Count in Table wise in Oracle

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;
    /