1 2 Previous Next 19 Replies Latest reply: Apr 23, 2013 9:08 PM by Frank Kulash RSS

    Count all Tables

    yxes2013
      Hi All,

      11.2.0.1
      OEL 6.4

      I want to count all tables in our database to check which one is growing. (well not really that fast) ;)

      Some helpful samaritan post me this pl/sql program:
      SQL> create table temptable (owner varchar2(30), table_name varchar2(30), row_co                      unt number, record_date date);
      
      Table created.
      
      SQL> declare
        2  counter number;
        3  cursor cur is select owner, table_name from dba_tables;
        4  begin
        5  for rec in cur loop
        6  execute immediate 'select count(*)  from '||rec.owner||'.'||rec.table_name                       into counter;
        7  insert into temptable values (rec.owner,rec.table_name,counter,sysdate);
      commit;
        8    9  counter := 0;
       10  end loop;
       11   end;
      / 12
      declare
      *
      ERROR at line 1:
      ORA-01031: insufficient privileges
      ORA-06512: at line 6
      I am already logged in as SYSTEM. :(



      If I use "SYS" I get different error.
      SQL> declare
        2  counter number;
        3  cursor cur is select owner, table_name from dba_tables;
        4  begin
        5  for rec in cur loop
        6  execute immediate 'select count(*)  from '||rec.owner||'.'||rec.table_name into counter;
        7  insert into temptable values (rec.owner,rec.table_name,counter,sysdate);
        8  commit;
        9  counter := 0;
       10  end loop;
       end;
       11   12  /
      declare
      *
      ERROR at line 1:
      ORA-00911: invalid character
      ORA-06512: at line 6
      Thanks a lot,
      zxy

      Edited by: yxes2013 on 23.4.2013 18:34
        • 1. Re: Count all Tables
          sb92075
          yxes2013 wrote:
          Hi All,

          11.2.0.1
          OEL 6.4

          I want to count all tables in our database to check which one is growing fast every minute.
          do you suffer from Compulsive Tuning Disorder or Obsessive/Compulsive disorder?
          You can't see uncommited DML which can take hours to complete.
          It would take you HOURS to count every row in every table in any decent sized databased.
          This idea is strictly delusional & totally unrealistic!
          • 2. Re: Count all Tables
            asahide
            Hi,
            I want to count all tables in our database to check which one is growing fast every minute.
            I think that you might be able to check dba_segments or dba_extents.


            Regards,
            • 3. Re: Count all Tables
              rp0428
              >
              I want to count all tables in our database to check which one is growing fast every minute.
              >
              For me the term 'growing' means the actual space being used is increasing.

              Oracle already keeps track of that for every segment for you.

              See DBA_SEGMENTS in the Database Reference
              http://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_4264.htm
              >
              BYTES NUMBER Size, in bytes, of the segment
              BLOCKS NUMBER Size, in Oracle blocks, of the segment
              >
              That information will be for ALLOCATED bytes and blocks and will include blocks that have been allocated for that segment but not yet used. But the fact that they are allocated for a segment means they can NOT be used by other segments.

              And if you keep your stats up to date (e.g. by a nightly batch job) then Oracle collects the actual USED information for you.

              See ALL_TABLES in the Database Reference
              http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2105.htm#i1592091
              >
              NUM_ROWS* NUMBER Number of rows in the table
              BLOCKS* NUMBER Number of used data blocks in the table
              EMPTY_BLOCKS NUMBER
              >
              There are your 'counts'.

              If you need frequent numbers use the DBA_SEGMENTS info for ALLOCATED space - that is what might cause you to run out of space.
              • 4. Re: Count all Tables
                Frank Kulash
                Hi,

                Interesting problem!

                Whenever you do dynamic SQL, you show put the dynamic command into a string variable, so you can easily disply it for debugging purposes, like this:
                 declare
                      counter     number;
                      cursor cur is select owner, table_name from dba_tables;
                      sql_txt     VARCHAR2 (200); 
                  begin
                      for rec in cur loop
                             sql_txt := 'select count(*)  from ' || rec.owner || '.' || rec.table_name;
                       dbms_output.put_line (sql_txt || ' = sql_txt');
                          execute immediate  sql_txt   into counter;
                          insert into temptable values (rec.owner,rec.table_name,counter,sysdate);
                         commit;
                         counter := 0;
                     end loop;
                end;
                / 
                I ran this, and got the same error you did:
                ...
                select count(*)  from SYS.ACCESS$ = sql_txt
                select count(*)  from SYS.LINK$ = sql_txt
                 declare
                *
                ERROR at line 1:
                ORA-01031: insufficient privileges
                ORA-06512: at line 9
                So it looked like SYSTEM can't query SYS.LINK$. I tried that explicitly, and got the same error. Sorry, I don't know why.

                The problem with SYS probably hs to do with non-standard table names, that must be enclose in double-quotes. It doesn't hurt to enclose any table name in double-quotes, as long as it's capitalized the sae way it appears in db_tables. Since we're getting the information from dba_tables, we're all set:
                 declare
                      counter     number;
                      cursor cur is select owner, table_name from dba_tables;
                      sql_txt     VARCHAR2 (200); 
                  begin
                      for rec in cur loop
                             sql_txt := 'select count(*)  from "' || rec.owner || '"."' || rec.table_name || '"';
                       dbms_output.put_line (sql_txt || ' = sql_txt');
                          execute immediate  sql_txt   into counter;
                          insert into temptable values (rec.owner,rec.table_name,counter,sysdate);
                         commit;
                         counter := 0;
                     end loop;
                end;
                / 
                When I ran that as SYS, I moved on to another error:
                *
                ERROR at line 1:
                ORA-25191: cannot reference overflow table of an index-organized table
                ORA-06512: at line 9
                • 5. Re: Count all Tables
                  yxes2013
                  It would take you HOURS to count every row in every table in any decent sized databased.
                  Not in our servers dear ;) its fast.
                  This idea is strictly delusional & totally unrealistic!
                  Not for me :P its very realistic on the contrary
                  • 6. Re: Count all Tables
                    Bill Haverberg
                    Fortunately, you ran into permissions errors early in this, preventing you from slowing down the database and causing problems for everyone else.

                    It would help to understand why you are trying to do this. If you were a DBA you'd (1) have the permissions to do this, and (2) the training to know there are better ways to check on the system.

                    So this means you are attempting to get some metrics on tables you either have responsibility on, or for which your immediate group has responsibility for. If your attempt was to get metrics for the entire system, well, that really is something that is out of scope for you.

                    Your DBA is probably analyzing tables on a nightly basis (if they aren't, ask them to). The results can be used to get fined-toothed metrics for the tables being looked at, and can give yiou historical data such as number of blocks and extants that will tell you how rapidly a table is growing in size over time. You can also get good information from the various data dictionaries, and you can pull from them on a nightly basis as well to get an angle on what you are looking for without imposing load on your system.
                    • 7. Re: Count all Tables
                      yxes2013
                      I think that you might be able to check dba_segments or dba_extents.
                      If the table just inserted 1 row or updated 1, or delete row it does not move :(

                      I also monitor which one is moving.

                      Thanks,
                      • 8. Re: Count all Tables
                        Frank Kulash
                        Hi,

                        This ran for me as SYS:
                        declare
                              counter     number;
                              cursor cur is select  owner
                                                 ,          table_name 
                                      from    dba_tables
                                      WHERE   NVL (iot_type, '?')     != 'IOT_OVERFLOW';
                              sql_txt     VARCHAR2 (200); 
                          begin
                              for rec in cur loop
                                     sql_txt := 'select count(*)  from "' || rec.owner || '"."' || rec.table_name || '"';
                        --       dbms_output.put_line (sql_txt || ' = sql_txt');
                                  execute immediate  sql_txt   into counter;
                                  insert into temptable values (rec.owner,rec.table_name,counter,sysdate);
                                 commit;
                                 counter := 0;
                             end loop;
                        end;
                        /
                        There are some good questions above about is this a really smart thing to do. If it provides you with useful information, and doesn't hurt your system's performance too much, that's great; otherwise, look for ways to identify the tables you're most interested in, and only monitor them, using Oracle-supplied auditing tools if possible. The General Database Discussions forum might be a better place to ask how to do that.
                        • 9. Re: Count all Tables
                          rp0428
                          >
                          If the table just inserted 1 row or updated 1, or delete row it does not move

                          I also monitor which one is moving.
                          >
                          Counting rows won't tell you anything about updates.

                          And, for me, if inserting a row doesn't require any additional space (because the row fits into an existing block) the table hasn't 'grown'.

                          Any extent allocations DO MOVE the values in those system views.
                          • 10. Re: Count all Tables
                            yxes2013
                            Your the Man! frank ;)

                            See its just 10 seconds counting all 2,800 tables in dba_tables :D you can try it mr sb.
                            • 11. Re: Count all Tables
                              sb92075
                              yxes2013 wrote:
                              It would take you HOURS to count every row in every table in any decent sized databased.
                              Not in our servers dear ;) its fast.
                              Interesting & unsubstantiated claim; since you don't have any working code to do any timing tests!
                              • 12. Re: Count all Tables
                                yxes2013
                                Hi gain frank and all,

                                I want to add another column counter2, and instead of inserting I want to update the existing tables with new count so I can have history.
                                How do I do that?

                                Thanks
                                • 13. Re: Count all Tables
                                  rp0428
                                  >
                                  I want to add another column counter2, and instead of inserting I want to update the existing tables with new count so I can have history.
                                  How do I do that?
                                  >
                                  You don't.

                                  The way to add 'history' is to add a DATE column to the data and populate it when you run your counts.

                                  Every time you run your counts add new records to the table; that gives you your history using a normalized data model.

                                  And as a bonus your 'count' query will give you a 'count' of the number of records in your 'count history' table so you can monitor how fast your count history is growing! ;)
                                  • 14. Re: Count all Tables
                                    Frank Kulash
                                    Hi,
                                    yxes2013 wrote:
                                    Hi gain frank and all,

                                    I want to add another column counter2, and instead of inserting I want to update the existing tables with new count so I can have history.
                                    How do I do that?
                                    Sorry, I don't understand.
                                    This sounds like a completely separate question, so maybe you should start a complerly separate thread. The fact that this problem just happens to occur in the same project as the previous problem doesn't seem to be significant. Also, not that many people are going to reply to a question that's already "Answered", and has a dozen replies.

                                    Your existing table has a DATE column, where you are storing the time that the count ws taken, so you can have any number of measurments for the same table. What kind of history do you want that you can't get already?

                                    Post a complete scenario, such as:
                                    "I'm populatng this log table: CREATE TABLE ...
                                    Say at 21:00, I collect this information on tables x, y and z: INSERT ...
                                    Then, at 21:05, the data on those same 3 tables is: INSERT ...
                                    But I don't want to add a new row, because ... I want ... because ... so I can get output like ..."

                                    If you want to INSERT in some situations, and UPDATE in others, that sound like a job for MERGE.
                                    1 2 Previous Next