0

We have some pretty high-load service in oltp database built using Oracle's procedures, functions, views and etc.
Whenever we want to update our code base, it's usually throws an error like

  1. ORA-06550 (invalidations because of changing depending objects)
  2. ORA-04068
  3. ORA-04065 (not executed, altered or dropped stored procedure)

because parent procedure that triggers view execution that triggers functions execution is called by outside API at this right moment when we are updating functions, views, procedures.
We usually update it via CREATE OR REPLACE and it takes about 0.3 - 0.4 seconds. Then we recompile invalid objects if there any.

Problem

Because we can't have any downtime during daytime, we usually proceed with this operations at night, which is very inconvenient.

Oracle promises eliminating downtime using their EBR, but aren't for example functions EDITIONABLE by default? And is recompiling them using simple DDL any different from using EBR?

Will changing editions using EBR throw ORA-04065 while objects being called? Or any better solutions for Oracle 19c version?

There isn't much about EBR besides some marketing crap from Oracle

1 Answer 1

0

Functions are editionable by default for edition enable users but you need to create a new edition to benefit otherwise you’re just modifying the package in the edition that your users are already using. Your user that owns the objects also needs to be edition enabled https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-USER.html#GUID-9FCD038D-8193-4241-85CD-2F4723B27D44)

Your upgrade process will look like

  • Create new edition
  • Switch upgrade session to use new edition
  • Do all the pl/sql and view changes
  • Set the new default edition to be your new edition
  • New sessions will start using your code changes. You will need to restart front end processes in order to get them to pick up the new default (assuming you’re pooling connections)
  • When no more sessions are using the old edition, drop it. Next upgrade will start by creating a new edition from your newly created one

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.