Re: cant drop any user

On Nov 20, 11:28 am, ngarima..._at_gmail.com wrote:
> On 20 nov, 18:19, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Nov 20, 10:56 am, ngarima..._at_gmail.com 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 Received on Tue Nov 20 2007 - 12:09:52 CST