This is very easily possible, with no change to any code (neither the PL/SQL code nor your application code, if there is some other application like Java or whatever):
- Put all PL/SQL in one (or more) separate Oracle schema. If your application today uses only one "myapp" schema for tables and PL/SQL, then in the future you will have "myapp" (tables, indexes etc.) and "mycode" (or whatever) which only contains PL/SQL procedures.
- In "myapp", create synonyms for all the objects that now live in "mycode".
- In "mycode", you can create stub tables - i.e., the same tables as in "myapp", but without any data. You do not need any indexes or whatnot, just the tables. These are only used at compile time; they are especially not used by the optimizer, later.
That's it for separating your code and data. The next step obviously is to create "mycode2" as a clone of "mycode", and from then on you simply need to switch the synonyms in "myapp" between those two. You can have as many code schemas as you want, and switch between them freely with some rather trivial tooling.
(Setting the proper options, grants, making sure that the PL/SQL uses the tables of the caller instead of the compile unit left as an exercise to the reader... - feel free to ask if anything is really unclear.)
Practical hints: It is true that you have to change 5000 synonyms (well, hopefully you do not have 5000 PL/SQL packages :) ), but that is an uncritical operation insofar as there is nothing that can go wrong (both
drop synonym and
create synonym can not go wrong for any reason I am aware of; unlike e.g.
drop table which can lock if the table is in use). You will obviously script those drops; and you will also probably query the synonyms you have to set by
select object_name from all_objects where user_name='MYCODE2' and object_type in ('PACKAGE', 'PROCEDURE') or something like that to automatically catch all of them.