View Logs for Data Validation
To validate an external table, use the procedures DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE
, DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE
, and
DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE
.
After you validate your source files you can see the result of the validate operation by querying a load operations table:
-
dba_load_operations
: shows all validate operations. -
user_load_operations
: shows the validate operations in your schema.
You can use these files to view load validation information. For example use this
select operation to query user_load_operations
:
SELECT table_name, owner_name, type, status, start_time, update_time, logfile_table, badfile_table
FROM user_load_operations
WHERE type = 'VALIDATE';
TABLE_NAME OWNER_NAME TYPE STATUS START_TIME UPDATE_TIME LOGFILE_TABLE BADFILE_TABLE
------------- ---------- ---------- ---------- -------------- ------------- --------------- ---------------
CHANNELS_EXT SH VALIDATE COMPLETED 13-NOV-17... 13-NOV-17... VALIDATE$21_LOG VALIDATE$21_BAD
Using this SQL statement with the WHERE
clause on the
TYPE
column displays all of the load operations with type
VALIDATE
.
The LOGFILE_TABLE
column shows the name of the table you can query to
look at the log of a validate operation. For example, the following query shows the
log for this validate operation:
SELECT * FROM VALIDATE$21_LOG;
The column BADFILE_TABLE
shows the name of the table you can query
to look at the rows where there were errors during validation. For example, the
following query shows the rejected records for the above validate operation:
SELECT * FROM VALIDATE$21_BAD;
Depending on the errors shown in the log and the rows shown in the
BADFILE_TABLE
, you can correct the error by dropping the
external table using the DROP TABLE
command and recreating it by
specifying the correct format options in DBMS_CLOUD.CREATE_EXTERNAL_TABLE
, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE
.
The
LOGFILE_TABLE
and BADFILE_TABLE
tables are stored for two days for each validate operation and then removed automatically.
Parent topic: Query External Data with Autonomous Database