Skip to Main Content
  • Questions
  • Getting Error While Dropping Edition ORA-38810 implementation restriction cannot drop edition that has a parent and a child

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Girish.

Asked: June 02, 2022 - 12:02 pm UTC

Last updated: June 09, 2022 - 2:06 pm UTC

Version: 19.0.0.0.0

Viewed 10K+ times! This question is

You Asked

Hello,

We have created the multiple Editions in oracle 19c while dropping the editions using cascade we are getting below error.

ora-38810 implementation restriction cannot drop edition that has a parent and a child.


Request your help/suggestions to drop the editions.

We have performed below steps to drop and edition.

1) Created below three editions. Using below command.

CREATE EDITION E1 AS CHILD OF ORA$BASE;
    
EDITION_NAME PARENT_EDITION_NAME USABLE
ORA$BASE                  YES
E1         ORA$BASE         YES
E2         E1                 YES
E3         E2                YES


alter session set edition =E1;


Alter database default edition = E1;


Above all three steps performed for three editions and now current edition is set to E3;

select sys_context('USERENV','CURRENT_EDITION_NAME') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
E3


select * from database_properties where property_name = 'DEFAULT_EDITION';

Output:
  
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
DEFAULT_EDITION E3         Name of the database default edition


Now, We want to retire Edition E1 we performed below steps.

1) Checked the privilege's using below query.

SELECT *--GRANTEE, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'E1';

GRANTEE   OWNER  TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE    INHERITED
IT7_HOST  SYS    E1         SYS     USE       YES       NO        NO     EDITION  NO


2) Revoked privilege's of schema using below command.

REVOKE USE ON EDITION E1 FROM IT7_HOST;

DROP EDITION E1 CASCADE;


Getting below error while dropping;

ora-38810 implementation restriction cannot drop edition that has a parent and a child


4) dba_editions view shows below output.
EDITION_NAME PARENT_EDITION_NAME USABLE
ORA$BASE                  YES
E1         ORA$BASE         YES
E2         E1                 YES
E3         E2                 YES


Regards
GirishR

and Chris said...

The edition E1 has a parent (ORA$BASE) and a child (E2).

As the error says, you can't do this!

The only editions you can drop are the root and the leaf. Which are ORA$BASE and E3 in this case.

So if you want to remove E1, either you need to first

Drop ORA$BASE

OR

Drop E3, then drop E2.

Or you could leave E1 in place. As you've revoked user IT7_HOST access to this edition, it can no longer use it.

Rating

  (4 ratings)

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

Comments

Not Able To Drop Edition

Girish Rane, June 07, 2022 - 7:13 am UTC

Thanks for your response.

Tried doing the same as suggested. Below are the steps and output.

REVOKE USE ON EDITION E3 FROM IT7_HOST;

Drop edition E3 CASCADE;
ERROR at line 1
ORA-38805: edition is in use

ALTER DATABASE DEFAULT EDITION =E1;

Drop edition E3 CASCADE;
ERROR at line 1
ORA-38805: edition is in use


select s.sid,s.serial#,s.username,s.SESSION_EDITION_ID,o.OBJECT_NAME,OBJECT_TYPE from v$session s, dba_objects o
where s.SESSION_EDITION_ID=o.OBJECT_ID
and username= 'IT7_HOST';

Killed session where edition E3 were in use.

Drop edition E3 CASCADE;
ERROR at line 1
ORA-38805: edition is in use


Getting edition is in use error after setting default edition to E1.

Request your help.

Regards
GirishR
Chris Saxon
June 07, 2022 - 9:35 am UTC

Are there any sessions from other users connected to E3?

Not Able To Drop Edition

Girish Rane, June 07, 2022 - 10:33 am UTC

There are sessions which were holding the Edition E3 and i have already kill those sessions but still the session are showing in killed status.

After DB bounce those sessions got removed and able to drop the edition.

DB bounce is required to drop the edition?

Regards
GirishR
Chris Saxon
June 07, 2022 - 3:23 pm UTC

No, you just need to wait for the sessions to fully disappear

Perhaps rollback.

Rajeshwaran Jeyabal, June 09, 2022 - 11:42 am UTC


There are sessions which were holding the Edition E3 and i have already kill those sessions but still the session are showing in killed status.


Perhaps those sessions many be doing the rollback for those dml's. Hence they might have been still shown in v$session with status as "killed"
Chris Saxon
June 09, 2022 - 2:06 pm UTC

Perhaps, yes.

Prafull, July 26, 2023 - 7:52 am UTC

We required to set “v$parameter.compatible” parameter as 19.0.0

This resolved the issue - now we able to drop edition after meeting required conditions for dropping the editions.


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.