Results 1 to 8 of 8

Thread: Row Counts

  1. #1
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Row Counts

    Dear Team,

    I need a query showing record counts for all tables in your database.
    For e.g.

    Suppose there are three tables A,B,C

    The query should return

    Table Name Record Count
    __________________________________
    A 1000
    B 200
    C 1750

    Without using script


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Row Counts

    select table_name, num_rows from user_tables order by table_name;
    OR
    select table_name, num_rows from ALL_tables
    order by table_name;


  3. #3
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Re: Row Counts

    Quote Originally Posted by susarlasireesha View Post
    select table_name, num_rows from user_tables order by table_name;
    OR
    select table_name, num_rows from ALL_tables
    order by table_name;

    Dear susarlasireesha,

    I had already tried the above two queries before. But its not giving the correct result for all tables.

    Is there any other option.

    Thanks for your kind reply


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Row Counts

    If you are trying forall the tables in the database ,you may not have required perviliges for the same.


  5. #5
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Re: Row Counts

    Quote Originally Posted by debasisdas View Post
    If you are trying forall the tables in the database ,you may not have required perviliges for the same.
    Dear debasisdas,

    Thank you for your kind reply.

    I am trying this only for FINANCE schema for which i have all the privileges.
    There are more than 300 tables which fall under my schema.


  6. #6
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Row Counts

    Quote Originally Posted by sreekumar_nair_it View Post
    Dear susarlasireesha,

    I had already tried the above two queries before. But its not giving the correct result for all tables.

    Is there any other option.

    Thanks for your kind reply
    Try this
    Sql > Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
    from user_tables
    order by table_name
    Sql > Set heading off
    Sql > Spool tablescount
    Sql > /
    Sql > Spool off
    Sql > @ tablescount


  7. #7
    Contributing Member
    Join Date
    Nov 2007
    Answers
    53

    Re: Row Counts

    Quote Originally Posted by susarlasireesha View Post
    Try this
    Sql > Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
    from user_tables
    order by table_name
    Sql > Set heading off
    Sql > Spool tablescount
    Sql > /
    Sql > Spool off
    Sql > @ tablescount
    Dear susarlasireesha,

    Thank you for your kind reply.

    But i needed a single query instead of script.


  8. #8
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Row Counts

    use This but This must be run in sys user other wise
    ur user must have sufficient privileges
    select
    table_name,
    to_number(
    extractvalue(
    xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
    from ALL_tables ;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact