CODEWITHSUNDEEP
oracle-database
Bit_hunter
Bit_hunter

Reputation: 819

What happen when we drop table & package in oracle?

I am created a new table, wrote package & gave execute access to one user for this package. Is it necessary to create rollback script for these tasks?

Upvotes: 0

Views: 585

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

  1. Yes, a trigger must always be for a table or view - so if you drop the table (or view, as the case may be), all triggers on that object will be automatically dropped as well.*

  2. Yes, privileges are always on an object - if you drop the object (e.g. a package), all privileges on that object will be lost as well.

If you subsequently CREATE a new object with the same name, the old triggers and privileges will not magically reappear - you would have to add them manually.

Alternatively, if you CREATE OR REPLACE a package, the privileges on the original package will remain. Similarly, if you ALTER a table, the triggers will not be affected.

* In Oracle 11g, by default a dropped table gets moved to the Recycle Bin. If the table had any triggers, they get moved to the Recycle Bin as well.

Upvotes: 1

Related Questions