-- Get a listing of AWS-RDS Oracle database log

SELECT -- *
  record_id, message_text, originating_timestamp
FROM listnerlog
ORDER BY record_id;

SELECT -- *
   message_text, originating_timestamp
FROM alertlog
;

-- The database timezone is set to UTC.  The timezone offset can be changed 
-- by executing script shown below

exec RDSADMIN.RDSADMIN_UTIL.alter_db_time_zone(p_new_tz => '-4:00');


-- Script to change the trace file retention period (to 24 hours)

exec RDSADMIN.RDSADMIN_UTIL.set_configuration('tracefile retention', 1440);


-- Get a list of files that can be used for data migration

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.listdir('DATA_PUMP_DIR'))
ORDER BY mtime;

-- Get export file (log) details that is used for data migration

SELECT * FROM
 TABLE(RDSADMIN.RDS_FILE_UTIL.read_text_file(
        p_directory => 'DATA_PUMP_DIR',
        p_filename  => 'exp_file.log')         --use exact log filename
  );

-- Get a list of files that are created in the ADUMP directory.  To execute this 
-- query, user needs to have READ access on the ADUMP directory

SELECT
 SUBSTR(filename,-3) file_type,
 TO_CHAR(MIN(mtime),'mm/dd/yyyy') earliest_file,
 TO_CHAR(MAX(mtime),'mm/dd/yyyy') latest_file,
 COUNT(*) counts
FROM TABLE(RDSADMIN.RDS_FILE_UTIL.listdir(p_directory => 'ADUMP'))
GROUP BY ROLLUP(SUBSTR(filename,-3));