Oracle Statspack on RDS

-- Execute as Master User

-- Unlock User
ALTER USER PERFSTAT IDENTIFIED BY pwd ACCOUNT UNLOCK;

-- Grant Privileges and fix known Issue
GRANT CREATE JOB TO PERFSTAT;
INSERT INTO PERFSTAT.STATS$IDLE_EVENT (EVENT)
SELECT NAME FROM V$EVENT_NAME WHERE WAIT_CLASS='Idle'
MINUS
SELECT EVENT FROM PERFSTAT.STATS$IDLE_EVENT;
COMMIT;

-- Connect PERFSTAT and Schedule Snapshots
VARIABLE jn NUMBER;
exec dbms_job.submit(:jn, 'statspack.snap;',SYSDATE,'TRUNC(SYSDATE+1/24,''HH24'')');
COMMIT; 

-- Checking Snapshot IDs
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT SNAP_ID, SNAP_TIME FROM STATS$SNAPSHOT ORDER BY 1;

-- Create snapshot report
exec rdsadmin.rds_run_spreport(begin_snap,end_snap);
exec rdsadmin.rds_run_spreport(1,2);


-- List Files in Directory
set linesize 200
col FILENAME for a40
SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like '%.lst%';

-- Execute Snapshot individually
execute statspack.snap;

-- Purge snapshots
exec statspack.purge(begin snap, end snap); 
exec statspack.purge(1, 2); 

-- File deletion retention policy
set serveroutput on
EXEC rdsadmin.rdsadmin_util.show_configuration;

NAME:archivelog retention hours
VALUE:0
DESCRIPTION:ArchiveLog expiration specifies the duration in hours before archive/redo log files are automatically deleted.
NAME:tracefile retention
VALUE:10080
DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted.

-- Default is 10080 = 7 Days 

-- Checke file name
set linesize 200
col FILENAME for a40
SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like '%.lst%';

-- Delete File
EXEC UTL_FILE.FREMOVE('BDUMP','ORCL_spreport_1_2.lst');

-- Confirm Deletion
SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'BDUMP')) where FILENAME like '%.lst%';

no rows selected