RE: Best way to drop and recreate and instance?
Something to do with SYS.DUC$? Have you tried to delete all objects first (ie tables (with CASCADE CONSTRAINTS), views, types, packages, sequences, functions, procedures and synonyms - indexes, triggers and package bodies will go anyway) then to DROP USER without CASCADE ? Otherwise if your dictionary is beeped recreating the database is indeed probably the best you have to do. Would do it fairly easily under Unix (remove all files, and run CREATE DATABASE from scratch, catalog and all), I don't see any reason why it should be any different under Windows. You can probably even use OUI to recreate your database (sigh). - Original Message - From: Mark Leith [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 24 Apr 2002 03:53:19 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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Best way to drop and recreate and instance?
Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L 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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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). -- 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).
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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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).
RE: Best way to drop and recreate and instance?
Mark,
to remove an instance on NT (not the software, just the instance) do the
following:
Use ORADIM -sid {sid} -delete to remove the NT services for the instance.
then, simply remove all file structures for the database.
That's all there is to it. The database is gone.
On a side note - I have seen this type of error before, and it had to do
with Java not being installed correctly (within the database). I
re-installed Java (using initjvm.sql under the ORACLE_HOME/Javavm/install
directory and the error went away. This might not have any effect on your
problem. The Java reinstall takes about 20 minutes.
It's actually quicker to kill he instance and start again! :)
Hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Wednesday, April 24, 2002 7:53 AM
To: Multiple recipients of list ORACLE-L
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:
salesconnect sys/sys@sales
Connected.
salesselect username, password
2 from dba_users
3where username = 'SCOTT';
USERNAME PASSWORD
-- --
SCOTT F894844C34402B67
salesdrop 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
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: Best way to drop and recreate and instance?
The db config assistanct has a 'delete db' option that works quite well. even if it cannot connect to the db, it will still get rid of the registry and services bits for the db hth connor --- Mark Leith [EMAIL PROTECTED] 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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Best way to drop and recreate and instance?
Mark, Shame on you. Ignoring good old ORADIM? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 24, 2002 9:03 AM Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L 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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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: Best way to drop and recreate and instance?
Hi Mark, Regarding your problem with dropping a user, I've seen something like that before - this was 817 on Solaris, and the DBA there was getting a ORA-00942 at recursive SQL when trying any CTAS. I ran a trace on the statement and found that Oracle was looking for a sys.snap$ table which didn't exist (don't remember the name now). I also found that this database didn't have a single sys.snap$ table while another one on the same release had about 7-8. Later heard from the DBA that they weren't able to get replication working on the database either. Luckily for him, this was a test instance and hence not a serious issue.but I still never found out why those tables were missing. I had suggesting recreating the tables from sql.bsq (figured the db was knackered anyway, might as well try it), but I don't think he ever got around to doing it. I'd be very interested to know if a trace reveals anything to you... Hope this helps a bit.. Mandar. -Original Message- Sent: Wednesday, April 24, 2002 6:33 PM To: Multiple recipients of list ORACLE-L Never mind all - I cheated and used the Database Configuration Assistant to drop and create a new one.. ;P -Original Message- Sent: 24 April 2002 12:53 To: Multiple recipients of list ORACLE-L 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: salesconnect sys/sys@sales Connected. salesselect username, password 2 from dba_users 3where username = 'SCOTT'; USERNAME PASSWORD -- -- SCOTT F894844C34402B67 salesdrop 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). -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Shete 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
