Home » Server Options » Replication » Probelm creating snapshot
Probelm creating snapshot [message #289404] Fri, 21 December 2007 05:06 Go to next message
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;
Re: Probelm creating snapshot [message #289407 is a reply to message #289404] Fri, 21 December 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Probelm creating snapshot [message #289448 is a reply to message #289404] Fri, 21 December 2007 14:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

Re: Probelm creating snapshot [message #289471 is a reply to message #289448] Sat, 22 December 2007 01:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Can you create or update other objects over the same DB link??
Re: Probelm creating snapshot [message #290013 is a reply to message #289404] Thu, 27 December 2007 02:05 Go to previous messageGo to next message
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.
Re: Probelm creating snapshot [message #290030 is a reply to message #290013] Thu, 27 December 2007 03:35 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

As per Mr.DreamzZ.

did you checked your DB Link?

Babu
Re: Probelm creating snapshot [message #290047 is a reply to message #289404] Thu, 27 December 2007 04:27 Go to previous messageGo to next message
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
Re: Probelm creating snapshot [message #290158 is a reply to message #290047] Thu, 27 December 2007 17:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
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;
Previous Topic: Pls Help
Next Topic: ORA-00119: invalid specification for system parameter LOCAL_LISTENER
Goto Forum:
  


Current Time: Thu Mar 28 15:13:17 CDT 2024