all the credit belongs to Serge. LINK. cheers to always the community hero.

With what presented by Serge, PATH & CONNECT_PROC, the magic makes online application rolling available as well as brings significant thought on our schema design. This means a lot and worthies every effort to make the schema name looks a little "complicated" or even ugly.

Schema, has not been really appreciated for my practice. Just as directories for database objects. Never make it dynamic.

I'm working in a project in which all schema should be dynamic, decided by the stored procedure parameter. So almost all the code is in dynamic sql statements. Special care has been taken to the table/SP/function/trigger/index/alias/nickname, but just not as far as introducing the version number.

further thought:

0) Design schema name with version number.

1) the PATH makes the use of max(version) applicable. Say, only the database objects with the max version number can be selected.2) Carefully design the migration process. Taken special care on the dependent database objects. eg. trigger or table definition change need to involve all other related database objects to migrated to a new version which has exactly the same code.3) To avoid version conflicts between database objects, "force applications all" & "de/activate database" may be a necessary. Depends on your code.

4) special care need to be taken to tables. admin_copy_schema helps to achieve non-stop upgrade.

5) Triggers could be evil.

6) Any change to constraints/triggers may not be applicable while the application is running.

This triggers my thought of a topic something like "Super Available Prod System on cheap hardware". Combined with HA/DR than backup/restore, replication, plus online upgrade. The system has some limit, but the benefit is so high and I cannot resist to recommend.