Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cant drop any user

Re: cant drop any user

From: <ngarimaldi_at_gmail.com>
Date: Tue, 20 Nov 2007 11:23:36 -0800 (PST)
Message-ID: <fe2c9688-58ac-4bce-a55d-ab21f966fe30@s8g2000prg.googlegroups.com>


On 20 nov, 19:09, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- 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. Received on Tue Nov 20 2007 - 13:23:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US