Home » RDBMS Server » Server Administration » ORA-38802: edition does not exist (Oracle 11.2.0.1, RHEL7)
ORA-38802: edition does not exist [message #644599] Wed, 11 November 2015 01:04 Go to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
Hello All,

I am installing one database application built by a colleague of mine, which requires the DB schema user to have grants to create editions. However, once I have given the grants to the user and then i have also created the requisite edition, when I run the application is gives the error:

ORA-38802: edition does not exist

To give grants, I have issues below commands:

GRANT CREATE ANY EDITION, DROP ANY EDITION to <username>;
alter user <username> enable editions;

Even after multiple tries I am getting same error. I tried to create the Edition using SYS user and then granted its use to the application user as well, but that did not work.

create edition "<edition_name>" as child of "ORA$BASE";
grant use on edition "<edition_name>" to <username>;

Kindly advise if I am missing anything here!.

In case of more information kindly let me know.

Thanks,
Suddhasatwa
Re: ORA-38802: edition does not exist [message #644600 is a reply to message #644599] Wed, 11 November 2015 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not tell us what you did, show us.
Use SQL*Plus and copy and paste your session, the WHOLE session.
Before, please, read How to use [code] tags and make your code easier to read.

[Updated on: Wed, 11 November 2015 01:10]

Report message to a moderator

Re: ORA-38802: edition does not exist [message #644601 is a reply to message #644600] Wed, 11 November 2015 01:19 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
Hi

Find the commands below:

create user <username> identified by <password>;
grant connect, resource to <username>;
GRANT CREATE ANY EDITION, DROP ANY EDITION to <username>;
alter user <username> enable editions;
conn <username>/<password>
create edition "<edition_name>" as child of "ORA$BASE";
grant use on edition "<edition_name>" to <username>;


Kindly let me know in case of more info.

Thanks
Suddhasatwa
Re: ORA-38802: edition does not exist [message #644602 is a reply to message #644601] Wed, 11 November 2015 01:21 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 12:50:26 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @create_user
Enter username to be created:
ercore
Enter password of the user:
ercore
old   1: CREATE USER &user IDENTIFIED BY &passwd
new   1: CREATE USER ercore IDENTIFIED BY ercore

User created.

old   1: GRANT RESOURCE, CONNECT, QUERY REWRITE TO &user
new   1: GRANT RESOURCE, CONNECT, QUERY REWRITE TO ercore

Grant succeeded.

old   1: grant create view to &user
new   1: grant create view to ercore

Grant succeeded.

old   1: grant create synonym to &user
new   1: grant create synonym to ercore

Grant succeeded.

old   1: alter user &user enable editions
new   1: alter user ercore enable editions

User altered.

old   1: grant create any edition to &user
new   1: grant create any edition to ercore

Grant succeeded.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



Hope this helps as well ...
Re: ORA-38802: edition does not exist [message #644603 is a reply to message #644602] Wed, 11 November 2015 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do it again with "set echo on" and with the error, here there is no error so no problem.

Re: ORA-38802: edition does not exist [message #644604 is a reply to message #644603] Wed, 11 November 2015 01:26 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
Sorry, but as I wrote above, the error comes when I start the application, using the above username and password that I created ...

The logs, from the JBoss server logs where the application is deployed, are as under:

11:33:16,084 INFO  [PersistenceUnitDeployment] Starting persistence unit persistence.unit:unitName=ercore.ear/#batch
11:33:21,898 WARN  [JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: ORA-38802: edition does not exist


Hope this helps.

Thanks
SB
Re: ORA-38802: edition does not exist [message #644605 is a reply to message #644604] Wed, 11 November 2015 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post result of:
 select * from dba_editions;
col property_name  format a30 
col property_value format a50 
select property_name, property_value from database_properties order by 1;

Re: ORA-38802: edition does not exist [message #644606 is a reply to message #644605] Wed, 11 November 2015 01:39 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 13:09:11 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 1000 pages 1000
SQL> select * from dba_editions;
col property_name  format a30 
col property_value format a50 
select property_name, property_value from database_properties order by 1;
EDITION_NAME		       PARENT_EDITION_NAME	      USA
------------------------------ ------------------------------ ---
ORA$BASE						      YES
15.10			       ORA$BASE 		      YES

SQL> SQL> SQL> 

PROPERTY_NAME		       PROPERTY_VALUE
------------------------------ --------------------------------------------------
DBTIMEZONE		       00:00
DEFAULT_EDITION 	       15.10
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TBS_TYPE	       SMALLFILE
DEFAULT_TEMP_TABLESPACE        TEMP
DICT.BASE		       2
DST_PRIMARY_TT_VERSION	       11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE	       NONE
EXPORT_VIEWS_VERSION	       8
Flashback Timestamp TimeZone   GMT
GLOBAL_DB_NAME		       ORCL
NLS_CALENDAR		       GREGORIAN
NLS_CHARACTERSET	       WE8MSWIN1252
NLS_COMP		       BINARY
NLS_CURRENCY		       $
NLS_DATE_FORMAT 	       DD-MON-RR
NLS_DATE_LANGUAGE	       AMERICAN
NLS_DUAL_CURRENCY	       $
NLS_ISO_CURRENCY	       AMERICA
NLS_LANGUAGE		       AMERICAN
NLS_LENGTH_SEMANTICS	       BYTE
NLS_NCHAR_CHARACTERSET	       AL16UTF16
NLS_NCHAR_CONV_EXCP	       FALSE
NLS_NUMERIC_CHARACTERS	       .,
NLS_RDBMS_VERSION	       11.2.0.1.0
NLS_SORT		       BINARY
NLS_TERRITORY		       AMERICA
NLS_TIMESTAMP_FORMAT	       DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT 	       HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	       HH.MI.SSXFF AM TZR
NO_USERID_VERIFIER_SALT        8BF56230FC49D6ECFE53EB96A5922DD5
TDE_MASTER_KEY_ID
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE

36 rows selected.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Re: ORA-38802: edition does not exist [message #644607 is a reply to message #644606] Wed, 11 November 2015 01:52 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Using the string 15.10 as your edition name may be your problem. SQL identifiers are not allowed to begin with a digit. It looks as though you did it by enclosing the string within double quotes (though you have not yet shown the creation of the edition). Any code that does not use double quotes will not work subsequently.
I would do it all again, using a legal name.
Re: ORA-38802: edition does not exist [message #644608 is a reply to message #644606] Wed, 11 November 2015 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The next actions are:
1/ Try to connect with the same user using SQL*Plus
2/ If it works, you have to check the code to know what are the connection properties passed to Oracle.

Note: I think it is a very bad usage to have an edition named "15.10". You should only use [A-Z][0-9]_$ characters and start with one of [A-Z]. So it could be "EDITION_15_10" or the like.

Re: ORA-38802: edition does not exist [message #644610 is a reply to message #644607] Wed, 11 November 2015 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Using the string 15.10 as your edition name may be your problem. SQL identifiers are not allowed to begin with a digit.


Before posting my previous message I checked it on a database with same version than OP, if the user does not provide an edition at connection time then there is no problem.
But if the user provides the edition then this clearly can lead to the error due to the elements you mentioned.

Re: ORA-38802: edition does not exist [message #644613 is a reply to message #644610] Wed, 11 November 2015 02:00 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Sorry I interfered in the thread, I saw that a bit of time had passed so I thought I could step in. At exactly the same time as you replied Confused
Re: ORA-38802: edition does not exist [message #644614 is a reply to message #644610] Wed, 11 November 2015 02:03 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
can you advise how a user connects to sqlplus by specifying the edition ?
Re: ORA-38802: edition does not exist [message #644615 is a reply to message #644613] Wed, 11 November 2015 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No problem, two explanations is better than one. Smile

Re: ORA-38802: edition does not exist [message #644616 is a reply to message #644614] Wed, 11 November 2015 02:05 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Acronis2286 wrote on Wed, 11 November 2015 08:03
can you advise how a user connects to sqlplus by specifying the edition ?

IF you run
sqlplus -h
you will see the syntax.
Re: ORA-38802: edition does not exist [message #644617 is a reply to message #644614] Wed, 11 November 2015 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Acronis2286 wrote on Wed, 11 November 2015 09:03
can you advise how a user connects to sqlplus by specifying the edition ?


I meant to connect with the default edition to check there is no deeper problem in your database.
Connecting with default edition should work.

Re: ORA-38802: edition does not exist [message #644619 is a reply to message #644617] Wed, 11 November 2015 02:08 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
i agree with you ..
Do you think i have missed priding any grants or privileges to the application user which might be causing this issue?
Re: ORA-38802: edition does not exist [message #644621 is a reply to message #644619] Wed, 11 November 2015 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you try what the SQL*Plus connection?
First we check what is working and what is not working then we will make some hypothesis.

Re: ORA-38802: edition does not exist [message #644622 is a reply to message #644621] Wed, 11 November 2015 02:14 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
Here is what I tried ...

$ sqlplus er/er@orcl EDITION="ORA$BASE"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 13:42:42 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-38802: edition does not exist


Enter user-name: 


Option 2:

$ sqlplus er/er@orcl EDITION='15.10'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 13:44:06 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-38801: improper value for ORA_EDITION


Enter user-name: 


Your point is proven from output of Option 2, but why does Option 1 fails to work ?
Re: ORA-38802: edition does not exist [message #644624 is a reply to message #644622] Wed, 11 November 2015 02:20 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
one more test ...

$ sqlplus er/er@orcl EDITION='15.10'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 13:49:42 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-38801: improper value for ORA_EDITION


Enter user-name: er/er@orcl

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set edition = "15.10";

Session altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Re: ORA-38802: edition does not exist [message #644625 is a reply to message #644622] Wed, 11 November 2015 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are some inconsistencies in the post.
See my test:
SQL> create edition 15.10;
create edition 15.10
               *
ERROR at line 1:
ORA-00931: missing identifier


SQL> create edition "15.10";

Edition created.

SQL> conn michel/michel edition=15.10;
ERROR:
ORA-38801: improper value for ORA_EDITION

Warning: You are no longer connected to ORACLE.
SQL> conn michel/michel edition="15.10";
Connected.
SQL> conn michel/michel edition=ora$base;
Connected.
SQL> conn michel/michel edition="ORA$BASE"
Connected.

As you can see I have to use "15.10" between double quotes to be able to connect and I am still able to connect to ORA$BASE (with both syntaxes).

Your test seems to indicate that ORA$BASE no more exists but your previous output shows it in USABLE state in DBA_EDITIONS.
Check again.

[Updated on: Wed, 11 November 2015 02:23]

Report message to a moderator

Re: ORA-38802: edition does not exist [message #644627 is a reply to message #644625] Wed, 11 November 2015 02:26 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
both my editions are usable ...

SQL> select * from dba_editions;

EDITION_NAME		       PARENT_EDITION_NAME	      USA
------------------------------ ------------------------------ ---
ORA$BASE						      YES
15.10			       ORA$BASE 		      YES
Re: ORA-38802: edition does not exist [message #644628 is a reply to message #644624] Wed, 11 November 2015 02:34 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Just start again, man. You have so many mistakes and inconsistencies. Another example: you are trying to connect as er, but you said this earlier:
Quote:
SQL> @create_user
Enter username to be created:
ercore
Enter password of the user:
ercore
old 1: CREATE USER &user IDENTIFIED BY &passwd
new 1: CREATE USER ercore IDENTIFIED BY ercore

Create a new database, do it all again, and this time do it right.
Re: ORA-38802: edition does not exist [message #644629 is a reply to message #644627] Wed, 11 November 2015 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try:
sqlplus /nolog
conn er/er@orcl EDITION="ORA$BASE"
conn er/er@orcl EDITION=ora$base
conn er/er@orcl 
select sys_context('userenv','current_edition_name') from dual;

Re: ORA-38802: edition does not exist [message #644630 is a reply to message #644628] Wed, 11 November 2015 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Another example: you are trying to connect as er, but you said this earlier:


Ah! didn't see that, good catch.
Now the question comes: is this the same database and connection string for all posts?

[Updated on: Wed, 11 November 2015 02:36]

Report message to a moderator

Re: ORA-38802: edition does not exist [message #644631 is a reply to message #644630] Wed, 11 November 2015 02:40 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
above was an example using ercore user to show how i created the er user as well!
i will try and create anpther database now, and try once again.

the output which michel asked for is below:

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 14:09:01 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn er/er@orcl EDITION="ORA$BASE";
Connected.
SQL> conn er/er@orcl EDITION=ora$base;
Connected.
SQL> conn er/er@orcl;
Connected.
SQL> select sys_context('userenv','current_edition_name') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Re: ORA-38802: edition does not exist [message #644632 is a reply to message #644631] Wed, 11 November 2015 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is inconsistent with the previous posts, don't you see?
So now post:
sqlplus /nolog
conn er/er@orcl 
select sys_context('userenv','current_edition_name') from dual;
conn er/er@orcl EDITION=ora$base
select sys_context('userenv','current_edition_name') from dual;
conn er/er@orcl EDITION="15.10"
select sys_context('userenv','current_edition_name') from dual;

Re: ORA-38802: edition does not exist [message #644635 is a reply to message #644632] Wed, 11 November 2015 03:30 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
what i did to resolve this issue was to drop the user, then drop the edition, then recreate the user, grant the user with all privileges, altered the user to enable edition, created the edition while logging into the application user, and restarted the application ..

i sincerely appreciate all your assistance for this issue! many thanks ... !
Re: ORA-38802: edition does not exist [message #644636 is a reply to message #644635] Wed, 11 November 2015 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To complete the topic and help future readers can you post the result of the previous commands, that is (fixing the names if thay have changed):
sqlplus /nolog
conn <a user who has the privilege to select the following views>@orcl
select * from dba_editions;
col property_name  format a30 
col property_value format a50 
select property_name, property_value from database_properties
where property_name = 'DEFAULT_EDITION';
conn er/er@orcl 
select sys_context('userenv','current_edition_name') from dual;
conn er/er@orcl EDITION=ora$base
select sys_context('userenv','current_edition_name') from dual;
conn er/er@orcl EDITION="15.10"
select sys_context('userenv','current_edition_name') from dual;

Thanks.
Re: ORA-38802: edition does not exist [message #644637 is a reply to message #644636] Wed, 11 November 2015 04:07 Go to previous messageGo to next message
Acronis2286
Messages: 16
Registered: December 2014
Junior Member
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 11 15:35:32 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn sys@orcl as sysdba
Enter password: 
Connected.
SQL> 
SQL> col property_name  format a30 
SQL> col property_value format a50
SQL> set lines 1000 pages 1000
SQL> select property_name, property_value from database_properties
where property_name = 'DEFAULT_EDITION';  2  

PROPERTY_NAME		       PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_EDITION 	       ORA$BASE

SQL> conn er/er@orcl
Connected.
SQL> select sys_context('userenv','current_edition_name') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> conn er/er@orcl EDITION=ora$base
Connected.
SQL> select sys_context('userenv','current_edition_name') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA$BASE

SQL> conn er/er@orcl EDITION="15.10"
Connected.
SQL> select sys_context('userenv','current_edition_name') from dual;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15.10

SQL> 
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Re: ORA-38802: edition does not exist [message #644638 is a reply to message #644637] Wed, 11 November 2015 04:33 Go to previous message
Michel Cadot
Messages: 65843
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks.
Note that you are not in the same state than in original post as the database default edition is ORA$BASE and not 15.10.

Previous Topic: Need help in migrating Oracle database from windows 2003 server to 2012 server
Next Topic: Privileges issue while create database through oracle user
Goto Forum:
  


Current Time: Wed Oct 17 12:09:44 CDT 2018