Thursday, March 10, 2016

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:
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.

No comments:

Post a Comment