How to create a Shanpshot (in Oracle 8.0.5) to get data from Oracle 11g [message #442049] |
Thu, 04 February 2010 09:49  |
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 #442085 is a reply to message #442054] |
Thu, 04 February 2010 11:29   |
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 #442103 is a reply to message #442085] |
Thu, 04 February 2010 12:36   |
 |
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  |
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.
|
|
|