Separating DB schema - reappoint PLSQL code

user1987306

Hello everyone!

We have an Oracle DB 11gR2 which is used to store the EBS data.

Inside that DB, we also have a schema called EX to store our custom tables, views, etc...

Many of our developments (PL/SQLs, APEX) use the EX schema objects.

Our boss has requested to separate the EX schema, I mean, export the EX schema and import it into a new Oracle DB.

We don't want to modify our PL/SQL code because that implies execute long testings, many hours of effort... what is the best way to "repoint" our code without make changes? is it possible not use DB links?

We have thought about create a public DB link to the new database, then create a synonym to the schema (the whole schema not to a specific schema object), but it doesn't work properly..

Is it possible to block or "hide" (disable) the EX schema in the current DB and then use the new schema (remote database)?

We appreciate any help.

Thanks in advance.

Brian Bontrager

It sounds like all the data will remain where it is, but ONLY the EX schema will move to a remote instance, but need data in the original instance?  Am I understanding your situation correctly?

what is the best way to "repoint" our code without make changes?

"Repoint"ing your code is, by definition, making changes. You won't avoid the testing.

is it possible not use DB links?

If you move EX to a different instance and need data from the original you will, by necessity, use database links.

then create a synonym to the schema (the whole schema not to a specific schema object)

What syntax have you tried to do that? Synonyms point to specific objects.

We don't want to modify our PL/SQL code because that implies execute long testings, many hours of effort...

Whether you modify PL/SQL directly to reference tables over a link or you leave code the same and create synonyms to objects over database link, you still need full regression testing to make sure everything works as expected, and to learn what performance degradation you can expect in such an arrangement.

The business justification to move the schema should include the cost of testing in the cost-benefit analysis used to determine whether to make this significant system change.

Is it possible to block or "hide" (disable) the EX schema in the current DB and then use the new schema (remote database)?

Revoke all privileges on the objects in EX.

Unknown

We have an Oracle DB 11gR2 which is used to store the EBS data.

Inside that DB, we also have a schema called EX to store our custom tables, views, etc...

Many of our developments (PL/SQLs, APEX) use the EX schema objects.

Ok - so far so good.

Our boss has requested to separate the EX schema, I mean, export the EX schema and import it into a new Oracle DB.

And here is where it all goes bad.

1. WHY? We can't help you with solutions without knowing and understanding the actual PROBLEM. Tell us what PROBLEM your 'boss' is trying to solve. Also tell us WHY your 'boss' chose whatever solution it is that you are talking about.

2. You can't 'separate' a schema. A schema is a single, indivisible object. So tell us what this means to you. Nothing in your statement implies 'separate'; it suggests that you still intend to use that same schema with the same objects but, for some bewildering and unknown reason, your 'boss' wants the schema on a different database.

We don't want to modify our PL/SQL code because that implies execute long testings, many hours of effort

If someone has suggested to you that your proposed changes will NOT require complete retesting then they are delusional. Ask them to seek help ASAP - there are some good medications that can help them adjust to, and cope with, reality. That reality is that EVERYTHING in that application will need to be thoroughly tested.

Code and queries that execute across database links with typically work differently and sometimes will not work at all. It all depends on what the code/queries do and whether they use object types. Execution plans will change.

... what is the best way to "repoint" our code without make changes? is it possible not use DB links?

The 'best' way, IMHO, is to NOT do it at all. If you even have to ask about NOT using DB links to communicate between two different databases then you have a serious problem on your hands. That question means that whoever came up with this plan doesn't understand the issues involved and/or how your code works.

Totally unmentioned (and unconsidered) are the substantial changes to the backup/recovery/archive processes and procedures that are being used. Those will be radically different in a two-database application that for a single database.

I suggest that your 'boss' put this plan on hold and, instead, request the creation of a functional requirements document. That document should have sections for:

1. Current architecture, SLAs, data volumes, performance metrics,

2. Problems/Issues/limitations with the current architecture

3. Specific goals for mitigating/eliminating the problems/issues/limitations in #2 above

4. Timeline for development, testing and rollout

Only AFTER that document has been completed and SIGNED OFF by the business users and your 'boss' can you even begin to consider the potential solutions that might be applicable.