When trying to run a Discover scan against an Oracle database, the scan completes in a few seconds and does not return any results.
One common cause for this problem is an incorrect table query, which causes the Discover scanner to be unable to list the tables to be scanned in the Oracle database.
Check the sqldatabasecrawler.properties file to see what table query is currently being used. You can find this file on the Discover server in \Vontu\Protect\config (Windows) or /opt/Vontu/Protect/config (Linux).
Below is a table query that works in many environments. You may need to modify this query to suit your Oracle installation; consult your database administrator if you need more information.
driver_table_query.oracle = select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW')
To test the table query, perform the following steps:
select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW');
To change the query used by the Discover scan, perform the following steps:
driver_table_query.oracle = select ut.table_name, ut.iot_type from user_tables ut where not exists (select unt.table_name from user_nested_tables unt where unt.table_name = ut.table_name) and (ut.iot_type is null or not ut.iot_type = 'IOT_OVERFLOW')
driver_table_query.oracle = select dt.owner||'.'||dt.table_name from dba_tables dt where not exists (select dnt.table_name from dba_nested_tables dnt where dnt.table_name = dt.table_name and dnt.owner = dt.owner) and (dt.iot_type is null or not dt.iot_type = 'IOT_OVERFLOW')
This will query the system dictionary rather than the user dictionary. This query requires that the scanning account be granted select on dba_tables and dba_nested_tables.
Another cause, in some cases (possibly including Oracle RAC installations), the database connection string needs to use a fully qualified name, rather than the Oracle database SID alone. This problem causes an error in the ScanDetail log (ORA-12154: TNS:could not resolve service name).
To resolve this problem, change the database connection string from the standard format (oracle:@//oracleserver.company.com:1521/mydatabase) to the following format: (oracle:@//oracleserver.company.com:1521/mydatabase.mydomain.net).