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
No comments:
Post a Comment