Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 29, 2021 - 6:19 am UTC

Last updated: September 30, 2021 - 10:00 am UTC

Version: Oracle 19c

Viewed 10K+ times! This question is

You Asked

Some time ago we migrated from oracle 11 to 19c. Out of 5 of our schemas, 2 of them, when adding new objects to the schema (packages, synonyms, views, etc.), create them as non-editonable in default, which makes it impossible to update the objects of these two applications.
Editions in this database instance are disabled for all of our 5 schemas. Only two schemas have this problem, the other 3 create new objects as editionable as standard (even SYS and SYSTEM don't have this problem). The base is in the default "ora $ base" edition. In our applications, editions are to be turned off. What we missed for these two schemas, and how can it be fixed without re-importing (production database, and it's been a long time since the migration). I assume this is not an editions problem, there must be something else that caused these two schemas to have such problem.
?

and Chris said...

I'm unclear exactly what the issue is here - you can change noneditionable objects. Just not the (non)editionable property with create or replace commands:

create or replace noneditionable procedure p as
begin
  dbms_output.put_line ( 'First version' );
end p;
/

exec p ();

First version

create or replace noneditionable procedure p as
begin
  dbms_output.put_line ( 'Second version' );
end p;
/

exec p ();

Second version

create or replace editionable procedure p as
begin
  dbms_output.put_line ( 'Editionable version' );
end p;
/

ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.

exec p ();

Second version

alter procedure p 
  editionable;

create or replace editionable procedure p as
begin
  dbms_output.put_line ( 'Editionable version' );
end p;
/

exec p ();

Editionable version


So do your scripts include the (non)edtitionable clause in them?

If so, omit this and the objects keep their current setting and you can change them fine.

Note that the user must have editions disabled to change the editionable property:

select editions_enabled 
from   dba_users
where  username = user;

EDITIONS_ENABLED   
N            


If this is Y, you can't change it. You'll have to recreate the objects to make them editionable.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Piotr, September 29, 2021 - 9:06 am UTC

SQL> select row_number() over(order by username desc) as SCHEMA, editions_enabled
  2  from   dba_users
  3  where  username in ('PPS','MEDIS');

    SCHEMA E
---------- -
         1 N
         2 N


2: (on this two schema):
create table test_editionable(
test number
)

create synonym syn_editionable for test_editionable

-------------------------------------------------------------------------

AND


first schema: SQL> select editionable from all_objects where owner = 'PPS' and object_name = 'SYN_EDITIONABLE';

E
-
N (as default)

second schema: SQL> select editionable from all_objects where owner = 'MEDIS' and object_name = 'SYN_EDITIONABLE';

E
-
Y (as default)



Why? WTF?

Why on first is noneditionable as default, and second is editionable as default?


Chris Saxon
September 29, 2021 - 1:55 pm UTC

I don't know why this is happening - contact support to look into this further.

What practical problem is this causing you?

Piotr, September 29, 2021 - 2:16 pm UTC

Newly created objects cannot be updated in the future because
ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object
Chris Saxon
September 29, 2021 - 3:35 pm UTC

So what happens when you try the commands I showed above?

create or replace noneditionable ...

alter ...
  editionable;

Piotr, September 30, 2021 - 6:12 am UTC

I found out what the problem was yesterday. Probably something went wrong during migration and created all objects incompatible with the default schema configuration. Instead of being created as editionable, it has been created as noneditionable. When trying to repair, I changed all objects in all schemes to editionable (
alter ... editionable
). The error was that I should only change these 3 schemas (default editionable) and not the other 2 (default noneditionable). And first, if we had a problem (ORA-38824) in 3 schemas when updating, then after the repair, there was only a problem in these 2 schemas.

Thanks for handling the report, otherwise I wouldn't have figured out what I missed.

BTW: Do you have any idea what could have gone wrong during the migration that happened - what did we do wrong?
Chris Saxon
September 30, 2021 - 10:00 am UTC

Not sure what happened - were the objects marked as noneditionable in the source before you exported them?

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database