Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, victor.

Asked: November 25, 2001 - 2:30 pm UTC

Last updated: July 30, 2006 - 8:47 am UTC

Version: 7/8/8i

Viewed 1000+ times

You Asked

Hi, Tom
I try to create a snapshot on copy site (oracle 8i) for master table T
having primary key (oracle 7.3). I created snapshot log in database for master table T . But when I try to create snapshot in copy site I retrieve a message : snapshot type is not supported by master site.
Though when I create snapshot using union in statement select (ex. add another some table with the same structure) it is created.
In error message guide i read : create ROWID snapshot or ... .
How to do it and why I get this error?
Thank in advance.
Victor.

and Tom said...

The error message cause/action seems clear in this case:

> oerr ora 12028
12028, 0000, "snapshot type is not supported by master site %s"
// *Cause: Pre-Oracle8 master sites are not able to support primary key or
// subquery snapshots that are able to perform a fast refresh.
// *Action: Create a rowid snapshot or use a master table from an Oracle8 site.


You have a master (7.3). Its snapshot log has a ROWID in it. 7.3 snapshot logs did not understand how to store a primary key -- only the rowid.

When you did it with a union, it ignored your "refresh fast" request, union snapshots are always done complete -- hence it can create (no reliance on a snapshot log in that case).

Create a rowid based snapshot.



ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot
2 t_snap
3 refresh fast
4 as
5 select * from t@ora734.world;
select * from t@ora734.world
*
ERROR at line 5:
ORA-12028: snapshot type is not supported by master site


ops$tkyte@ORA817DEV.US.ORACLE.COM> create snapshot
2 t_snap
3 refresh fast WITH ROWID

4 as
5 select * from t@ora734.world;

Materialized view created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Liked it

Jim, November 25, 2001 - 6:56 pm UTC

Always learning something on this site

unexpected consequence

victor, November 26, 2001 - 1:54 am UTC

Thank you very much for the clear explanation.
But may be I ought to be more precise. As I have Oracle 7.3.1 so after doing all of your advice on Oracle 8i I've received a message:
SQL> create snapshot t_arr
  2   refresh fast with rowid
  3   as select * from training.arrival@WG73.GSRAS.RU ;
 as select * from training.arrival@WG73.GSRAS.RU
           *
ERROR at line 3:
ORA-12018: following error encountered during code generation for
"DEVELOPER"."T_ARR"
ORA-00942: table or view does not exist
ORA-02063: preceding line from WG73.GSRAS.RU
But now master allows to create me a complete snapshot for
the master table.
SQL>  create snapshot t_arr
  2    refresh complete with rowid
  3  next sysdate +1 
  4   as select * from training.arrival@WG73.GSRAS.RU ;

Materialized view created.
All need snapshot logs exists on master site for master table as I mentioned. Whether may I get fast snapshot nevetheless ? I'l very appreciate for answer. 

Tom Kyte
November 26, 2001 - 9:33 am UTC

