Skip to Main Content

Oracle Database Discussions

Announcement

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!

Views dropped

user545194Jan 25 2021

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?

This post has been answered by user545194 on Jan 25 2021
Jump to Answer

Comments

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:
How to flashback procedure or package - OracleDocs.com (0 Bytes)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

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.

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.

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
Answer

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

Marked as Answer by user545194 · Jan 25 2021
1 - 10

Post Details

Added on Jan 25 2021
10 comments
130 views