12 Replies Latest reply on Jun 30, 2020 12:41 AM by Paulzip

    List BLOB and CLOB columns and their counts

    Julaayi

      Hello Experts,

      I am trying to list out BLOB/CLOB datatypes in a database based on the resources from here, here and here. However, I am getting duplicates with this for has_blob and has_clob columns. Could someone please help me with it?

       

      select  col.owner as schema_name,
              col.table_name, 
              t.num_rows, 
              CASE WHEN col.data_type = 'BLOB' THEN 'Y' ELSE 'N' END AS has_blob,
              CASE WHEN col.data_type = 'CLOB' THEN 'Y' ELSE 'N' END AS has_clob
            
      from sys.all_tab_columns col
      inner join sys.all_tables t on col.owner = t.owner 
                                    and col.table_name = t.table_name
      where 
      col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
         'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
         'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
         'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
         'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
         'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
      group by col.owner,
               col.table_name,
               t.num_rows,
               CASE WHEN col.data_type = 'BLOB' THEN 'Y' ELSE 'N' END,
              CASE WHEN col.data_type = 'CLOB' THEN 'Y' ELSE 'N' END
      order by col.table_name
      ;
      

       

      Thanks!

        • 1. Re: List BLOB and CLOB columns and their counts
          AndrewSayer

          Julaayi wrote:

           

          Hello Experts,

          I am trying to list out BLOB/CLOB datatypes in a database based on the resources from here, here and here. However, I am getting duplicates with this for has_blob and has_clob columns. Could someone please help me with it?

           

          1. selectcol.ownerasschema_name,
          2. col.table_name,
          3. t.num_rows,
          4. CASEWHENcol.data_type='BLOB'THEN'Y'ELSE'N'ENDAShas_blob,
          5. CASEWHENcol.data_type='CLOB'THEN'Y'ELSE'N'ENDAShas_clob
          6. fromsys.all_tab_columnscol
          7. innerjoinsys.all_tablestoncol.owner=t.owner
          8. andcol.table_name=t.table_name
          9. where
          10. col.ownernotin('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS',
          11. 'MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
          12. 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',
          13. 'WKPROXY','WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP','WKSYS',
          14. 'FLOWS_30000','FLOWS_FILES','MDDATA','ORACLE_OCM','XS$NULL',
          15. 'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','PUBLIC')
          16. ANDcol.owner='IDBS_EWB_CORE'
          17. groupbycol.owner,
          18. col.table_name,
          19. t.num_rows,
          20. CASEWHENcol.data_type='BLOB'THEN'Y'ELSE'N'END,
          21. CASEWHENcol.data_type='CLOB'THEN'Y'ELSE'N'END
          22. orderbycol.table_name
          23. ;

           

          Thanks!

          You will want to aggregate the has_blob and has_clob expressions for tables that have both. A max() will do the trick (as 'Y'>'N'), you will of course need to remove them from the group by.

          • 2. Re: List BLOB and CLOB columns and their counts
            Frank Kulash

            Hi,

             

            The subject line you gave this thread is "List BLOB and CLOB columns and their counts". If you' want the counts, you can do this:

            select  col.owner       as schema_name, 

                    col.table_name,  

                    t.num_rows,  

                    COUNT (CASE WHEN col.data_type = 'BLOB' THEN 'Y' END)  AS blob_col_count, 

                    COUNT (CASE WHEN col.data_type = 'CLOB' THEN 'Y' END)  AS clob_col_count         

            from       sys.all_tab_columns col 

            inner join sys.all_tables      t   on  col.owner      = t.owner  

                                               and col.table_name = t.table_name 

            where    col.owner = 'IDBS_EWB_CORE' 

            group by col.owner, 

                     col.table_name, 

                     t.num_rows 

            order by col.table_name 

            ;

            There's no point in having both conditions 

            col_owner not in (...)       and

            col_owner = 'IDBS_EWB_CORE'

            If the owner is IDBS_EWB_CORE, then it won't be any of the others.

            • 3. Re: List BLOB and CLOB columns and their counts
              Julaayi

              Hello AndrewSayer, thanks for your reply. When I remove the data_type in GROUP BY, I am not getting expected results. Do you mind posting the query for you what you meant? Thanks!

              • 4. Re: List BLOB and CLOB columns and their counts
                Julaayi

                Hello Frank, as mentioned in my post, I have taken references from three places to build that query. I am not looking for counts just for blobs but the entire table. Also, when I use sys_tables, I am getting incorrect count with comparison to count(*) on the respective table itself. Why is it so?

                • 5. Re: List BLOB and CLOB columns and their counts
                  Frank Kulash

                  Hi,

                   

                  I see you changed your original message after a couple of people had replied to it.  Please don't do that; it makes the thread very hard to follow.  Post all corrections or additions in a new reply.  The only change you should make to a posted message is to add a comment like  "EDIT: This is wrong; see reply #N for corrections."

                  Julaayi wrote:

                   

                  Hello Frank, as mentioned in my post, I have taken references from three places to build that query. I am not looking for counts just for blobs but the entire table.

                  Right; that's why you included all_tables.num_rows.

                  If the replies above are not producing the results you want, then what are the results you want?  Post CREATE TABLE and INSERT statements for a little sample data, and post the exact results you want from those tables.

                   

                   

                  Also, when I use sys_tables, I am getting incorrect count with comparison to count(*) on the respective table itself. Why is it so?

                  All_tables.num_rows tells approximately how many rows the table had the last time statistics were gathered.  Often it is the exact number, but it's not guaranteed to be. 

                  To get the exact number of rows at run-time requires dynamic SQL.  See How crate table count script? for one way to do that.

                  • 6. Re: List BLOB and CLOB columns and their counts
                    Paulzip

                    So you want count of rows for tables which have either a BLOB or a CLOB column?

                    • 7. Re: List BLOB and CLOB columns and their counts
                      Paulzip

                      with data as (

                        select owner, table_name, DBMS_XMLGen.GetXMLType('select count(*) cnt from '||owner||'.'||table_name) XML_Data

                        from sys.all_tables t

                        where (owner, table_name) in (

                            select owner, table_name

                            from sys.all_tab_columns col

                            where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',      -- Or whatever your owner filtering criteria is here

                                      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',

                                      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',

                                      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',

                                      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',

                                      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')

                              and col.data_type in ('BLOB', 'CLOB')

                        )

                      )

                      select d.owner, d.table_name, x.cnt

                      from data d,

                          XMLTable(

                            '/ROWSET/ROW'

                            passing d.XML_Data

                            columns

                              cnt integer path 'CNT'

                          ) x

                      order by 1, 2

                      • 8. Re: List BLOB and CLOB columns and their counts
                        Julaayi

                        Hi Paulzip, thanks for posting the query. Yes, I am looking for count of all records in the table as well as count of BLOB/CLOB records in the table (if possible). In addition, it would also be nice if I could get how many BLOB/CLOB columns are there in that table.

                         

                        BTW, your query is giving me an error.

                         

                        ORA-19202: Error occurred in XML processing

                        ORA-00942: table or view does not exist

                        ORA-06512: at "SYS.DBMS_XMLGEN", line 288

                        • 9. Re: List BLOB and CLOB columns and their counts
                          Julaayi

                          Hello Frank, the reason I had to edit my original post was to remove the real database names for security reasons. Thanks for clarifying about the counts with respect to stats. I think that makes sense.

                          • 10. Re: List BLOB and CLOB columns and their counts
                            Paulzip

                            Works for me. You probably don't have select permissions on one of the tables in the all_tables

                            • 11. Re: List BLOB and CLOB columns and their counts
                              Julaayi

                              I do have permissions on sys.all_tables. Are you referring to dba_all_tables? Then, I don't have that one.

                              • 12. Re: List BLOB and CLOB columns and their counts
                                Paulzip

                                Julaayi wrote:

                                 

                                I do have permissions on sys.all_tables. Are you referring to dba_all_tables? Then, I don't have that one.

                                I didn't say permissions on sys.all_tables, I said select permission on one of the tables returned by the view.

                                 

                                My query is effectively running

                                select count(*) from an_owner.a_table

                                For the owner tables the query on all_tables returns.

                                 

                                If you don't have "select" privilege on an_owner.a_table, it'll fail.  You could have say "update" privilege, but not select, so it might appear in the sys.all_tables, but you can't select from it.