1 2 Previous Next 23 Replies Latest reply on Nov 21, 2010 4:54 PM by MichaelS Go to original post
      • 15. Re: count(*) for all tables
        Aketi Jyuuzou
        I have arranged yours :-)
        conn scott/tiger
        col cnt for 9999
        
        select table_name,
        (select to_number(column_value)
           from xmltable(('count(ora:view("' || table_name || '"))'))) as cnt
        from user_tables
        order by table_name;
        
        TABLE_NAME  CNT
        ----------  ---
        BONUS         0
        DEPT          4
        EMP          14
        SALGRADE      5
        • 16. Re: count(*) for all tables
          MichaelS
          No need for subqueries in recent versions:
          SQL> select table_name,
                      xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))') returning content) as int) cnt
              from user_tables
             where table_name in ('EMP', 'DEPT', 'BONUS')
          order by table_name
          /
          TABLE_NAME                                                CNT
          --------------------------------------------- ---------------
          BONUS                                                       0
          DEPT                                                        5
          EMP                                                        14
          
          3 rows selected.
          ;)
          • 17. Re: count(*) for all tables
            For some reason I am getting ora-932 when running this.
            As I have never used XML, I don't understand what your code (and the other code) does.
            I'm not using LONGs in the scheme I'm connected to, but I do use LOBs.
            SQL> ed
            file afiedt.buf is weggeschreven.
            
              1  select table_name,
              2              xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))')
             returning content) as int) cnt
              3      from user_tables
              4* order by table_name
            SQL> /
                from user_tables
                     *
            FOUT in regel 3:
            .ORA-00604: Fout opgetreden bij recursief SQL-niveau 1.
            ORA-00932: inconsistente gegevenstypen: NUMBER verwacht, LONG gekregen
            --------------
            Sybrand Bakker
            Senior Oracle DBA
            • 18. Re: count(*) for all tables
              MichaelS
              What version are you on? Lobs shouldn't be a problem:
              SQL> select * from v$version where rownum = 1
              /
              BANNER                                                                          
              --------------------------------------------------------------------------------
              Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production          
              1 row selected.
              
              SQL> create table t as select to_blob('9D9D81') bl from dual
              /
              Table created.
              
              SQL> select table_name,
                          xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))') returning content) as int) cnt
                  from user_tables
                 where table_name in ('T')
              order by table_name
              /
              TABLE_NAME                                                CNT
              --------------------------------------------- ---------------
              T                                                           1
              1 row selected.
              You may also first try without XMLCAST.
              • 19. Re: count(*) for all tables
                I am on 11.2.0.*1*.0

                And it appears to work on your dummy table, but crashes (also without using xmlcast) without limiting the tables.

                --------
                Sybrand Bakker
                Senior Oracle DBA
                • 20. Re: count(*) for all tables
                  Aketi Jyuuzou
                  Mice one MichaelS :-)
                  SQL> select * from v$version;
                  
                  BANNER
                  --------------------------------------------------------
                  Oracle Database 11g Release 11.2.0.1.0 - Production
                  PL/SQL Release 11.2.0.1.0 - Production
                  CORE    11.2.0.1.0      Production
                  TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                  NLSRTL Version 11.2.0.1.0 - Production
                  
                  SQL> conn scott/tiger
                  接続されました。
                  SQL>
                  SQL> select table_name,
                    2  xmlcast (xmlquery ( ('count(ora:view("' || table_name || '"))')
                    3           returning content) as int) as cnt
                    4    from user_tables;
                  
                  TABLE_NAME  CNT
                  ----------  ---
                  SALGRADE      5
                  BONUS         0
                  EMP          14
                  DEPT          4
                  • 21. Re: count(*) for all tables
                    730915
                    SomeoneElse,

                    One question,
                    I am not seeing same count on select count(*) from mytable and select num_rows from user_tables where table_name='MYTABLE'.
                    I under stand oracle gets the num_rows when it analyses the table last time . Now the question is when do you decide to analyze the table ?Does oracle anlyze table internally ?

                    Thanks,
                    Mahesh
                    • 22. Re: count(*) for all tables
                      The code Michaels posted is non-functional, so it can not qualify as 'nice one'

                      ----------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 23. Re: count(*) for all tables
                        MichaelS
                        The code Michaels posted is non-functional
                        Well obviously it is functional in principle, as shown! We just don't know it's complete limitations yet ... I bet it is some kind of table types which are a bit uncommon as e.g. iot tables:
                        SQL> select table_name, xmlquery ( ('count(ora:view("' || owner || '","' || table_name || '"))') returning content) cnt
                          from all_tables
                         where owner = 'SYS' and iot_name is not null
                        /
                               *
                        Error at line 2
                        ORA-00604: error occurred at recursive SQL level 1
                        ORA-25191: cannot reference overflow table of an index-organized table
                        1 2 Previous Next