1 2 Previous Next 17 Replies Latest reply: Nov 30, 2012 3:49 PM by jgarry RSS

    user is not droping

    902332
      hi,

      db is 10.2.0.4.0

      trying to delete user:
      drop user <user_name> cascade;
      but saying user is currently connected,

      when i checked there is not sid, serial# from v$session.

      in the last seesion i treid to drop, but there it was hung.

      can you please help me on this. it's urgent pls.

      thanks.
        • 1. Re: user is not droping
          sb92075
          899329 wrote:
          hi,

          db is 10.2.0.4.0

          trying to delete user:
          drop user <user_name> cascade;
          but saying user is currently connected,

          when i checked there is not sid, serial# from v$session.

          in the last seesion i treid to drop, but there it was hung.

          can you please help me on this. it's urgent pls.
          explain why it is urgent for us to solve this issue for you?

          SHUTDOWN IMMEDIATE
          STARTUP

          doing as above will break the existing logjam
          • 2. Re: user is not droping
            Zoran Pavlovic
            Try to revoke create session from user. Also try:
            select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = 'username'
            Zoran
            • 3. Re: user is not droping
              902332
              it's production
              • 4. Re: user is not droping
                Mark D Powell
                It would be appropriate to post SQL queries and results showing that the user is not connected, etc ....

                It has often proven more efficient in the past to first drop user objects before attempting to drop the user from the database.

                Any change the user was logged on and logged off before you checked so if you just re-issue the drop user that it works?


                HTH -- Mark D Powell --
                • 5. Re: user is not droping
                  sb92075
                  899329 wrote:
                  it's production
                  what is OS name, version & edition?
                  • 6. Re: user is not droping
                    Osama_Mustafa
                    899329 wrote:
                    it's production
                    If you just post information such as Db version ,OS Version , and Output of SID,SERIAL#,USername from v$session that could be useful also
                    • 7. Re: user is not droping
                      902332
                      hi,

                      i am using database 10.2.0.4.0 and os is Linux x812.coat.com 2.6.32.36-0.5-default #1 SMP 2011-04-14 10:12:31 +0200 x86_64 x86_64 x86_64 GNU/Linux


                      select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE from dba_users where username='xxxxx';

                      here result is giving like user information. user is existing.

                      but when i am trying to drop:

                      SQL> DROP USER xxxxx CASCADE;
                      DROP USER EMELTON CASCADE
                      *
                      ERROR at line 1:
                      ORA-01940: cannot drop a user that is currently connected


                      when i check

                      SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='XXXXX';

                      no rows selected


                      Please let me know how can we drop this user
                      • 8. Re: user is not droping
                        Fran
                        Please, try this query:
                        select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'XXXXX' and p.addr (+) = s.paddr;

                        *maybe you must use s.username like '%XXXX%'

                        **another tip will be lock the user account, or revoke create session privelege to the user.

                        Edited by: Fran on 30-nov-2012 1:23
                        • 9. Re: user is not droping
                          902332
                          hi

                          SQL> select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'XXXXX' and p.addr = s.paddr;

                          no rows selected


                          actually another session was opened and through that session user has been droped. But it was in hung stat so that session was closed and in a new session is opened. but when trying to drop it is giving this type of errors.
                          • 10. Re: user is not droping
                            902332
                            i think it's deleted the hole schema and except the entry in dba_users view.

                            can i manually delete the entry for the user from dba_users view.

                            please suggest.

                            thanks
                            • 11. Re: user is not droping
                              Kaliya
                              This can happens when you have dblink connectivity / multi master replication connection or might be check the MATERIALIZED VIEW refresh jobs.
                              • 12. Re: user is not droping
                                DB
                                Use Toad to kill the session.Issue will be solved.
                                • 13. Re: user is not droping
                                  Mark D Powell
                                  Where is the result of the query against dba_users? Any change this user is set up to use a proxy user? The full (select *) output would be desirable. Another poster has raised the possibility of the username being used in a link but I am pretty sure the link name should show up on a query against v$session for the username.

                                  If the user is truely not connected but the drop user reports the user as connected I believe you would need to bounce the instance to fix the issue.

                                  HTH -- Mark D Powell --
                                  • 14. Re: user is not droping
                                    Richard Harrison .
                                    Hi,
                                    Don;t try and delete from dba_users - even if it did work you'll make your problem a lot worse!

                                    I think a restart will fix it but appreciate that may be difficult on a prod system.....

                                    Regards,
                                    Rich
                                    1 2 Previous Next