The invisible restore point

I regularly create and drop tablespace on my databases here at home, mainly because they are obviously not used in a “Production-like” manner.  Typically I’m tinkering with tablespaces to help answer customer questions on AskTOM about space allocation, reclaiming space and the like, so tablespaces come and go frequently. (Sidebar: If you’re regularly dropping/creating tablespaces on your Production database, and its not for the purposes of transportable tablespaces, then please drop me a line with why, because most of the time its not a great strategy)

This morning I went to drop a tablespace I had used to answer a question and this popped up.

SQL> drop tablespace demo including contents and datafiles;
drop tablespace demo including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace DEMO on primary database due to guaranteed restore points.

Seemingly no problem there – the error message is pretty clear as to what the cause is. Casting my memory back to the weekend, I recalled some other AskTOM questions about flashback and restore points, so presumably I had left one hanging around. However, things started to get interesting when I went to clean up the mess

SQL> select name from v$restore_point;

NAME
------------------------------------------
TESTING

But then…I can’t see that restore point when I drop it.

SQL> drop restore point testing;
drop restore point testing
*
ERROR at line 1:
ORA-38780: Restore point 'TESTING' does not exist.

Savvy readers will probably already know the solution to this mystery. Flashback and restore points, for lack of a better term, can “span the divide” between pluggable and container databases. If the “administrator of all adminstrators” wants to control flashback at the root container level, then all pluggables will need to abide by any such directives. In a multi-tenant database, transfer of authority is always arbitrated from the root down to the pluggables and not in the other direction (otherwise of course, as admins we would all be bumping up our cpu_count in our pluggables to grab a bigger slice of the server pie ).

In my case, I am the master of my database domain, so was just a case of flipping over to the root and dropping the restore point from there.

SQL> conn / as sysdba
Connected.
SQL> drop restore point testing;

Restore point dropped.

and then my tablespace can be dropped as expected

SQL> alter session set container = pdb1;

Session altered.

SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

TL;DR: If you can’t see a restore point but evidence suggests it is present, then it is probably in the container root.

pic credit: https://pixabay.com/users/sergeitokmakov-3426571