i have and AWS RDS Oracle Database wich its storage is full, looks like the cause is SYSAUX tablespace with 10.2GB size
The largest object inside SYSAUX is: WRI$_ADV_OBJECTS (TABLE) checking the table data looks like "Optimizer Statistics Advisor Task" Data is taking 9GB.
How can i delete this data and release the storage? and How can i stoped this Task to generate so much information and consuming all my free space?
(Notes: this database was created as 11G instance couple of years ago and was updated to 19 recently)
I've tried these 2 approaches (connected with the master user):
1) RUN AWS PACKAGES To stop the "Optimizer Statistics Advisor" task and also to keep the task info for only 5 days (instead of the 30 days default period)
RDSADMIN.RDSADMIN_UTIL.ADVISOR_TASK_DROP('AUTO_STATS_ADVISOR_TASK'); RDSADMIN.RDSADMIN_UTIL.ADVISOR_TASK_SET_PARAMETER('AUTO_STATS_ADVISOR_TASK', 'DAYS_TO_EXPIRE', '5' ); RDSADMIN.RDSADMIN_UTIL.ADVISOR_TASK_SET_PARAMETER('AUTO_STATS_ADVISOR_TASK', 'EXECUTION_DAYS_TO_EXPIRE', '5' );
But i only get error messages for all of them: PLS-00302: component 'ADVISOR_TASK_DROP' must be declared PLS-00302: component 'ADVISOR_TASK_SET_PARAMETER' must be declared
2) TRUNCATIN WRI$_ADV_OBJECTS TABLE with the command TRUNCATE TABLE SYS.WRI$_ADV_OBJECTS DROP STORAGE
but i get this error: ORA-04088: error during execution of trigger 'RDSADMIN.RDS_DDL_TRIGGER2'
