ERROR :Table or view not found while running this query

Question

select
  user_tables.table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||user_tables.table_name))
    ,'/ROWSET/ROW/C')) count

from all_tables user_tables 
   inner join all_tab_columns col on col.table_name=user_tables.table_name 
where col.column_name='EMPLOYEE_ID'
order by 
   user_tables.table_name;

Answer 1

You are querying all_tables, which will give you the names of tables that you have access to in other schemas as well as those you own. But you are not qualifying the table name with the schema name. You need to include that in the getxml() call:

xmltype(dbms_xmlgen.getxml('select count(*) c from '
   || '"' || user_tables.owner || '"."' || user_tables.table_name || '"'))

I've also quoted the schema and table names just in case you have any with quoted identifiers (mixed case or illegal characters in the stored table name).

You are also getting (or could get) duplicates, again because of the owner; if the same table name exists in multiple schemas the join will link them all; so include the owner in the join:

...
from all_tables user_tables 
inner join all_tab_columns col
on col.owner = user_tables.owner and col.table_name = user_tables.table_name 
where col.column_name='EMPLOYEE_ID'
...

I'd also include the owner in the output, pick a less confusing table alias, and use XMLQuery instead of the deprecated extractvalue():

select
  tabs.owner,
  tabs.table_name,
  to_number(
    xmlquery('/ROWSET/ROW/C/text()' passing
      xmltype(dbms_xmlgen.getxml('select count(*) c from '
         || '"' || tabs.owner || '"."' || tabs.table_name || '"'))
    returning content)) count
from all_tables tabs
inner join all_tab_columns cols
on cols.owner = tabs.owner and cols.table_name = tabs.table_name 
where cols.column_name = 'EMPLOYEE_ID'
order by tabs.owner, tabs.table_name;

which on a pretty-vanilla 12cR2 instance connected as HR gives:

OWNER                          TABLE_NAME                          COUNT
------------------------------ ------------------------------ ----------
APEX_050100                    WWV_FLOW_FND_USER                       3
HR                             EMPLOYEES                             107
HR                             JOB_HISTORY                            10
HRREST                         EMPLOYEES                             107
HRREST                         JOB_HISTORY                            10
OBE                            OEHR_EMPLOYEES                        107
OBE                            OEHR_JOB_HISTORY                       11

7 rows selected. 

With your original code I'd get the same error you did, because it would try to count rows in - for instance - the OEHR_EMPLOYEES table, which is not in my schema. I'd see the same error directly querying from OEHR_EMPLOYEES, but I'd be OK with from OBE.OEHR_EMPLOYEES. My modification adds that schema name to the count query. And if it got that far, it would count the rows in my EMPLOYEES table four times because of the loose join, rather than mine once and HRREST's once.