How to decrease Oracle undo tablespace in AWS RDS?

1
votes

Undo tablespace in my Oracle DB is taking 180GB. I don't have any process that would need this much space. There was an unknown issue with the DB, which cussed the undo table size to increase back in a while; and after bouncing RDS instance it has been rectified. The issue now is how decrease the size of the undo tablespace. I have managed to create a new undo table using 

creat undo tablespace UNDO_T2;

but have failed to tell oracle to use the new undo table space. Here is my query and its error:

alter system set undo_tablespace = UNDO_T2;

Error report:
SQL Error: ORA-02065: illegal option for ALTER SYSTEM
02065. 00000 - "illegal option for ALTER SYSTEM"
*Cause: The option specified for ALTER SYSTEM is not supported
*Action: refer to the user manual for option supported
3
databaseoracleamazon-web-servicesamazon-rdstablespace
pretty sure a hardore way would be to set up a new instance the way you want - then EXP from the old and IMP to the new. - Randy
Added a datafile to the undo_t2 tablespace? - REW

3 Answers

2
votes

You have a typo in the command, with an extra e. Unless you have a weird tablespace name you don't need the quotes either.

alter system set undo_tablespacee = 'UNDO_T2';

... should be:

alter system set undo_tablespace = UNDO_T2;
1
votes

I successfully resize the UNDO table down to 16GB using the following command. This requires Master User permission for the RDS instance.

ALTER TABLESPACE UNDO_T1 RESIZE 16G;

One should check what is a suitable size for there Oracle instance.

0
votes

Have you added a datafile to this tablespace?