| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Best way to drop and recreate and instance?
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_at_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: mark_at_cool-tools.co.uk
> ===================================================
> http://www.cool-tools.co.uk
> Maximising throughput & performance
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mark Leith
> INET: mark_at_cool-tools.co.uk
>
> 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:
> ListGuru_at_fatcity.com (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: Mark Leith INET: mark_at_cool-tools.co.uk 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: ListGuru_at_fatcity.com (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).Received on Wed Apr 24 2002 - 08:33:24 CDT
 |
 |