1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Count Records of All Tables in Oracle

Discussion in 'SQL PL/SQL' started by arivazhagan, Mar 3, 2011.

  1. arivazhagan

    arivazhagan Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    75
    i have so many tables..examples hari,ram,ravi,etc...
    ten i want count of each table in single query....reply me
    output:
    ram 30
    ravi 21
    ...
    .
    .
    etc
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: urgent

    This is one way of doing it . (in Oracle 10g g and above)

    Code (SQL):

    SELECT TABLE_NAME,
    TO_NUMBER(EXTRACTVALUE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECT COUNT(*) CNT FROM '||TABLE_NAME)),'/ROWSET/ROW/CNT')) AS COUNT
    FROM USER_TABLES
     
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Prefix all your table_names with some string or character say 'B'.. then it will be very useful

    here is a pl/sql program for your requirement..

    Code (SQL):
    DECLARE
    CURSOR C1 IS SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'B%';
    l_query varchar2(300);
    l_num pls_integer;
    BEGIN
    FOR REC IN C1 LOOP
    l_query := 'SELECT COUNT(*) FROM ' || REC.TABLE_NAME;
    EXECUTE IMMEDIATE  l_query INTO l_num;
    dbms_output.put_line(rec.TABLE_NAME||' '||l_num);
    END loop;
    exception
    WHEN others THEN
    dbms_output.put_line(sqlerrm);
    END;
    /
    drop a msg here after your testing is over.
    best of luck..
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Actually the script should be rewritten as below :)

    Code (SQL):

    DECLARE
    CURSOR C1 IS SELECT owner,TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'B%';
    l_query varchar2(300);
    l_num pls_integer;
    BEGIN
    FOR REC IN C1 LOOP
    l_query := 'SELECT COUNT(*) FROM ' || REC.owner||'.'||REC.TABLE_NAME;
    EXECUTE IMMEDIATE  l_query INTO l_num;
    dbms_output.put_line(REC.owner||'.'||REC.TABLE_NAME||':'||l_num);
    END loop;
    exception
    WHEN others THEN
    dbms_output.put_line(sqlerrm);
    END;
     
     
    kiran.marla likes this.
  5. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    Hi,

    You can use the following query to generate the rows in each table in your database

    select table_name,num_rows from all_tables
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    num_rows from All_tables/user_tables will not give you the exact count unless the tables is analysed.
     
  7. ora

    ora Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    The following may assists you.
    1) exec dbms_stats.gather_schema_stats('ONWER NAME'); -- Will analyze entire schema.
    then do
    select table_name, num_rows from user_tables/all_tables;

    2) declare
    n number;
    begin
    for i in (select table_name from user_tables) loop
    execute immediate' select count(*) from '||i.table_name into n;
    dbms_output.put_line('Table Name: '||i.table_name||' Count of Row''s: '||n);
    end loop;
    end;

    Thanks & Regards,
    Oracle Learner
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also you can create a function and call it in a straight SQL as Tom explains
     
  9. finaluser

    finaluser Guest

    Thanks rajavu