OraFAQ Forum: Replication » Probelm creating snapshot

Home » Server Options » Replication » Probelm creating snapshot

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Probelm creating snapshot [message #289404]

Fri, 21 December 2007 05:06

marin.mandic
Messages: 5
Registered: December 2007

Junior Member

I have problem when creating snapshot. My db link bibe is working.
It does not generate any error, it just do nothing.

this select works ok
SELECT * FROM mpglava@bibe;

but this create snapsohot does not
CREATE SNAPSHOT mv_mpglava_bibe
PCTFREE 15
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE optimus
USING INDEX
PCTFREE 0
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE optimus_i
REFRESH FORCE AS
SELECT * FROM mpglava@bibe;

Report message to a moderator

Re: Probelm creating snapshot [message #289407 is a reply to message #289404]

Fri, 21 December 2007 06:01

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste the execution of the statement.
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: Probelm creating snapshot [message #289448 is a reply to message #289404]

Fri, 21 December 2007 14:32

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

In the following, all I changed was the table name, talbespace names, and db link name, so the syntax is correct. You say it "does nothing". Does it show materialized view created, but not contain any rows or does it appear to just hang? Perhaps you have not waited long enough for it to finish. Have you tried testing on a smaller data set?

SCOTT@orcl_11g> SELECT * FROM dept@orcl_11g@loopback
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> CREATE SNAPSHOT mv_mpglava_bibe
  2  PCTFREE 15
  3  STORAGE
  4  (INITIAL 200K
  5  NEXT 200K
  6  PCTINCREASE 0)
  7  TABLESPACE users
  8  USING INDEX
  9  PCTFREE 0
 10  STORAGE
 11  (INITIAL 200K
 12  NEXT 200K
 13  PCTINCREASE 0)
 14  TABLESPACE users
 15  REFRESH FORCE AS
 16  SELECT * FROM dept@orcl_11g@loopback
 17  /

Materialized view created.

SCOTT@orcl_11g> SELECT * FROM mv_mpglava_bibe
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g> drop snapshot mv_mpglava_bibe
  2  /

Materialized view dropped.

Report message to a moderator

Re: Probelm creating snapshot [message #289471 is a reply to message #289448]

Sat, 22 December 2007 01:44

marin.mandic
Messages: 5
Registered: December 2007

Junior Member

I know the sintax is correct becouse it works when i create snapshot from another 10xe database.
It does not show materialized view created, data set is only one row but it just hang. I have waited long enough, half hour Confused

[Updated on: Sat, 22 December 2007 01:45]

Report message to a moderator

Re: Probelm creating snapshot [message #289501 is a reply to message #289471]

Sat, 22 December 2007 14:41

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

Just out of curiosity, do you have the same problem if you use "materialized view" instead of "snapshot"? Have you tried using tracing and checking the trace file and alert log, or using v$session_longops to try to find out what it is doing that is taking so long?

[Updated on: Sat, 22 December 2007 14:42]

Report message to a moderator

Re: Probelm creating snapshot [message #289919 is a reply to message #289501]

Wed, 26 December 2007 10:24

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Can you create or update other objects over the same DB link??

Report message to a moderator

Re: Probelm creating snapshot [message #290013 is a reply to message #289404]

Thu, 27 December 2007 02:05

marin.mandic
Messages: 5
Registered: December 2007

Junior Member

I tried using materialized view but the problem is same, alert.log does not show anything suspicious.

Report message to a moderator

Re: Probelm creating snapshot [message #290030 is a reply to message #290013]

Thu, 27 December 2007 03:35

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

As per Mr.DreamzZ.

did you checked your DB Link?

Babu

Report message to a moderator

Re: Probelm creating snapshot [message #290047 is a reply to message #289404]

Thu, 27 December 2007 04:27

marin.mandic
Messages: 5
Registered: December 2007

Junior Member

As I say:
this select works ok
SELECT * FROM mpglava@bibe;

so DB Link works fine

Report message to a moderator

Re: Probelm creating snapshot [message #290158 is a reply to message #290047]

Thu, 27 December 2007 17:30

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

Are your select privileges granted explicitly or through a role? If you SET ROLE NONE, can you still run the select? You need to have the privilege granted explicitly, not through a role, in order to use it in a procedure or snapshot and other such things. However, I would expect that to product an error, rather than just seem to hang. I would try using v$session_longops to try to narrow down what is causing the bottleneck. Ordinarily, I would expect that it is waiting for a lock to be released or some such thing, but I would also expect that force would eliminate that problem.

[Updated on: Thu, 27 December 2007 17:33]

Report message to a moderator

Re: Probelm creating snapshot [message #290231 is a reply to message #289404]

Fri, 28 December 2007 02:25

marin.mandic
Messages: 5
Registered: December 2007

Junior Member

I try to use v$session_longops but select does not show any record.

I notice that I have a lock that this session produce, last sql statement (from lock) is:
begin dbms_utility.db_version@BIBE(:version, :compat); end;

Report message to a moderator

Previous Topic:	Pls Help
Next Topic:	ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 11:56:20 CST 2025