Setting Edition // Remote Database [message #664825] |
Mon, 07 August 2017 07:09 |
|
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 #664838 is a reply to message #664837] |
Tue, 08 August 2017 04:34 |
|
Michel Cadot
Messages: 68665 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 #664997 is a reply to message #664994] |
Wed, 16 August 2017 16:40 |
|
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 #665024 is a reply to message #665002] |
Fri, 18 August 2017 02:26 |
|
Michel Cadot
Messages: 68665 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
|
|
|