- 3,723,860 Users
- 2,244,635 Discussions
- 7,850,737 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.1K Databases
- 615 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 497 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 422 SQLcl
- 62 SQL Developer Data Modeler
- 185.1K SQL & PL/SQL
- 21.1K SQL Developer
- 2.4K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.6K Development Tools
- 14 DevOps
- 3K QA/Testing
- 337 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 72 Java Community Process
- 2 Java 25
- 12 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 16 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 10 Software
- 4 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
Views dropped
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?
Best Answer
-
Yes, I will definitely run this in the test environment. Thanks again!
Answers
-
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:
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!