Do you have select privs granted directly on the snapshot log to the userid associated with the database link? (i think not -- that explains why the FULL works -- doesn't need the snapshot logs where as the fast refresh does.

Try:

grant select on mlog$_arrival to <USERNAME>;

in the 7.3 instance where username is the user the database link is connecting as.

Still getting this error

Hitesh, January 28, 2003 - 1:07 am UTC

Following stmt geneater error :
ORA-12028: snapshot type is not supported by master site

CREATE materialized VIEW cust
PCTFREE 0
STORAGE(INITIAL 32k NEXT 32k PCTINCREASE 2)
build IMMEDIATE
refresh on commit
ENABLE query rewrite
AS
SELECT
cc.appl_no AS appno,
TO_CHAR(cc.appl_date,'dd-mon-yy') AS dt,
TO_CHAR(cc.appl_date,'hh:mi:ss') AS tm,
cc.associate_code AS ass_code,
cc.gross_income AS gr_income ,
cc.user_id AS userid,
cc.business_name AS emp_name,
a.first_name AS fname,
a.designation AS desg,
a.last_name AS lname,
a.res_tel1 AS res_ph,
a.off_tel1 AS off_ph ,
a.off_ext AS ext,
a.mobile AS mob ,
a.email_id AS email ,
a.res_address AS res_bld,
a.off_address AS off_bld,
FROM
CREDIT_CARD cc,
CUST_DTL a
WHERE
cc.user_id = a.user_id AND
a.res_city_code IN (67,102,105)

I have installed Oracle 8.1.5. Can u pls describe in detail why this error comes and how to solve this ?


Tom Kyte
January 28, 2003 - 7:19 am UTC

due to insuffient information -- nope.

no idea what any of the base objects look like, what commands you did to get here, nothing.

nope, no idea.

(i'll comment on your storage clause -- it stinks. use LMTS, never put a storage clause on an object ever again)

A reader, January 28, 2003 - 11:43 am UTC

Tom,

In your new book do you have topics on Replication?

Thanks.

Tom Kyte
January 28, 2003 - 2:57 pm UTC

Nope, not a big fan of the "technology", especially with the web. makes it virtually obsolete except in the rarest of circumstances.


Full detail

Hitesh, January 29, 2003 - 2:13 am UTC

Following r list of my tables.


SELECT
cc.appl_no AS appno,
TO_CHAR(cc.appl_date,'dd-mon-yy') AS dt,
TO_CHAR(cc.appl_date,'hh:mi:ss') AS tm,
cc.associate_code AS ass_code,
cc.gross_income AS gr_income ,
cc.user_id AS userid,
a.first_name AS fname,
a.designation AS desg,
a.last_name AS lname,
a.res_tel1 AS res_ph,
a.off_tel1 AS off_ph ,
a.off_ext AS ext,
a.mobile AS mob ,
a.email_id AS email ,
a.res_address AS res_bld,
a.off_address AS off_bld,
FROM
CREDIT_CARD cc,
CUST_DTL a
WHERE
cc.user_id = a.user_id AND
a.res_city_code IN (67,102,105)

Following stmt geneater error :
ORA-12028: snapshot type is not supported by master site


desc CREDIT_CARD

Name Null? Type
----------------------------------------- -------- ----------------------
USER_ID NOT NULL VARCHAR2(20) --> foreign key maps to cust_dtl
APPL_NO NOT NULL NUMBER(7) --> Primary key
APPLIED_DATE DATE
ASSOCIATE_CODE VARCHAR2(25)
GROSS_INCOME NUMBER(13)



desc CUST_DTL
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_ID NOT NULL VARCHAR2(20) --> Primary key
PASSWORD NOT NULL VARCHAR2(20)
REGISTRATION_DATE NOT NULL DATE
FIRST_NAME NOT NULL VARCHAR2(30)
MIDDLE_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
DATE_OF_BIRTH NOT NULL DATE
SEX VARCHAR2(6)
RES_TEL1 VARCHAR2(40)
OFF_TEL1 VARCHAR2(40)
EMAIL_ID VARCHAR2(40)
MOBILE VARCHAR2(40)
PAGER VARCHAR2(40)
OFF_EXT VARCHAR2(40)
RES_CITY_CODE NUMBER(7)


I wants to create a snapshot which would be update on every commit. But when I execute this command its giving me the error. Its important coz in these tables there r 20 lacs records and this query takes more time. So, I thought that materialized view is better for this.

Pls help me

Tom Kyte
January 29, 2003 - 7:52 am UTC

the mv is totally the wrong approach here.

if you have 2,000,000 records -- it is going to take 10 seconds or so. Is that too much time?

how much time is it taking?

you are not using the RBO are you which would use indexes and take forever probably.

what is your tkprof for this query....

you don't want to DUPLICATE this data. databases were born to join, it is one of the things they do best.

create snapshot for large tables

Mukul, July 30, 2006 - 6:54 am UTC

I am having a large table of 4gb (approx) in size in my database. when i execute the query for creating snapshot inorder to avoid the ora-12028 error , its taking too much time to complete .
What to do ?



Tom Kyte
July 30, 2006 - 8:47 am UTC

buy a faster machine, faster disk or tune the underlying query??

4gb is pretty small. I do bigger things on my laptop for demonstrations.

given the level of information supplied, that is about all I can say. I have to assume that if you are getting the 12028 you are using software written early last century as well - on hardware that likely comes from the same era?