Re: cannot drop snapshot with partition.
Hello, We had the same problem and here comes one solution : 0. Pray !!! 1. Full backup 2. -- Change u.name and o.name UPDATE tab$ t set t.property = t.property - 67108864 where bitand(t.property, 67108864) = 67108864 and t.obj# = (select o.obj# from sys.user$ u , sys.obj$ o where u.user# = o.owner# and o.obj# = t.obj# and u.name = 'SC' and o.name = 'STOCKS'); commit; Good luck .. Regards Henrik --- Henrik Ekenberg Anoto AB www.anoto.com On Thu, 31 May 2001, A. Bardeen wrote: -!-Sandesh, -!- -!-This is a known bug (1335477) that allows you to -!-rename a snapshot/materialized view, which you -!-shouldn't be allowed to do. What you've got now is -!-data dictionary corruption because not all of the data -!-dictionary entries were changed when the snapshot was -!-renamed. -!- -!-You can try renaming the snapshot back to its original -!-name, but I suspect you'll get an error that the -!-object already exists. -!- -!-Your only option, other than a PITR prior to when the -!-snapshot was renamed, is probably to log a tar with -!-support so they can walk you through correcting the -!-data dictionary corruption. This is not something I'd -!-advise trying on your own unless you can afford to -!-lose this db. -!- -!-HTH, -!- -!--- Anita -!- -!- -! Naik, Sandesh S [EMAIL PROTECTED] wrote: -!- Hi All, -!- I have a 8.1.6.2 database on hp9000. (11.0). I -!- have created one snapshot , -!- with partition. Now I not able to drop snapshot or -!- nor I cam drop last -!- partition. -!- It does not show that snapshot in dba_snapshot. I -!- renamed the snapshot to -!- old. But drop snapshot is not working -!- SQL drop snapshot old; -!- drop snapshot old -!- * -!- ERROR at line 1: -!- ORA-12003: snapshot SYS.OLD does not exist -!- If I try to drop the table then it gives this error -!- SQL drop table old; -!- drop table old -!-* -!- ERROR at line 1: -!- ORA-12083: must use DROP MATERIALIZED VIEW to drop -!- SYS.OLD -!- -!- Does somebody has encountered this error or has -!- solution to this problem. -!- -!- Sandesh -!- -- -!- Please see the official ORACLE-L FAQ: -!- http://www.orafaq.com -!- -- -!- Author: Naik, Sandesh S -!- INET: [EMAIL PROTECTED] -!- -!- Fat City Network Services-- (858) 538-5051 FAX: -!- (858) 538-5051 -!- San Diego, California-- Public Internet -!- access / Mailing Lists -!- -!- -!- To REMOVE yourself from this mailing list, send an -!- E-Mail message -!- to: [EMAIL PROTECTED] (note EXACT spelling of -!- 'ListGuru') and in -!- the message BODY, include a line containing: UNSUB -!- ORACLE-L -!- (or the name of mailing list you want to be removed -!- from). You may -!- also send the HELP command for other information -!- (like subscribing). -!- -!- -!-__ -!-Do You Yahoo!? -!-Get personalized email addresses from Yahoo! Mail - only $35 -!-a year! http://personal.mail.yahoo.com/ -!--- -!-Please see the official ORACLE-L FAQ: http://www.orafaq.com -!--- -!-Author: A. Bardeen -!- INET: [EMAIL PROTECTED] -!- -!-Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 -!-San Diego, California-- Public Internet access / Mailing Lists -!- -!-To REMOVE yourself from this mailing list, send an E-Mail message -!-to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in -!-the message BODY, include a line containing: UNSUB ORACLE-L -!-(or the name of mailing list you want to be removed from). You may -!-also send the HELP command for other information (like subscribing). -!- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: cannot drop snapshot with partition.
Step 1a and 2a should also be PRAY. And you should continue praying, crossing your fingers and making burnt offerings to any of the gods you know of and believe in until the day you no longer use that database. /rant on Editing the underlying tab$ table is begging for trouble on a production database. Unless you LIKE living dangerously and unsupported. Do what you want on your own little test database, that's what it's there for -- to break, fix, and learn what can't be fixed once broken. Are you planning on fixing his database if it breaks? SHEESH!! /rant off From: Henrik Ekenberg [EMAIL PROTECTED] [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: cannot drop snapshot with partition. Date: Fri, 01 Jun 2001 01:55:27 -0800 Hello, We had the same problem and here comes one solution : 0. Pray !!! 1. Full backup 2. -- Change u.name and o.name UPDATE tab$ t set t.property = t.property - 67108864 where bitand(t.property, 67108864) = 67108864 and t.obj# = (select o.obj# from sys.user$ u , sys.obj$ o where u.user# = o.owner# and o.obj# = t.obj# and u.name = 'SC' and o.name = 'STOCKS'); commit; Good luck .. Regards Henrik --- Henrik Ekenberg Anoto AB www.anoto.com On Thu, 31 May 2001, A. Bardeen wrote: -!-Sandesh, -!- -!-This is a known bug (1335477) that allows you to -!-rename a snapshot/materialized view, which you -!-shouldn't be allowed to do. What you've got now is -!-data dictionary corruption because not all of the data -!-dictionary entries were changed when the snapshot was -!-renamed. -!- -!-You can try renaming the snapshot back to its original -!-name, but I suspect you'll get an error that the -!-object already exists. -!- -!-Your only option, other than a PITR prior to when the -!-snapshot was renamed, is probably to log a tar with -!-support so they can walk you through correcting the -!-data dictionary corruption. This is not something I'd -!-advise trying on your own unless you can afford to -!-lose this db. -!- -!-HTH, -!- -!--- Anita -!- -!- -! Naik, Sandesh S [EMAIL PROTECTED] wrote: -!- Hi All, -!- I have a 8.1.6.2 database on hp9000. (11.0). I -!- have created one snapshot , -!- with partition. Now I not able to drop snapshot or -!- nor I cam drop last -!- partition. -!- It does not show that snapshot in dba_snapshot. I -!- renamed the snapshot to -!- old. But drop snapshot is not working -!- SQL drop snapshot old; -!- drop snapshot old -!- * -!- ERROR at line 1: -!- ORA-12003: snapshot SYS.OLD does not exist -!- If I try to drop the table then it gives this error -!- SQL drop table old; -!- drop table old -!-* -!- ERROR at line 1: -!- ORA-12083: must use DROP MATERIALIZED VIEW to drop -!- SYS.OLD -!- -!- Does somebody has encountered this error or has -!- solution to this problem. -!- -!- Sandesh -!- -- -!- Please see the official ORACLE-L FAQ: -!- http://www.orafaq.com -!- -- -!- Author: Naik, Sandesh S -!- INET: [EMAIL PROTECTED] -!- -!- Fat City Network Services-- (858) 538-5051 FAX: -!- (858) 538-5051 -!- San Diego, California-- Public Internet -!- access / Mailing Lists -!- -!- -!- To REMOVE yourself from this mailing list, send an -!- E-Mail message -!- to: [EMAIL PROTECTED] (note EXACT spelling of -!- 'ListGuru') and in -!- the message BODY, include a line containing: UNSUB -!- ORACLE-L -!- (or the name of mailing list you want to be removed -!- from). You may -!- also send the HELP command for other information -!- (like subscribing). -!- -!- -!-__ -!-Do You Yahoo!? -!-Get personalized email addresses from Yahoo! Mail - only $35 -!-a year! http://personal.mail.yahoo.com/ -!--- -!-Please see the official ORACLE-L FAQ: http://www.orafaq.com -!--- -!-Author: A. Bardeen -!- INET: [EMAIL PROTECTED] -!- -!-Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 -!-San Diego, California-- Public Internet access / Mailing Lists -!- -!-To REMOVE yourself from this mailing list, send an E-Mail message -!-to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in -!-the message BODY, include a line containing: UNSUB ORACLE-L -!-(or the name of mailing list you want to be removed from). You may -!-also send the HELP command for other information (like subscribing). -!- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henrik Ekenber INET: [EMAIL PROTECTED] [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: cannot drop snapshot with partition.
I've had a couple of occasions where I had to edit the DD like this. It was done under the supervision of Oracle Support however, and if you want any help from them with your databases, you won't do this without their assistance. Jared On Friday 01 June 2001 02:55, Henrik Ekenberg wrote: Hello, We had the same problem and here comes one solution : 0. Pray !!! 1. Full backup 2. -- Change u.name and o.name UPDATE tab$ t set t.property = t.property - 67108864 where bitand(t.property, 67108864) = 67108864 and t.obj# = (select o.obj# from sys.user$ u , sys.obj$ o where u.user# = o.owner# and o.obj# = t.obj# and u.name = 'SC' and o.name = 'STOCKS'); commit; Good luck .. Regards Henrik --- Henrik Ekenberg Anoto AB www.anoto.com On Thu, 31 May 2001, A. Bardeen wrote: -!-Sandesh, -!- -!-This is a known bug (1335477) that allows you to -!-rename a snapshot/materialized view, which you -!-shouldn't be allowed to do. What you've got now is -!-data dictionary corruption because not all of the data -!-dictionary entries were changed when the snapshot was -!-renamed. -!- -!-You can try renaming the snapshot back to its original -!-name, but I suspect you'll get an error that the -!-object already exists. -!- -!-Your only option, other than a PITR prior to when the -!-snapshot was renamed, is probably to log a tar with -!-support so they can walk you through correcting the -!-data dictionary corruption. This is not something I'd -!-advise trying on your own unless you can afford to -!-lose this db. -!- -!-HTH, -!- -!--- Anita -!- -!- -! Naik, Sandesh S [EMAIL PROTECTED] wrote: -!- Hi All, -!- I have a 8.1.6.2 database on hp9000. (11.0). I -!- have created one snapshot , -!- with partition. Now I not able to drop snapshot or -!- nor I cam drop last -!- partition. -!- It does not show that snapshot in dba_snapshot. I -!- renamed the snapshot to -!- old. But drop snapshot is not working -!- SQL drop snapshot old; -!- drop snapshot old -!- * -!- ERROR at line 1: -!- ORA-12003: snapshot SYS.OLD does not exist -!- If I try to drop the table then it gives this error -!- SQL drop table old; -!- drop table old -!-* -!- ERROR at line 1: -!- ORA-12083: must use DROP MATERIALIZED VIEW to drop -!- SYS.OLD -!- -!- Does somebody has encountered this error or has -!- solution to this problem. -!- -!- Sandesh -!- -- -!- Please see the official ORACLE-L FAQ: -!- http://www.orafaq.com -!- -- -!- Author: Naik, Sandesh S -!- INET: [EMAIL PROTECTED] -!- -!- Fat City Network Services-- (858) 538-5051 FAX: -!- (858) 538-5051 -!- San Diego, California-- Public Internet -!- access / Mailing Lists -!- -!- -!- To REMOVE yourself from this mailing list, send an -!- E-Mail message -!- to: [EMAIL PROTECTED] (note EXACT spelling of -!- 'ListGuru') and in -!- the message BODY, include a line containing: UNSUB -!- ORACLE-L -!- (or the name of mailing list you want to be removed -!- from). You may -!- also send the HELP command for other information -!- (like subscribing). -!- -!- -!-__ -!-Do You Yahoo!? -!-Get personalized email addresses from Yahoo! Mail - only $35 -!-a year! http://personal.mail.yahoo.com/ -!--- -!-Please see the official ORACLE-L FAQ: http://www.orafaq.com -!--- -!-Author: A. Bardeen -!- INET: [EMAIL PROTECTED] -!- -!-Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 -!-San Diego, California-- Public Internet access / Mailing Lists -!- -!-To REMOVE yourself from this mailing list, send an E-Mail message -!-to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in -!-the message BODY, include a line containing: UNSUB ORACLE-L -!-(or the name of mailing list you want to be removed from). You may -!-also send the HELP command for other information (like subscribing). -!- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: cannot drop snapshot with partition.
do this create table your_snapshot_name (test number); drop table your_snapshot_name now you would be able to recreate the snapshot. i have seen this on 8.1.5. maybe its still there on 8162 babu -! Naik, Sandesh S [EMAIL PROTECTED] wrote: -!- Hi All, -!- I have a 8.1.6.2 database on hp9000. (11.0). I -!- have created one snapshot , -!- with partition. Now I not able to drop snapshot or -!- nor I cam drop last -!- partition. -!- It does not show that snapshot in dba_snapshot. I -!- renamed the snapshot to -!- old. But drop snapshot is not working -!- SQL drop snapshot old; -!- drop snapshot old -!- * -!- ERROR at line 1: -!- ORA-12003: snapshot SYS.OLD does not exist -!- If I try to drop the table then it gives this error -!- SQL drop table old; -!- drop table old -!-* -!- ERROR at line 1: -!- ORA-12083: must use DROP MATERIALIZED VIEW to drop -!- SYS.OLD -!- -!- Does somebody has encountered this error or has -!- solution to this problem. -!- -!- Sandesh -!- -- -!- Please see the official ORACLE-L FAQ: -!- http://www.orafaq.com -!- -- -!- Author: Naik, Sandesh S -!- INET: [EMAIL PROTECTED] -!- -!- Fat City Network Services-- (858) 538-5051 FAX: -!- (858) 538-5051 -!- San Diego, California-- Public Internet -!- access / Mailing Lists -!- -!- -!- To REMOVE yourself from this mailing list, send an -!- E-Mail message -!- to: [EMAIL PROTECTED] (note EXACT spelling of -!- 'ListGuru') and in -!- the message BODY, include a line containing: UNSUB -!- ORACLE-L -!- (or the name of mailing list you want to be removed -!- from). You may -!- also send the HELP command for other information -!- (like subscribing). -!- -!- -!-__ -!-Do You Yahoo!? -!-Get personalized email addresses from Yahoo! Mail - only $35 -!-a year! http://personal.mail.yahoo.com/ -!--- -!-Please see the official ORACLE-L FAQ: http://www.orafaq.com -!--- -!-Author: A. Bardeen -!- INET: [EMAIL PROTECTED] -!- -!-Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 -!-San Diego, California-- Public Internet access / Mailing Lists -!- -!-To REMOVE yourself from this mailing list, send an E-Mail message -!-to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in -!-the message BODY, include a line containing: UNSUB ORACLE-L -!-(or the name of mailing list you want to be removed from). You may -!-also send the HELP command for other information (like subscribing). -!- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: orclbabu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: cannot drop snapshot with partition.
Sandesh, This is a known bug (1335477) that allows you to rename a snapshot/materialized view, which you shouldn't be allowed to do. What you've got now is data dictionary corruption because not all of the data dictionary entries were changed when the snapshot was renamed. You can try renaming the snapshot back to its original name, but I suspect you'll get an error that the object already exists. Your only option, other than a PITR prior to when the snapshot was renamed, is probably to log a tar with support so they can walk you through correcting the data dictionary corruption. This is not something I'd advise trying on your own unless you can afford to lose this db. HTH, -- Anita --- Naik, Sandesh S [EMAIL PROTECTED] wrote: Hi All, I have a 8.1.6.2 database on hp9000. (11.0). I have created one snapshot , with partition. Now I not able to drop snapshot or nor I cam drop last partition. It does not show that snapshot in dba_snapshot. I renamed the snapshot to old. But drop snapshot is not working SQL drop snapshot old; drop snapshot old * ERROR at line 1: ORA-12003: snapshot SYS.OLD does not exist If I try to drop the table then it gives this error SQL drop table old; drop table old * ERROR at line 1: ORA-12083: must use DROP MATERIALIZED VIEW to drop SYS.OLD Does somebody has encountered this error or has solution to this problem. Sandesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naik, Sandesh S INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
