Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view (Oracle10g, SUSE Linux)
Materialized view [message #598850] Fri, 18 October 2013 06:19 Go to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hello,
When I try to drop the table it gives "ORA-12083: must use DROP MATERIALIZED VIEW ..." . When I try to drop the materialized view it gives
"ORA-12003: materialized view 'xxxx' does not exist". Could you please suggest what to do for this issue.

Database -Oracle10g
OS- SUSE Linux

Thanks,

[Updated on: Fri, 18 October 2013 06:29]

Report message to a moderator

Re: Materialized view [message #598857 is a reply to message #598850] Fri, 18 October 2013 07:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
This is a wrong forum for your technical question.

Anyway, follow these steps and let me know the output-
1. Query dba_mviews to see if the MV exists? If no, then,
2. Recretae the MV.
3. Drop the MV.
4. Drop the table.

[update : I said about the wrong forum since it was earlier posted in suggestions and feedback, now I see it is moved to SQL & PL/SQL]

[Updated on: Fri, 18 October 2013 07:36]

Report message to a moderator

icon2.gif  Re: Materialized view [message #598871 is a reply to message #598850] Fri, 18 October 2013 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session then we are sure that what you report is the correct thing.

Report message to a moderator

Re: Materialized view [message #598958 is a reply to message #598871] Mon, 21 October 2013 00:07 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Hello,
It's not done via SQl*plus session also. Getting the same error.Plz find the screen shot as an attachement for the same.

SQL> /
DROP MATERIALIZED VIEW GPS_MM_LDC_FRAME.MV_HUN
*
ERROR at line 1:
ORA-12003: materialized view "GPS_MM_LDC_FRAME"."MV_HUN" does not exist


SQL> DROP TABLE GPS_MM_LDC_FRAME.MV_HUN;
DROP TABLE GPS_MM_LDC_FRAME.MV_HUN
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "GPS_MM_LDC_FRAME"."MV_HUN


SQL> DROP MATERIALIZED VIEW GPS_MM_LDC_FRAME.MV_HUN;
DROP MATERIALIZED VIEW GPS_MM_LDC_FRAME.MV_HUN
*
ERROR at line 1:
ORA-12003: materialized view "GPS_MM_LDC_FRAME"."MV_HUN" does not exist


SQL>
  • Attachment: MV error.JPG
    (Size: 41.94KB, Downloaded 605 times)

[Updated on: Mon, 21 October 2013 00:09]

Report message to a moderator

Re: Materialized view [message #598959 is a reply to message #598958] Mon, 21 October 2013 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are connected as SYS and trying to drop an object that belongs to GPS_MM_LDC_FRAME user.

What is the result of
select * from all_objects where upper(object_name) = 'MV_HUN';

Report message to a moderator

Re: Materialized view [message #598963 is a reply to message #598959] Mon, 21 October 2013 01:52 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
I am dropping the objects (Table and View) via the parent user also but getting same error. See the attachement.

select owner, created, status, OBJECT_TYPE from all_objects where upper(object_name) = 'MV_HUN';

Output:

GPS_MM_LDC_FRAME 08/Oct/13 7:10:42 PM VALID TABLE

Report message to a moderator

Re: Materialized view [message #598964 is a reply to message #598963] Mon, 21 October 2013 02:01 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Have you tried recreating the MV, like suggested?

MHE

Report message to a moderator

Re: Materialized view [message #598975 is a reply to message #598964] Mon, 21 October 2013 03:49 Go to previous messageGo to next message
daulat01
Messages: 62
Registered: May 2011
Location: Delhi
Member
Yes I am already trying to do this isue by recreating MV but still getting error.

Report message to a moderator

Re: Materialized view [message #598988 is a reply to message #598975] Mon, 21 October 2013 04:23 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
And the creation of materialized view "GPS_MM_LDC_FRAME.MV_HUN" succeeded? If so, you might want to check Oracle support: there is already an entry in all_objects( same user, same object name). Can you select from that object?

MHE

Report message to a moderator

Re: Materialized view [message #599040 is a reply to message #598975] Mon, 21 October 2013 13:25 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
daulat01 wrote on Mon, 21 October 2013 14:19
Yes I am already trying to do this isue by recreating MV but still getting error.


Did you follow the steps I suggested above? Please post the output of each step.

Report message to a moderator

Previous Topic: Mutating error
Next Topic: Normal join and outer join
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 10:07:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.