Wednesday, October 31, 2012

A query to check that which oracle table have data and which table is empty?

The following query give the solution that which oracle table have data and which table is empty?


 select table_name,  
      extractvalue(  
       dbms_xmlgen.getXMLtype  
        ('SELECT 1 val FROM dual WHERE EXISTS(SELECT ''x'' FROM '||table_name||')'),  
         '/ROWSET/ROW/VAL') rows_in_table  
   from user_tables  
   where -- a real table  
    ( tablespace_name is not null or partitioned='YES' or nvl(iot_type,'NOT_IOT')='IOT' )  
    -- not an iot overflow  
  and nvl(iot_type,'NOT_IOT') not in ('IOT_OVERFLOW','IOT_MAPPING')   
    -- not a mview container  
  and (user, table_name) not in (select owner, container_name from user_mviews)  
    -- not a mview log  
  and (user, table_name) not in (select log_owner, log_table from user_mview_logs)  
   order by 1