Offer at PACKT

Good chance to grab your Oracle 11g R1/R2 Real Application Clusters Essentials at PACKT

Find whether stale statistics

Column STALE_STATS from *_tab_statistics. click here for description

SQL> select table_name,object_type,num_rows,last_analyzed,stale_stats from user_tab_statistics;

TABLE_NAME                     OBJECT_TYPE    NUM_ROWS LAST_ANAL STA

------------------------------ ------------ ---------- --------- ---

ADDRESS                        TABLE                 0 08-NOV-12 NO

GANIBHAI                       TABLE                10 08-NOV-12 NO

T1                             TABLE             10000 23-JUN-12 NO

T1_BACKUP                      TABLE             20700 27-JUN-12 NO

T2                             TABLE               999 23-JUN-12 NO

TEST                           TABLE               298 14-SEP-12 NO

ORA-01940: cannot drop a user that is currently connected 

There may be situations we need to drop schemas on request. What if, we try to drop and user sessions connecting continuously from application end. Though we kill session connection establishes again. We have one option, first is to LOCK the user account (This tip given by my friend Mohammed Gani sait) simple but will not strike when we are trying urgently

Just to stimulate the error the following illustration

SQL> create user drop_user identified by drop123
  2  default tablespace users
  3  ;

User created.

SQL> grant create session to drop_user;

Grant succeeded.

--established few connections--

SQL> select username,osuser,sid,serial#,process from v$session where username='DROP_USER';

USERNAME                       OSUSER                                SID    SERIAL# PROCESS
------------------------------ ------------------------------ ---------- ---------- ----------------
DROP_USER                      ASIAPACIFIC\athil                       8         21 9120:7896
DROP_USER                      ASIAPACIFIC\athil                      53         21 7488:8436
DROP_USER                      ASIAPACIFIC\athil                      93         23 6128:2724
DROP_USER                      ASIAPACIFIC\athil                      95         15 9116:1436


SQL> drop user drop_user;
drop user drop_user
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
                         

--Dynamic query for killing sessions --
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';

Kill string                                                                    
--------------------------------------------------------------------------------
alter system kill session '15,9' immediate;                                    
alter system kill session '50,13' immediate;                                  
alter system kill session '53,3' immediate;                                    
alter system kill session '98,21' immediate;                                  

SQL> alter system kill session '15,9' immediate;

System altered.

SQL> alter system kill session '50,13' immediate;

System altered.

SQL> alter system kill session '53,3' immediate;

System altered.

SQL> alter system kill session '98,21' immediate;

System altered.
Killed all above four sessions and tried to drop the schema


SQL> drop user drop_user;
drop user drop_user
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

There is a new connection again
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';

Kill string
--------------------------------------------------------------------------------
alter system kill session '15,25' immediate;

Locked the schema account
SQL> alter user drop_user account lock;

User altered.

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';

Kill string
--------------------------------------------------------------------------------
alter system kill session '15,25' immediate;

SQL> alter system kill session '15,25' immediate;

System altered.

SQL> drop user drop_user;

User dropped.

--------------------------------------------------------------------------------