Home » SQL & PL/SQL » SQL & PL/SQL » How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g (Oracle 8.0.5 and Oracle 11g, VMS)
How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442049] Thu, 04 February 2010 09:49 Go to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Hi,

I need to create a Shanpshot(in Oracle 8.0.5) to get data from Oracle 11g.

Oracle 8.0.5 does not support "MATERIALIZED VIEW" so I can not use that.

My database link works fine. For example this query works.
select * from scott.emp@ora11g;


But when I try to create a Snapshot using the code
CREATE snapshot mnw_test AS
  SELECT * FROM scott.emp@ora11g;


I get this error: ORA-12028: snapshot type is not supported by master site.

Can you suggest any solution?
Thanks in advance.
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442054 is a reply to message #442049] Thu, 04 February 2010 10:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There is no way to my knowledge. Additionally to my knowledge 8.0.5 is an unsupported version of the database.

Good luck. Kevin
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442069 is a reply to message #442049] Thu, 04 February 2010 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Connection between 11g (any version) and 8.0.5 is not supported.
The fact it might work sometimes does not change the fact that is is not suppported (neither tested).

Regards
Michel
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442085 is a reply to message #442054] Thu, 04 February 2010 11:29 Go to previous messageGo to next message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
I agree it is not supported by Oracle anymore. But I also know there are still very large Corporations who are using Oracle 8 (and even 7.3 - I worked with one of them before) in their prod envs.

Anyway I got a solution from asktom here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2047000413112


It helped me create the snapshot but it only works for default option (REFRESH). When I used REFRESH FAST - it did not complain, but it will not refresh at snaphsot at all. When I try to do the refresh manually I get error message:

ERROR at line 1:
ORA-12004: REFRESH FAST cannot be used for snapshot "SCOTT"."MNW_TEST"
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 386
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 140
ORA-06512: at line 1


Anyway - I think I have to abandon this approach now. Thanks for looking into this.

Cheers.


Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442086 is a reply to message #442085] Thu, 04 February 2010 11:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
No sweat. I commend you for continuing to dig into the problem yourself. Kudos too for taking the time to post your own answer so others can benefit from it. Thanks.

Kevin
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442095 is a reply to message #442085] Thu, 04 February 2010 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Anyway - I think I have to abandon this approach now. Thanks for looking into this.

I think you have to give up this way.
You can never be sure it works.
You can never be sure it will not provide wrong data.
You will never get any help from Oracle if something goes wrong.
You will be the only one that will be blamed if something goes wrong.
Don't take the world on your shoulders.

Regards
Michel
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442103 is a reply to message #442085] Thu, 04 February 2010 12:36 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you, by any chance, have access to database(s) whose versions are between 8i and 11g? Then, you might try to create a chain; something like this:
                  Oracle 11g
              original table (A)
                      |
                      V
                  Oracle 10g
synonym to the original table over database link (B)
                      |
                      V
                  Oracle 9i
   synonym to the synonym B over database link (C)
                      |
                      V
                  Oracle 8i
   synonym to the synonym C over database link (D)
          snapshot for synonym D

I suppose that not ALL databases should be involved; perhaps you can omit 10g, but I'm not sure.
Also, I can't test it myself, so - this might all be just a dream (not to mention nonsense).

Here's how I imagine (part of) it; note that all I have is 10g Express Edition, so I have just "faked" the whole thing. However, it seems that you can create a database link, a synonym over it, a materialized view (snapshot) based on that synonym and, finally, refresh the materialized view.

I have no idea whether it will (would) work in your case. But, if you have an opportunity, try it.
SQL> connect scott/tiger
Connected.
SQL> create database link dbl_hr
  2  connect to hr
  3  identified by hr
  4  using 'xe';

Database link created.

SQL> select 'x' from dual@dbl_hr;

'
-
x

SQL> create synonym syn_job_history for job_history@dbl_hr;

Synonym created.

SQL> select * from syn_job_history where rownum < 3;

EMPLOYEE_ID START_DA END_DATE JOB_ID     DEPARTMENT_ID
----------- -------- -------- ---------- -------------
        102 13.01.93 24.07.98 IT_PROG               60
        101 21.09.89 27.10.93 AC_ACCOUNT           110

SQL> create snapshot mv_job_history as select * from syn_job_history;

Materialized view created.

SQL> select * from mv_job_history where rownum < 3;

EMPLOYEE_ID START_DA END_DATE JOB_ID     DEPARTMENT_ID
----------- -------- -------- ---------- -------------
        102 13.01.93 24.07.98 IT_PROG               60
        101 21.09.89 27.10.93 AC_ACCOUNT           110

SQL> exec dbms_mview.refresh('mv_job_history');

PL/SQL procedure successfully completed.

SQL>
Re: How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442183 is a reply to message #442049] Fri, 05 February 2010 03:57 Go to previous message
daverich
Messages: 23
Registered: January 2010
Location: UK
Junior Member
Littlefoot, actually that is the first thing I have done (creating snapshot the way you mentioned) - then I encoutnered the error "ORA-12028: snapshot type is not supported by master site." and that's why I posted the question here. Only difference is I am trying to create my snapshot in Oracle 8.0.5 to get data from Oracle 11g (well, i have no other version of Oracle that i can use).

Anyway I have decided to use simple database link to get my data instead. Thanks.
Previous Topic: display query as per requirement
Next Topic: YTD Calculation using analytical functions
Goto Forum:
  


Current Time: Sat Feb 08 20:51:28 CST 2025