- Refreshing a schema (often with Test accounts)
- Performing complete restores
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:
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.