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.
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 ?
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.
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
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 ?
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?