Learning is not a spectator sport

Recently when trying to drop a user, I got an error that seemed unrelated to the act of dropping the user.

SQL> drop user demo cascade;

drop user demo cascade

*

ERROR at line 1:

ORA-29972: user does not have privilege to change/ create registration

It turns out, that this user account was being used via an ODP.NET application, which was using the new 11g feature – Change Notification. This is a system privilege, and (for an unknown reason) you need to revoke this privilege before you can drop the user.

SQL> revoke CHANGE NOTIFICATION from demo;

Revoke succeeded.

SQL> drop user demo cascade;

User dropped.