record count of every table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: record count of every table

  1. #1
    Join Date
    May 2002
    Posts
    193

    record count of every table

    Dear All,

    I need to find out the no of records in each table under a particular user and there are around 3000 tables under that user. Now what is the best way to get the tablename and the record count of every table under that user.

    Any hints in this regard.....

    Thanks,

    Regards,

    K.Diwakar
    Last edited by diwakar; 09-24-2003 at 04:17 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    a long way of doing it, and probba;y not very efficient either, but it works

    set serveroutput on size 200000
    declare
    tabname varchar2(30);
    tempcount number(10);
    cursor tabs is select table_name from tabs;
    tab_record tabs%ROWTYPE;
    begin
    for tab_record in tabs loop
    fetch tabs into tabname;
    exit when tabs%NOTFOUND;
    dbms_output.put_line('Counting from ' ||tabname);
    execute immediate 'select count(*) from '||tabname into tempcount;
    dbms_output.put_line('Count = ' || tempcount);
    dbms_output.put_line('.....................................................');
    end loop;
    end;
    /

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    Set Head off
    Set Feedback off
    Set Trimspool on
    
    Spool C:\Apps\Base\Count_In_Table.sql
    
    Select 
      'Select Count(*) from '||Table_Name||';'
    From
      DBA_TABLES
    Where
      Owner  =  'SCHEMA_NAME'
    ;
    
    Spool off
    
    Set Head on
    Set Feedback on
    
    Spool C:\Apps\Logs\Count_In_Table.log
    
    @C:\Apps\Base\Count_In_Table.sql
    
    Spool off
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    May 2002
    Posts
    193
    Dear abhaysk and davey23uk,

    Thanks for your reply.

    It was very usefull indeed.

    Regards,

    K.Diwakar

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    the one problem is that wont give you the table name, just a count from it

  6. #6
    Join Date
    May 2002
    Posts
    193
    A better way is to query user_tables.

    This will solve the issue completely.

    Just got that idea..

    Regards,

    K.Diwakar

  7. #7
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    If you Analyze all your tables with COMPUTE STATISTICS (rather than ESTIMATE) then you can look at the NUM_ROWS field in your USER_TABLES (or DBA_TABLES) to see the table_name and the row count. If you are using CBO it might even help your overall performance to have current statistics.

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Better still, use DBMS_STATS and do it in parallel:


    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MY_SCHEMA', degree=>10, cascade=>TRUE);
    SELECT table_name, num_rows
    FROM user_tables
    ORDER BY table_name;


    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    I wrote something similar that runs nightly on our databases. It gathers counts, number of extents, size and then stores it all to a table so you have a nice history.

    It's not rocket science but can be usefull for spotting trends and figuring out when a table increased in size.

    Feel free to use it and hack it to death.

    Chris

    -- Author. Chris Watson
    -- Date. 11/10/02
    -- File. count_all_tables.sql
    -- Usage run as normal procedure (execute )
    -- Propose. PLSQL package to run a count(*) on all tables in a users schema.
    -- Needs a table called TBL_COUNTS to store all the results in.
    -- Also collects extent numbers and segment size (it shows segments less that
    -- 1/2 Mb as 0 (due to the round function)).
    --
    -- Enhancments. 1. Tablespaces as variable.
    -- 2. Table owner for systems with multiple schemas.
    -- 3. Timestamp renamed to datastamp
    --
    -- Change control
    -- NAME DATE WHAT
    -- Chris Watson 270203 Added table spaces to object creation and SQL to submit DBMS_JOB.
    -- for automatic running (3am every day)
    -- Chris Watson 190303 2 new columns on the table (EXTENTS and SIZE_MB)
    -- variable all changed (v prefix)
    -- cursor changed (c prefix)
    --


    -- Create table syntax for tbl_counts
    -- CREATE TABLE TBL_COUNTS (
    -- DBNAME VARCHAR2(10) not null,
    -- TABLE_NAME VARCHAR2(30) not null,
    -- ROW_COUNT NUMBER,
    -- TIMESTAMP VARCHAR2(8) not null,
    -- EXTENTS NUMBER,
    -- SIZE_MB NUMBER
    -- )
    -- TABLESPACE &TABLE_TABLESPACE;
    --
    -- Unique index on tbl_counts.
    -- CREATE UNIQUE INDEX TBL_COUNTS_UNIQUE
    -- ON TBL_COUNTS(DBNAME, TABLE_NAME, TIMESTAMP)
    -- TABLESPACE &INDEX_TABLESPACE;


    /* Create the PROCEDURE...... */
    create or replace procedure count_all_tables
    is
    /* define variables */
    vDBNAME tbl_counts.dbname%TYPE;
    vTBLNAME tbl_counts.table_name%TYPE;
    vROW_CNT tbl_counts.row_count%TYPE;
    vTODAY tbl_counts.timestamp%TYPE;
    vEXTENTS tbl_counts.extents%TYPE;
    vSIZE_MB tbl_counts.size_mb%TYPE;
    /* cursor declaration */
    CURSOR cTABLES is select table_name from user_tables where IOT_TYPE is null;
    begin
    /* set variables */
    Select name into vDBNAME from v$database;
    Select to_char((sysdate),'yyyymmdd') into vTODAY from sys.dual;
    /* start cursor loop */
    open cTABLES;
    loop
    fetch cTABLES into vTBLNAME;
    exit when cTABLES%NOTFOUND;
    /* count tables */
    execute immediate 'Select count(*) from '||vTBLNAME into vROW_CNT;
    execute immediate 'Select count(segment_name), round(sum(bytes)/(1024*1024)) from user_extents where segment_name='''||vTBLNAME||''' group by segment_name' into vEXTENTS, vSIZE_MB;
    insert into TBL_COUNTS values (vDBNAME,vTBLNAME,vROW_CNT,vTODAY,vEXTENTS,vSIZE_MB);
    commit;
    end loop;
    end;


    -- Show any errors in the creating of the procedure
    SHOW err;


    -- Create a job to run procedure every day at 3 am (thats the 3/24 bit!)

    DECLARE
    my_jobno number;
    BEGIN
    -- Run the job at 2am every day
    DBMS_JOB.SUBMIT(my_jobno,'COUNT_ALL_TABLES;',trunc(sysdate+1,'DD')+3/24,'trunc(sysdate+1,''DD'')+3/24',false);
    END;
    /

    COMMIT;

    -- View the new job for this package.

    set line 200

    COLUMN job heading JOB
    COLUMN log_user FORMAT A9
    COLUMN priv_user FORMAT A10
    COLUMN schema_user FORMAT A12
    COLUMN last_date FORMAT A10
    COLUMN last_sec FORMAT A10
    COLUMN this_date FORMAT A10
    COLUMN this_sec FORMAT A10
    COLUMN next_date FORMAT A10
    COLUMN next_sec FORMAT A10
    COLUMN broken FORMAT A8
    COLUMN interval FORMAT A30
    COLUMN what FORMAT A30

    SELECT what,
    job,
    LOG_USER,
    PRIV_USER,
    SCHEMA_USER,
    NEXT_DATE,
    NEXT_SEC,
    LAST_DATE,
    LAST_SEC,
    THIS_DATE,
    THIS_SEC,
    INTERVAL,
    BROKEN
    FROM DBA_JOBS;

    clear columns;

    -- run job now.

    -- DECLARE
    -- BEGIN
    -- DBMS_JOB.RUN(xx);
    -- END;
    -- /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width