I have mentioned in previous posts that I am working on migrating a large 11.2 database on HP Unix to 19c on Linux. I ran across a database link to an older 9.2 database in the current 11.2 database. That link does not work in 19c so I thought I would blog about my attempts to get it to run in 19c. It may not be that useful to other people because it is a special case, but I want to remember it for myself if nothing else.
First, I’ll just create test table in my own schema on a 9.2 development database:
SQL> create table test as select * from v$version;
Table created.
SQL>
SQL> select * from test;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
Next, I will create a link to this 9.2 database from a 19c database. I will hide the part of the link creation that has my password and the database details, but they are not needed.
SQL> create database link link_to_92
... removed for security reasons ...
Database link created.
SQL>
SQL> select * from test@link_to_92;
select * from test@link_to_92
*
ERROR at line 1:
ORA-03134: Connections to this server version are no longer supported.
So I looked up ways to get around the ORA-03134 error. I can’t remember all the things I checked but I have a note that I looked at this one link: Resolving 3134 errors. The idea was to create a new database link from an 11.2 database to a 9.2 database. Then create a synonym on the 11.2 database for the table I want on the 9.2 system. Here is what that looks like on my test databases:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
... removed for brevity ...
SQL> create database link link_from_112
... removed for security ...
Database link created.
SQL> create synonym test for test@link_from_112;
Synonym created.
SQL>
SQL> select * from test;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
Now that I have the link and synonym on the 11.2 middleman database, I go back to the 19c database and create a link to the 11.2 database and query the synonym to see the original table:
SQL> select * from v$version;
BANNER ...
-------------------------------------------------------------------------------- ...
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ...
...
SQL> create database link link_to_112
...
Database link created.
...
SQL> select * from v$version@link_to_112;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
...
SQL> select * from test@link_to_112;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
So far so good. I am not sure how clear I have been, but the point is that I could not query the table test on the 9.2 database from a 19c database without getting an error. By jumping through an 11.2 database I can now query from it. But, alas, that is not all my problems with this remote 9.2 database table.
When I first started looking at these remote 9.2 tables in my real system, I wanted to get an execution plan of a query that used them. The link through an 11.2 database trick let me query the tables but not get a plan of the query.
SQL> truncate table plan_table;
Table truncated.
SQL>
SQL> explain plan into plan_table for
2 select * from test@link_to_112
3 /
Explained.
SQL>
SQL> set markup html preformat on
SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
SQL>
SQL> select object_name from plan_table;
OBJECT_NAME
------------------------------------------------------------------------------
TEST
Kind of funky but not the end of the world. Only a small number of queries use these remote 9.2 tables so I should be able to live without explain plan. Next, I needed to use the remote table in a PL/SQL package. For simplicity I will show using it in a proc:
SQL> CREATE OR REPLACE PROCEDURE BOBBYTEST
2 AS
3 ver_count number;
4
5 BEGIN
6 SELECT
7 count(*) into ver_count
8 FROM test@link_to_112;
9
10 END BOBBYTEST ;
11 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS;
Errors for PROCEDURE BOBBYTEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
6/3 PL/SQL: ORA-00980: synonym translation is no longer valid
I tried creating a synonym for the remote table but got the same error:
SQL> create synonym test92 for test@link_to_112;
...
SQL> CREATE OR REPLACE PROCEDURE BOBBYTEST
2 AS
3 ver_count number;
4
5 BEGIN
6 SELECT
7 count(*) into ver_count
8 FROM test92;
9
10 END BOBBYTEST ;
11 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS;
Errors for PROCEDURE BOBBYTEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
6/3 PL/SQL: ORA-00980: synonym translation is no longer valid
Finally, by chance I found that I could use a view for the remote synonym and the proc would compile:
SQL> create view test92 as select * from test@link_to_112;
View created.
...
SQL> CREATE OR REPLACE PROCEDURE BOBBYTEST
2 AS
3 ver_count number;
4
5 BEGIN
6 SELECT
7 count(*) into ver_count
8 FROM test92;
9
10 END BOBBYTEST ;
11 /
Procedure created.
SQL> SHOW ERRORS;
No errors.
SQL>
SQL> execute bobbytest;
PL/SQL procedure successfully completed.
SQL> show errors
No errors.
Now one last thing to check. Will the plan work with the view?
SQL> explain plan into plan_table for
2 select * from test92
3 /
Explained.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE
Sadly, the view was not the cure all. So, here is a summary of what to do if you have a procedure on a 19c database that needs to access a table on a 9.2 database:
- Create a link on a 11.2 database to the 9.2 database
- Create a synonym on the 11.2 database pointing to the table on the 9.2 database
- Create a link on the 19c database to the 11.2 database
- Create a view on the 19c database that queries the synonym on the 11.2 database
- Use the view in your procedure on your 19c database
- Explain plans may not work with SQL that use the view
Bobby