For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Env: Oracle 11g SE on Windows Server. We dropped a number of views in a specific schema: drop view schema_name.view_name; So far, so good. We noticed that the views were dropped in another schema as well. How could this have happened?
It couldn't unless dropped in both schemas directly or indirectly (e.g. there is after DDL trigger). Also, second schema could have not the view but rather a synonym to first schema view, although in such case you'd get "synonym translation no longer valid" rather than "table or view doesn't exist". You could check DBA_AUDIT_TRAIL to find out who and when dropped what, assuming DDL audit it enabled. SY.
Dear OP If audit wasn't enabled in the database, it's hard to answer the question "who dropped the view". If the data is still in UNDO, you can use flashbcak query to get the dropped views back. Check the following blog post for more information on this: How to flashback procedure or package - OracleDocs.com (0 Bytes)BR, Kamran Aghayev A.
There is no need to dig into flashback unless OP has no saved view definition script (which would be strange and would indicate much deeper organizational issues). SY.
OP mentioned that views in other schemas were dropped which means that most probably they didn't saved the script of the views which dropped by accident
enable_ddl_logging is not enabled. We are on SE, therefore do not have flashback query. I can check the alert log to see what happened. Aren't dropped objects moved to the recyclebin?
If so, then, as I already mentioned, it would indicate much deeper organizational issues. Any changes must be scripted and saved to CMS before deploying. SY.
Views are just scripts which are not moved to the recycle bin. Do you have a test environment for this particular database to imitate the same action there as well and see if views are dropped (which shouldn't )
Yes, we have a test environment and the views are there, but they are not in sync with the production env. But I can perform the same action and see what the outcome is. For production however, we'll have to restore from a recent backup. Good news: Suddenly the views "re-appeared" and are visible in the SQL Developer tree view. Really strange. Thanks to all for your feedback.
Sure you have to restore it from backup anyways. What I'm wondering the abnormal behavior that happened in your system. I would suggest you to enable the audit on the test environment, drop the mentioned views and check if other views are also dropped BR, Kamran Aghayev A.
Yes, I will definitely run this in the test environment. Thanks again!