CODEWITHSUNDEEP
oracle-database
Cherry
Cherry

Reputation: 33608

Is it safe and possible to recover oracle dump with cycle dependencies?

Suppose there are 2 schemas A and B:

  1. B defines several views from A tables
  2. A defines some triggers which use functions from B schema.

Is it safe to create dump for A or B and recover it later? For example if I create dump A and try to restore it to Oracle instance without B I got compile errors. So can A or B dump be restored without compilation errors? (may be restore A then B and restore error part from A again). Is there a way or practise to handle such situations?

Upvotes: 2

Views: 36

Answers (1)

APC
APC

Reputation: 146309

Cyclic dependencies are generally held to be a Bad Thing. The nearest thing to Best Practice is don't have them. Have a third schema C which has things shared by one or both of the schemas. So, some variation of:

  • C -> B and C -> A , or
  • C -> B -> A , or
  • B -> C -> A

That provides a clear restore path: we can figure out what's going to break when we restore A (nothing) or C (maybe everything).

But given you are where you are, the thing to remember is code can be recompiled. We can create packages, functions and triggers with missing dependencies; they will be invalid but can be recompiled once the dependencies are in place.

Views are slightly different; they will fail if there's a missing dependency unless we use the FORCE keyword: create or replace force v_whatever as .... With FORCE the view is created in an invalid state for recompilation later.

Another complexity is that grants on invalid objects will fail. You have cyclic grants, and that is the thing which will make restoration hard, because you need to get the objects in B valid before you can grant then to A and vice versa.

One last thing: it's probably a bad idea to rely on dump (export) for backing up your code. Use source control for PL/SQL programs, views, etc. It offers much more control over deployment than importing a schema.

Upvotes: 2

Related Questions