Home » SQL & PL/SQL » SQL & PL/SQL » Setting Edition // Remote Database (Oracle 11.2.0.4)
Setting Edition // Remote Database [message #664825] Mon, 07 August 2017 07:09 Go to next message
suddhasatwa_oracle
Messages: 24
Registered: January 2014
Junior Member
Hello All,

I am running a data warehouse, where in both my source and target databases are running on Oracle 11.2.0.4. Source and Target databases are in different physical servers itself. The ETL of data from Source to Target is based on pre-defined logic ran via Oracle PL-SQL.

The issue is, in Source database, we have editions while in target we do not. The developers of Source database have created a new edition and overwritten the existing structure of a view using an edition based view. I would like to use the older structure of the view; however I cannot do that since the DBA of Source database has set the edition to the new edition.

Is there a way I can set edition on a remote database (in this case, the source database), without opening / starting a session from a remote database (in this case, the target database) and run a query ? I believe there are none, since Edition is a database parameter, and I do not believe there may be any hints or any other methods by which we can set edition on a remote database; however, there's nothing wrong in asking.

Thanks,
Suddhasatwa
Re: Setting Edition // Remote Database [message #664831 is a reply to message #664825] Tue, 08 August 2017 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You mean your ETL only access your target database via a DB LINK?
Ask the remote DBA to create a logon trigger for your ETL user and set the edition to its need.

Re: Setting Edition // Remote Database [message #664833 is a reply to message #664831] Tue, 08 August 2017 03:06 Go to previous messageGo to next message
suddhasatwa_oracle
Messages: 24
Registered: January 2014
Junior Member
Thanks, Michel. Are there any available hints, other than using logon triggers? Since the source system is an OLTP, our DBA's always become skeptical when they hear the term - TRIGGER. ! Smile
kindly advise.
Re: Setting Edition // Remote Database [message #664835 is a reply to message #664833] Tue, 08 August 2017 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The trigger will be on the target database not on the source one.

Re: Setting Edition // Remote Database [message #664836 is a reply to message #664835] Tue, 08 August 2017 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's an example of such trigger:
CREATE OR REPLACE TRIGGER trg_edition_logon
AFTER logon ON <your ETL remote user>.SCHEMA
BEGIN
  dbms_session.set_edition_deferred ('<your wanted edition>');
END;
/
"<your ETL remote user>" must have the USE privilege on "<your wanted edition>".

Re: Setting Edition // Remote Database [message #664837 is a reply to message #664836] Tue, 08 August 2017 04:26 Go to previous messageGo to next message
suddhasatwa_oracle
Messages: 24
Registered: January 2014
Junior Member
Thanks, Michel.
however, I am accessing the Source database over DB Link. In this case a login trigger will work? Maybe I can test this once on some test database.
Re: Setting Edition // Remote Database [message #664838 is a reply to message #664837] Tue, 08 August 2017 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

When you access a remote database through a database you open a connection/session to the former, I didn't test it but there is no reason the logon trigger didn't apply.
Test it and tell us the result.

Edit: note that the user that connects is the one declared in the database link definition not the one that accesses this db link (both may be the same one).

[Updated on: Tue, 08 August 2017 04:36]

Report message to a moderator

Re: Setting Edition // Remote Database [message #664839 is a reply to message #664838] Tue, 08 August 2017 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I tested it, it works (MIKB local database, MIKA remote one):
MIKA> create table logon_table (dt date);

Table created.

MIKA> create or replace trigger michel_logon after logon on michel.schema
  2  begin
  3    insert into logon_table values(sysdate);
  4    commit;
  5  end;
  6  /

Trigger created.
MIKB> select * from dual@mika;
D
-
X

1 row selected.
MIKA> select * from logon_table;
DT
-------------------
08/08/2017 11:38:47

1 row selected.
Re: Setting Edition // Remote Database [message #664993 is a reply to message #664839] Wed, 16 August 2017 10:41 Go to previous messageGo to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Could you not create a new service on the SOURCE database (that is specific for your needs), define the default edition on that service to whatever edition you require, then configure the DB link on the target DB to connect to that new service?
Re: Setting Edition // Remote Database [message #664994 is a reply to message #664993] Wed, 16 August 2017 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How do you set a default edition for a service?

Re: Setting Edition // Remote Database [message #664997 is a reply to message #664994] Wed, 16 August 2017 16:40 Go to previous messageGo to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Final Parameter in the create service program:

 
DBMS_SERVICE.CREATE_SERVICE(
   service_name        IN VARCHAR2, 
   network_name        IN VARCHAR2,
   goal                IN NUMBER DEFAULT NULL,
   dtp                 IN BOOLEAN DEFAULT NULL,
   aq_ha_notifications IN BOOLEAN DEFAULT NULL,
   failover_method     IN VARCHAR2 DEFAULT NULL,
   failover_type       IN VARCHAR2 DEFAULT NULL,
   failover_retries    IN NUMBER DEFAULT NULL,
   failover_delay      IN NUMBER DEFAULT NULL,
   clb_goal            IN NUMBER DEFAULT NULL,
   edition             IN VARCHAR2 DEFAULT NULL);
Re: Setting Edition // Remote Database [message #665000 is a reply to message #664997] Thu, 17 August 2017 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks, didn't notice this parameter.
This is indeed a better way than the trigger.


Re: Setting Edition // Remote Database [message #665002 is a reply to message #665000] Thu, 17 August 2017 01:39 Go to previous messageGo to next message
fixxxer
Messages: 45
Registered: August 2014
Member
Never had to use it before, so never tried it, but certainly worth trying out!
Re: Setting Edition // Remote Database [message #665024 is a reply to message #665002] Fri, 18 August 2017 02:26 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And it works:
MICHEL> select * from dba_editions;
EDITION_NAME                   PARENT_EDITION_NAME            USA
------------------------------ ------------------------------ ---
ORA$BASE                                                      YES
TOTO                           ORA$BASE                       YES

MICHEL> begin
  2    dbms_service.create_service ('TOTO','TOTO',edition=>'TOTO');
  3    dbms_service.start_service ('TOTO');
  4  end;
  5  /

PL/SQL procedure successfully completed.

MICHEL> select name from v$services order by 1;
NAME
----------------------------------------------------------------
MIKB2
MIKB2.MYSERVER
SYS$BACKGROUND
SYS$USERS
TOTO

MICHEL> conn michel/michel@//myserver:1531/mikb2.myserver
Connected.
MICHEL> select sys_context('userenv','current_edition_name') current_edition from dual;
CURRENT_EDITION
------------------------------------------------------------------------------------------------------------
ORA$BASE

MICHEL> conn michel/michel@//myserver:1531/toto.myserver
Connected.
MICHEL> select sys_context('userenv','current_edition_name') current_edition from dual;
CURRENT_EDITION
------------------------------------------------------------------------------------------------------------
TOTO
Previous Topic: Need to get the comma separated value
Next Topic: Triggers on editioning views inheriting trigger events from NEWER editions
Goto Forum:
  


Current Time: Fri Mar 29 02:59:58 CDT 2024