This is the second post about working with the edition based redefinition feature of the oracle database. The first post was about useful commands that help to set up editions inside an oracle database and enable schemas to use editioned objects.
This post will concentrate on different methods for connecting and setting a session to a specific edition.
It assumes that we already have three editions in our database :
ORA$BASE->DEV$BETA->DEV$ALPHA
connect using SQL*PLUS directly
- connect to the default edition
Without any additional settings, the connect goes to the specified default edition of the database. E.g. ORA$BASESQL> connect user/pwd@database Connect done.
- Use environment Variable ORA_EDITION
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm
windows:set ORA_EDITION=DEV$BETA
linux:
export ORA_EDITION=DEV\$BETA
- CONNECT with specific edition
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve015.htm#i2697450SQL> connect user/pwd@database edition=DEV$ALPHA Connect done.
so setting the edition works with SQL*plus.
Unfortunatly only a minority of tools support editions during the connect.
connect using TNSNAMES + DB Services
This will work with all tools including connections from applications servers, e.g. Apex/ORDS.
We can create different tnsnames entries that connect to a different db service name.
The db service can be configured to connect to a defined edition.
extra DB Service for different editions
- using servercontrol (important when Oracle Restart is used)
srvctl add service -d db_unique_name -s service_name -t edition_name
- using plsql package to install DB services (starting from 11.2.0.2)
begin DBMS_SERVICE.CREATE_SERVICE( service_name => 'DEV_BETA', network_name => 'DEV_BETA', edition => 'DEV$BETA'); end; / begin DBMS_SERVICE.CREATE_SERVICE( service_name => 'DEV_ALPHA', network_name => 'DEV_ALPHA', edition => 'DEV$ALPHA'); end; / begin DBMS_SERVICE.CREATE_SERVICE( service_name => 'DEV_BASE', network_name => 'DEV_BASE', edition => 'ORA$BASE'); end; / commit; select * from dba_services;
The name of the DB service is then used inside TNSNAMES.ORA for the service_name parameter (do not use SID!).
connect using JDBC
It is possible to set the edition for connections made via JDBC:OCI. To use that the OracleConnection class extention needs to be used.
Properties prop = new Properties(); prop.setProperty(OracleConnection.CONNECTION_PROPERTY_EDITION_NAME, "DEV$BETA");
check the configuration (services and other relevant parameters)
select * from v$parameter where regexp_like(name, 'edition|service|global|processes|sessions|dispatch|shared|listen') ;
[…] Information how to create a service for an edition can by found on one of my older blog entries: working with editions – part 2. Also Oren Nakdimon recently publiced a very nice article about it: using services for exposing new […]
[…] On the Oracle Autonomous Database there are some restrictions on what is possible an what not. In generell Edition Based Redefinition (EBR) can be used on the Oracle Cloud Infrastructure. EBR is now part of Oracles Maximum Availability Architecture (MAA). It is possible to enable schemas to use editions, to create editionable objects, editioning views and cross edition triggers. One thing that doesn’t work on Autonomous Transaction Database (ATP) – and I assume on the other Autonomous DBs as well – is to create a custom service using DBMS_SERVICE. This is not a feature directly needed for EBR. But such a service can be connected to a specific edition. When doing so this service acts as a separate layer for all kind of connects from the application or even from development tools like SQL Developer. This is super helpful. I wrote about this a couple of years ago: https://svenweller.wordpress.com/2015/06/16/working-with-editions-part-2/ […]