On 20 nov, 22:17, DA Morgan <damor..._at_psoug.org> wrote:
> ngarima..._at_gmail.com wrote:
> > On 20 nov, 20:38, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> >> 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- 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..._at_x.washington.edu (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
Received on Tue Nov 20 2007 - 15:47:20 CST
