Re: cant drop any user

On Nov 20, 3:17 pm, 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- Hide quoted text -

- Show quoted text -

I must agree with Daniel and his advice; mine was misplaced although
well-intentioned.


David Fitzjarrell
.

Relevant Pages

  • Re: cant drop any user
    ... error occurred at recursive SQL level 2 ... exact fetch returns more than requested number of rows ... spool drop_users.sql ... Then check the drop_users.sql script for user accounts you do NOT ...
    (comp.databases.oracle.server)
  • Re: No one could logon to productio database for a while
    ... Puget Sound Oracle Users Group ... code for script is quite simple, it does a v$sql to find all sql ... The script ask for the hash value of the SQL statement. ... col "SLAVE SQL" format A95 WORD_WRAP ...
    (comp.databases.oracle.server)
  • Re: Data Pump error
    ... Copyright 2003, 2005, Oracle. ... Oracle Database 10g Express Edition Release 10.2.0.1.0 - ... If I just installed Oracle Express, what is the password for SYSDBA? ... Then you can run suggested script (just paste or type the ...
    (comp.databases.oracle.tools)
  • Problem installing an Oracle patch using Perl on sunfire 3800
    ... Oracle installs fine, ... the patch script supplied by Oracle, ... waitid() ...
    (SunManagers)
  • Re: Solaris 10: cannot gracefully shutdown legacy services?
    ... Oracle starts up just fine. ... > processes with a SIGTERM. ... > Reading `man init`, I find: ... > This invokes the script, ...
    (comp.unix.solaris)