Re: cant drop any user

On 20 nov, 22:17, DA Morgan <damor...@xxxxxxxxx> wrote:
ngarima...@xxxxxxxxx wrote:
On 20 nov, 20:38, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Nov 20, 1:23 pm, ngarima...@xxxxxxxxx wrote:

On 20 nov, 19:09, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Nov 20, 11:28 am, ngarima...@xxxxxxxxx wrote:
On 20 nov, 18:19, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Nov 20, 10:56 am, ngarima...@xxxxxxxxx wrote:
whem i try to drop any user or tbspaces i have the next error
drop user $USER CASCADE;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01422: exact fetch returns more than requested number of rows
i check the sys.dual and it have 1 row
select count(*) from dual;
COUNT(*)
----------
1
if i create a new user i can drop it, but cant drop the current users
or tbspaces.
anyone can help me with this problem plz
I want to see the entire shell script you're using to try to drop
users.
David Fitzjarrell- Ocultar texto de la cita -
- Mostrar texto de la cita -
there isnt any script
just "drop user *USER* cascade;"
i cant drop any user- Hide quoted text -
- Show quoted text -
No wonder, that's so wrong ...
What, exacly, is this *USER* noise? You should be doing something
like this:
select 'drop user '||username||' cascade;'
from dba_users
where username not like '%SYS%'
spool drop_users.sql
/
spool off
Then check the drop_users.sql script for user accounts you do NOT
want dropped and remove those lines. Then run the script as SYS:
connect / as sysdba
@drop_users
And you're done. You CANNOT use any sort of 'wildcard' like you were
trying to do when dropping users.
Tablespaces are the same way:
select 'drop tablespace '||tablespace_name||' including contents;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','UNDOTBS')
spool drop_tablespaces.sql
/
spool off
Again, check the drop_tablespaces.sql file for any tablespaces you do
NOT want dropped and remove those lines. And you run THAT script as
SYS as well:
connect / as sysdba
@drop_tablespaces
Of course you can seriously damage your system with these scripts and
resulting files if you're not careful or not familiar with how Oracle
works.
I would STRONGLY suggest you backup your database before you try any
of this. And this had best be your own personal database to play
with. I will NOT be responsible for any damage you do to a production
database with this information.
David Fitzjarrell- Ocultar texto de la cita -
- Mostrar texto de la cita -
Well $USER i a variable to indicate that can be any user
i will give the exact example so there is no more confusion
drop user testuser cascade;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01422: exact fetch returns more than requested number of rows
The user exist.- Hide quoted text -
- Show quoted text -
Then your catalog is corrupted and needs to be rebuilt.

David Fitzjarrell- Ocultar texto de la cita -

- Mostrar texto de la cita -

how can i rebuild it ?

If this is a production database you go tohttp://metalink.oracle.com
and you engage Oracle support. Corruption like this may not be
limited only to this single issue and you should be very careful.

Following advice from a usenet group or forum on the internet, no
matter how well intended, may have unintended consequences. I would
suggest you do nothing without Oracle support's instructions.

That said ...
1. Perform a level 0 backup immediately and verify that it is good.
2. Download the RDA tool from metalink and create an RDA.
3. Open an SR at metalink and upload the RDA.

This is not a good time to do anything creative just to see what
happens. Few things can get better. Many things can get worse.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damor...@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org- Ocultar texto de la cita -

- Mostrar texto de la cita -

thx for the advice, but isnt a production database, is just a test
server
.

Relevant Pages

  • Re: cant drop any user
    ... I want to see the entire shell script you're using to try to drop ... David Fitzjarrell- Ocultar texto de la cita - ... and you engage Oracle support. ...
    (comp.databases.oracle.server)
  • Re: process all lines after a match
    ... I plan to make a script that will run every 20 minutes ... filter this with something like "awk" to do the conversion between ... Ed.- Ocultar texto de la cita - ...
    (comp.unix.shell)
  • =?iso-8859-1?q?Re:_Soldado_reclama_a_Ch=E1vez_en_Cadena_nacional?=
    ... publicamente por chavez luego de interrumpirlo y prohibido de volver a ... la reina de Inglaterra, y los acuso publicamente de ser mentirosos, lo ... Jose Marti- Ocultar texto de la cita - ...
    (soc.culture.venezuela)
  • =?iso-8859-1?q?Re:_Soldado_reclama_a_Ch=E1vez_en_Cadena_nacional?=
    ... Matt- Ocultar texto de la cita - ... publicamente por chavez luego de interrumpirlo y prohibido de volver a ... la reina de Inglaterra, y los acuso publicamente de ser mentirosos, lo ...
    (soc.culture.venezuela)