Hi Witold, Thanks for the reply..
I had already dropped all of scott's objects (although this does actually occur with *any* user that I tried it against), then connected as SYS as shown below, and still got the same error. I also re-ran catalog.sql and catproc.sql.. There were no other objects within the database relating to any created/owned by scott.. I've now dropped the instance and re-created it ;P Being a sales critter doesn't give me the time I would prefer to get my hands dirty with a "real" DBA type job.. Thanks for the help anyway Mark -----Original Message----- Sent: 24 April 2002 13:02 To: Mark Leith Hi I don't remember what others recommended and don't know what you have tried so far but if I had similar problem I would check dba_objects to see which objects the SCOTT owns. Maybe it will give you a clue. If it didn't help, I would connect as SCOTT and droped his objects. Then I would connect as system and dropped SCOTT. There probably may be many reasons but one I can think of is that there is an object/synonym/grant problem. It should take less time than recreating your Oracle instance. Plus it may be opportunity to learn something. If you decide to try and find the problem I would like to know. I don't know the details, and am not a DBA but have done some "dba'ing" and have seen few problems when a user owned an object and there was a public synonym, with the same name, pointing to non-existing object. HTH Witold On 24 Apr 2002 at 3:53, Mark Leith wrote: > Hi All, > > I have *finally* got time to drop one of our test instances and > recreate it (tomorrow), and wanted some feedback from all of you as to > the best way to go around this? > > The current instance is 8.1.7. on a Win2K machine.. The reason that > I'm dropping/recreating (some of you may remember this) is because I > simply *CANNOT* drop a user: > > sales>connect sys/sys@sales > Connected. > sales>select username, password > 2 from dba_users > 3 where username = 'SCOTT'; > > USERNAME PASSWORD > ------------------------------ ------------------------------ > SCOTT F894844C34402B67 > > sales>drop user scott cascade; > drop user scott cascade > * > ERROR at line 1: > ORA-00604: error occurred at recursive SQL level 1 > ORA-00942: table or view does not exist > > LOL! > > If anybody has seen this since I last asked the list, and has a > solution, please speak up now as this will save me the time of > dropping/recreating the instance.. > > Moving on - what would you consider the best approach to dropping and > recreating an instance on a Win32 platform? In the past (as these are > purely test instances) I have simply blasted the whole directories > (Oracle and all), then deleted any mention of "Oracle" within the > registry (after taking a backup of course), and moved on to a > completely fresh install (usually to go to a higher version). This > time though, I would like to keep 8.1.7 installed on this box, and > would prefer to keep Oracle installed, and simply drop and recreate a > fresh (empty) instance.. > > How would you go about doing this? > > Advanced thanks. > > Mark > > =================================================== > Mark Leith | T: +44 (0)1905 330 281 > Sales & Marketing | F: +44 (0)870 127 5283 > Cool Tools UK Ltd | E: [EMAIL PROTECTED] > =================================================== > http://www.cool-tools.co.uk > Maximising throughput & performance > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mark Leith > 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). ================================== Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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).
