Re: cant drop any user

On Nov 20, 1:23 pm, ngarima..._at_gmail.com wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Then your catalog is corrupted and needs to be rebuilt.

David Fitzjarrell Received on Tue Nov 20 2007 - 13:38:29 CST