ngarimaldi_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 to http://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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Nov 20 2007 - 15:17:06 CST
