Posts

Showing posts from January, 2020

Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle

Image
Since in Oracle RDS environment, we don't have a direct access to the operating system I have recently been asked to provide a workaround from the database. A procedure that: 1. Write to the alertlog 2. Generate a trace file (no matter its content). 3. Let the user know how to query a. The alertlog b. The trace file Oracle RDS implementation enable us this functionality without a need to create objects or have a special grants. SET SERVEROUTPUT ON VERIFY OFF FEED OFF LINES 300 PAGES 200 DECLARE     full_file_name   VARCHAR2 (512);     file_name        VARCHAR2 (512);     MESSAGE          VARCHAR2 (1024);     ALERT_TABLE      VARCHAR2 (30);     rds_exists       NUMBER;     TABLE_MISSING    EXCEPTION;     PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);     FUNCTION FIND_VIEW (object_name VARCHAR2)         RETURN NUMBER     IS     BEGIN         EXECUTE IMMEDIATE 'select count(1) from ' || object_name;         RETURN 1;     EXCEPTION         WHEN