Working with Application System Accounts (CREATE LIKE)

If you have an Application on a Oracle database that uses a standard account to access the database (and usually owns all the objects), sometimes you will need to drop that user (cascade constraints) for reasons such as:

 

• Refreshing a schema (often with Test accounts)
• Performing complete restores

 

Since the roles and object privileges are often quite involved on systems like this, it is a good idea to make copies of your user. I used to use this all the time on OEM but now this function exists in SQL*Developer also.

 

Please see this great article from Jeff Smith:

http://www.thatjeffsmith.com/archive/2015/12/trick-getting-all-the-privileges-grants-for-a-user/

 

I would usually name my user "XXXX_shell"  (like: FIN_SHELL) and after their objects were dropped, would perform this "CREATE LIKE" again back to the normal user name (like: FIN_PROD). Now you are ready to IMP your objects.