Views dropped

user545194

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?

user545194

https://community.oracle.com/tech/developers/discussion/comment/16782538#Comment_16782538

Yes, I will definitely run this in the test environment. Thanks again!

Solomon Yakobson

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.

Kamran Agayev A.

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:

http://www.oracledocs.com/how-to-flashback-procedure-or-package/

BR,

Kamran Aghayev A.

Solomon Yakobson

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.

Kamran Agayev A.

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

user545194

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?

Solomon Yakobson

https://community.oracle.com/tech/developers/discussion/comment/16782531#Comment_16782531

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.

Kamran Agayev A.

https://community.oracle.com/tech/developers/discussion/comment/16782532#Comment_16782532

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 )

user545194

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.

Kamran Agayev A.

https://community.oracle.com/tech/developers/discussion/comment/16782537#Comment_16782537

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.

user545194

https://community.oracle.com/tech/developers/discussion/comment/16782538#Comment_16782538

Yes, I will definitely run this in the test environment. Thanks again!