Home » SQL & PL/SQL » SQL & PL/SQL » droppping a snapshot (merged)
droppping a snapshot (merged) [message #313609] Sun, 13 April 2008 23:31 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi all,

I am unable to drop a snapshot , i tried even from sys, it is giving the following error,

drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists

but, when i try to create a new snapshot with the same name


CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............

I get
ORA-12006: a materialized view with the same user.name already exists


I am stuck with this from the past 4 hours , please help
Re: ORA-08103: object no longer exists [message #313610 is a reply to message #313609] Sun, 13 April 2008 23:34 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hey guys sorry, i shall post this one in the new-bee forum
droppping a snapshot [message #313611 is a reply to message #313609] Sun, 13 April 2008 23:36 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi all,

I am unable to drop a snapshot , i tried even from sys, it is giving the following error,

drop snapshot adm.dup_resource_status
9:46:29 ORA-08103: object no longer exists

but, when i try to create a new snapshot with the same name


CREATE MATERIALIZED VIEW ADM.DUP_RESOURCE_STATUS
..............

I get
ORA-12006: a materialized view with the same user.name already exists


I am stuck with this from the past 4 hours , please help
Re: ORA-08103: object no longer exists [message #313612 is a reply to message #313609] Sun, 13 April 2008 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
Yes, you have a problem.
Based upon what you have posted, nobody can reproduce what you have described.

You're On Your Own (YOYO)!
icon9.gif  Re: ORA-08103: object no longer exists [message #313619 is a reply to message #313609] Sun, 13 April 2008 23:51 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hey buddy, please help, do you have any suggesstions as to why i am unable to drop it ??
Re: droppping a snapshot [message #313621 is a reply to message #313611] Mon, 14 April 2008 00:00 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does
SELECT * FROM all_snapshots 
WHERE name = 'DUP_RESOURCE_STATUS';
say?
Re: droppping a snapshot [message #313623 is a reply to message #313611] Mon, 14 April 2008 00:05 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
OWNER ADM
NAME DUP_RESOURCE_STATUS
.....it goes on....which column should i post exactly ?
Re: droppping a snapshot [message #313625 is a reply to message #313623] Mon, 14 April 2008 00:11 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hey budyy, which column value do u want, so that it can help u resolve my problem
Re: droppping a snapshot [message #313626 is a reply to message #313623] Mon, 14 April 2008 00:13 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
Try
create or replace materialized view?
Re: ORA-08103: object no longer exists [message #313628 is a reply to message #313609] Mon, 14 April 2008 00:22 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
>hey buddy, please help, do you have any suggesstions as to why i am unable to drop it ??
How can anyone reproduce the problem?
Re: droppping a snapshot [message #313631 is a reply to message #313623] Mon, 14 April 2008 00:24 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
jagannathkiran wrote on Mon, 14 April 2008 07:05
OWNER ADM
NAME DUP_RESOURCE_STATUS
.....it goes on....which column should i post exactly ?

Wow! How many "DUP_RESOURCE_STATUS" snapshots do you have?

So, what does
SELECT COUNT(*) 
FROM all_snapshots 
WHERE owner = 'ADM'
  AND name = 'DUP_RESOURCE_STATUS';

Did you, by any chance, create that snapshot using double quotes so its name IS NOT "DUP_RESOURCE_STATUS"? If so, you'll have to use double quotes when dropping the snapshot as well.
Re: droppping a snapshot [message #313633 is a reply to message #313611] Mon, 14 April 2008 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have a table with name DUP_RESOURCE_STATUS?
SQL> create table mv (col integer);

Table created.

SQL> create materialized view mv as select * from t;
create materialized view mv as select * from t
                                             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Regards
Michel
Re: ORA-08103: object no longer exists [message #313639 is a reply to message #313609] Mon, 14 April 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost.

Regards
Michel
Re: droppping a snapshot [message #313641 is a reply to message #313631] Mon, 14 April 2008 00:49 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
SELECT COUNT(*)
FROM all_snapshots
WHERE owner = 'ADM'
AND name = 'DUP_RESOURCE_STATUS'

COUNT(*)
--------------------------------------
1

and i did not create the snapshot with double quotes, and I tried Create or replace and i get


ORA-00922: missing or invalid option

so, create or replace is not a correct syntax
Re: droppping a snapshot [message #313647 is a reply to message #313641] Mon, 14 April 2008 00:55 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you post the result of thet following code (SQL*Plus session)?
CONNECT adm

DROP SNAPSHOT dup_resource_status;
Re: droppping a snapshot [message #313648 is a reply to message #313631] Mon, 14 April 2008 00:55 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I have attached the result of the below query

SELECT * FROM all_snapshots
WHERE name = 'DUP_RESOURCE_STATUS'
Re: droppping a snapshot [message #313651 is a reply to message #313648] Mon, 14 April 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And your Oracle version is? (3 decimals)

Regards
Michel
Re: droppping a snapshot [message #313652 is a reply to message #313651] Mon, 14 April 2008 01:03 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
select * from V$version

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

Re: droppping a snapshot (merged) [message #313653 is a reply to message #313609] Mon, 14 April 2008 01:04 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
i dont have access to ADM, but i was able to do this many times before, i am sure on that
Re: droppping a snapshot (merged) [message #313654 is a reply to message #313653] Mon, 14 April 2008 01:08 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
i tried even from sys

So you have access to SYS but can't log on as ADM? If you can't/don't know how to alter user (and restore it after you're done), perhaps you should find someone who knows ADM's password.
Re: droppping a snapshot (merged) [message #313658 is a reply to message #313654] Mon, 14 April 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select owner, object_type 
from dba_objects 
where object_name='DUP_RESOURCE_STATUS'
order by 1, 2
/

Regards
Michel
Re: droppping a snapshot (merged) [message #313659 is a reply to message #313658] Mon, 14 April 2008 01:27 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
select owner, object_type
from dba_objects
where object_name='DUP_RESOURCE_STATUS'
order by 1, 2
/


OWNER OBJECT_TYPE
------------------------------ ------------------
ADM MATERIALIZED VIEW
ADM TABLE

Re: droppping a snapshot (merged) [message #313663 is a reply to message #313654] Mon, 14 April 2008 01:46 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
yeah...thanks everyone, i shall just ask the DBA here who just dropped in....
Re: droppping a snapshot (merged) [message #313664 is a reply to message #313659] Mon, 14 April 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post formatted as we did.
Read OraFAQ Forum Guide, "How to format your post?" section to know how to do it: use code tags.

Try to use "drop materialized view" instead of "drop snapshot".

Is this a fully database created in 9i or is this one migrated or imported from a previous version?

Regards
Michel


Re: droppping a snapshot (merged) [message #313665 is a reply to message #313664] Mon, 14 April 2008 01:51 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
"drop materialized view" is not working either !!

is there any way to force a drop materialized view/snapshot
Re: droppping a snapshot (merged) [message #313673 is a reply to message #313665] Mon, 14 April 2008 02:40 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Is not working" is meaningless. Please, copy and paste your SQL*Plus session so that we could SEE what you were doing. When saying copy/paste, I really mean it; I don't want you to type what happened, or even worse type what you *think* you saw on screen - exact copy/paste, properly formatted using [code] tags.
Re: droppping a snapshot (merged) [message #313675 is a reply to message #313665] Mon, 14 April 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...and answer the question I asked.

Regards
Michel
Re: droppping a snapshot (merged) [message #313688 is a reply to message #313675] Mon, 14 April 2008 03:45 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
 
  CREATE MATERIALIZED VIEW adm.dup_resource_status
  PCTFREE     10
  PCTUSED     40
  MAXTRANS    255
  TABLESPACE  v40usr1
  STORAGE   (
    INITIAL     131072
    NEXT        1048576
    PCTINCREASE 0
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOLOGGING
BUILD IMMEDIATE
REFRESH START WITH '14-APR-2008'NEXT TRUNC(SYSDATE+1)+22/24
AS
SELECT
  distinct rq.proj,
  pr.ds as proj_ds,
  pr.pc14,
  pr.pc11,
  av.uc01,
  pr.pc15,
  pr.pc12,
  pr.pc08,
  pr.pc07,
  rq.act,
  av.ds as act_ds,
  av.obs,
  el2.resp1 as func_mgr,
  STUDY_TYPE(av.uc02) as study_type,
  av.memo_sent,
  av.uc04,
  rq.rn,
  rs.rc02,
  el1.resp1 as    res_mgr,
  av.res_asm_flg,
  av.uc03,
  av.uc06,
  av.test_subst_amt,
  av.smp_alloc_flg,
  av.act_id,
  av.act_res_status,
  av.approved_date
FROM
  av_activity av,
  av_project pr,
  av_resreq rq,
  av_resource rs,
  av_element el1,
  av_element el2
WHERE
  pr.proj <> 'ADMIN-99'  and
  pr.proj not like 'PC%'    and
  pr.pc15 in ('ACTIVE','NOT ACTIVE','HOLD')    and
  av.pi <> 'C'    and
  av.memo_sent NOT IN (5,9) and
  av.proj = rq.proj    and
  av.proj = pr.proj    and
  av.act = rq.act    and
  av.ver=0    and
  av.ver = rq.ver    and
  rq.rn not like 'CONT%'    and
  rs.rn = rq.rn    and
  rs.uom = 'MANHOURS'    and
  rq.rn <> rs.rc01    and
  rq.rn <> av.uc04    and
  rs.rc02 <> av.obs    and
  el1.resp1<>el2.resp1    and
  rs.rc02 not like '%D'    and
  (el1.category = 'GT OBS' AND el1.element = rs.rc02)    and
  (el2.category = 'GT OBS' AND el2.element = av.obs)  


I get below error.

ORA-12006: a materialized view with the same user.name already exists

NOTE: I executed above in SYS
P.S "Sorry for the late reply guys, i had gone for lunch "
Re: droppping a snapshot (merged) [message #313689 is a reply to message #313688] Mon, 14 April 2008 03:48 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
When i try to drop the MV(materialized view) it gives following error(again i am in SYS)

 drop snapshot adm.dup_resource_status 

ORA-08103: object no longer exists
Re: droppping a snapshot (merged) [message #313696 is a reply to message #313689] Mon, 14 April 2008 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Michel Cadot wrote on Mon, 14 April 2008 08:49
Is this a fully database created in 9i or is this one migrated or imported from a previous version?


Regards
Michel
icon6.gif  Re: droppping a snapshot (merged) [message #313705 is a reply to message #313696] Mon, 14 April 2008 04:37 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
I am unsure about that, but i have to ask my onsite DBA for that.
But i was able to drop and recreate it umpteen times before !!
I dont know why there is a problem now.

I have currently created another snapshot whith the same query and temporarily working on it. Smile
icon7.gif  Re: droppping a snapshot (merged) [message #534778 is a reply to message #313705] Fri, 09 December 2011 01:35 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Michel, Littlefoot and BlackSwan, I really appreciate the effort and solutions you guys gave me long time go.

Cheers,
Kiran (Jaggy)
Previous Topic: To fire the triggers while Materialized FAST Refresh
Next Topic: null value
Goto Forum:
  


Current Time: Wed Dec 07 08:28:50 CST 2016

Total time taken to generate the page: 0.09757 seconds