3

We are using the Edition Based Redefinition feature in some software and as a DBA, I want to be able to determine which edition each given connection is using. This way, we can be sure all connections to the database are using the correct edition. How can I tell the edition being used by any given session?

1

2 Answers 2

3

Query V$SESSION and include the column SESSION_EDITION_ID which contains the value you are looking for.

The link http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223 defines all the column meanings for that dynamic view; I would probably do a query like

SELECT SID, USERNAME, SCHEMANAME, OSUSER, 
   PROCESS, MACHINE, TERMINAL, PROGRAM, 
   SESSION_EDITION_ID 
from V$SESSION  
order by SESSION_EDITION_ID;
3
  • This returns 400 rows. How do I know which one is the correct one? Aug 30, 2017 at 10:49
  • @Superdooperhero -- It depends; for me it might be adding criteria based on PROGRAM, OSUSER, or SCHEMANAME. If you have control over the creation of the session, you can use the columns MODULE and ACTION in the query, and when you create a session, invoke exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'My_Module', action_name=>'do stuff'); then query for those values. Aug 30, 2017 at 14:16
  • 1
    The SESSION_EDITION_ID is just a numeric value and does not really show what the requestor asked for. See my answer as well. Jan 7, 2022 at 14:24
2

To find the name of the edition a session is using, do following query:

select object_name as USED_EDITION, vses.*
from V$SESSION vses
JOIN ALL_OBJECTS AO on AO.object_id = vses.session_edition_id

It will show you the name as used in DBA_EDITIONS or in ALL_EDITIONS.

Your Answer

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

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