OraFAQ Forum: Server Administration » droped user in my query result?

Home » RDBMS Server » Server Administration » droped user in my query result?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

droped user in my query result? [message #128300]

Sun, 17 July 2005 08:16

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi

I had droped one user from my oracle database....

but when i run this below query i found that user exist

SQL>select username from dba_users;

but when i try to connect that user i get result

SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied

Now tell me how to remove this user from result of this query
SQL>select username from dba_users;

Thanks

From:-Sunilkumar Mishra

Report message to a moderator

Re: droped user in my query result? [message #128303 is a reply to message #128300]

Sun, 17 July 2005 09:34

dnvs_praveen
Messages: 47
Registered: July 2005
Location: bangalore

Member

This perticular ORA-01017 invalid username/password; login denied will occur in case of username or password authentication failure. In your case you have not dropped the user, that much confirm. your sunil users password you are entering spelling mistake or wrong password. U change the password of the user sunil by giving the following command from sys user

alter user sunil identified by sunil

then connect by sunil user. conn sunil/sunil

Report message to a moderator

Re: droped user in my query result? [message #128310 is a reply to message #128303]

Sun, 17 July 2005 22:25

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi,

Please check out what i am doing ... still the same result
=================================================================
Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production

SQL> alter user sunil identified by sunil;

User altered.

SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
=================================================================

now ---> ceck this what i am doing next
--------------------------------------

SQL> connect sys as sysdba;
Enter password:
Connected.
SQL> purge recyclebin;

Recyclebin purged.

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

SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied

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

Is there is any solution to my problem?
Then please guide me...
Note :- Still Problem is same ....

Thanks

From:- sunilkumar Mishra

Report message to a moderator

Re: droped user in my query result? [message #128314 is a reply to message #128310]

Sun, 17 July 2005 23:14

Barbara Boehmer
Messages: 9095
Registered: November 2002
Location: California, USA

Senior Member

There must be a session where user sunil is still connected. You need to identify that session and kill it, then you can drop user sunil, as demonstrated below.

scott@ORA92> drop user sunil;
drop user sunil
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


scott@ORA92> select sid, serial# from v$session where username = 'SUNIL';

       SID    SERIAL#
---------- ----------
        11         16

scott@ORA92> alter system kill session '11,16';

System altered.

scott@ORA92> drop user sunil;

User dropped.

scott@ORA92> select username from dba_users
  2  where username = 'SUNIL'
  3  /

no rows selected

Report message to a moderator

Re: droped user in my query result? [message #128329 is a reply to message #128314]

Mon, 18 July 2005 01:00

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi

Please check out the below written things... and what i am getting.

SQL> select username from dba_users;

USERNAME
------------------------------
SYSTEM
SYS
IQKIL
IMAX7070
OLAPSYS
JLII737
ORAUSER
SUNIL <-------- still there present
ORDPLUGINS
VALGARDUR
WKPROXY

USERNAME
------------------------------
TEST
XDB
SYSMAN
C1
MOREAIMS
TESTING
MARTINOD
PRES_PROC
DIP
SEVNTDEV
OCPADMIN

USERNAME
------------------------------
MDDATA
WK_TEST
SEVENM
GOODVISION
C2R4
WKSYS
STEFAN
SEVNTPROD
HARYANTO
MAIN_USER
TRIAL_1

USERNAME
------------------------------
SPLEVENT05NET2
DMSYS
RK
EXFSYS
ORDSYS
IQ7070
IQ3131
IMAX3131
LEMMEL
SI_INFORMTN_SCHEMA
MGMT_VIEW

USERNAME
------------------------------
IQPOS
SPLEVENT05NET
KIL
OUTLN
ANONYMOUS
CTXSYS
IQ
JOELRODRIGUES
ATUL
EMESSAGE
DEV

USERNAME
------------------------------
SIGNEOORACLE
MFG
WMSYS
SCOTT
DBSNMP
MYUSER
RDAHAN
MDSYS
ROY

64 rows selected.
----------------------------------------------------------------
SQL> drop user sunil;
drop user sunil
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

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

no rows selected

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

SQL> select sid, serial#, username from v$session;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
240 5647
242 5540 IQKIL
244 2233
248 6726 IQKIL
250 4896 IQKIL
251 1424 IQKIL
254 1675 SYS
257 3725
259 8982 IQKIL
260 2608
261 6210 SYSTEM

SID SERIAL# USERNAME
---------- ---------- ------------------------------
267 1
269 1
270 7
271 39327 SYSMAN
272 7899 SYS
273 1
274 1
275 1
276 1
277 1
278 1

SID SERIAL# USERNAME
---------- ---------- ------------------------------
279 1
280 1

24 rows selected.

Note :- I am Not able to see sunil user in above query.

Now tell me what to do ? Cool

to remove this sunil from result of my query SQL> Select username from dba_usernames;

The issue is not yet solved so still waiting for the answer.

thanks

From :- Sunilkumar Mishra

Report message to a moderator

Re: droped user in my query result? [message #128344 is a reply to message #128329]

Mon, 18 July 2005 02:21

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Hi,

I think you update the statistics for the database cause i think the statistics are not updated and the data dictionary is not having the current status of the database.

First, perform a clean shutdown if you can and then update the statistics of your database through DBMS_STATS package.

I think this can resolve your problem though I am not sure.

Milind.

Report message to a moderator

Re: droped user in my query result? [message #128350 is a reply to message #128344]

Mon, 18 July 2005 02:35

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi

Milind thanks for your reply .. as my database is running 24x7 so difficult to shutdown .. even i was knowing that after shuting down the database and starting it again every thing will be fine ... but is there is any other solution to this without shuting down the database.

Thanks

From :- Sunilkumar Mishra

[Updated on: Mon, 18 July 2005 02:36]

Report message to a moderator

Re: droped user in my query result? [message #128354 is a reply to message #128350]

Mon, 18 July 2005 02:52

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Hi,

What you can try on this is collect the database stats through DBMS_STATS package without shutting down the db.

I think once the statistics will be updated your problem will get resolved.

Please let me know if your problem gets resolved after doing this.

Milind.

Report message to a moderator

Re: droped user in my query result? [message #128402 is a reply to message #128300]

Mon, 18 July 2005 06:47

Frank
Messages: 7901
Registered: March 2000

Senior Member

I fail to see how gathering statistics can influence the data displayed by a query...

Report message to a moderator

Re: droped user in my query result? [message #128407 is a reply to message #128402]

Mon, 18 July 2005 06:53

milind_sri
Messages: 70
Registered: February 2005
Location: Pune

Member

Hi Frank,

I have already said in my previous post that I am not sure about this. But, I have seen practically once or twice Oracle does not recognise Create table or Drop table or Drop user commands until statistics are updated as the data dictionary tables are not having the recent changes.

That is why I told Sunil to give this a try.

Milind.

Report message to a moderator

Re: droped user in my query result? [message #459091 is a reply to message #128310]

Thu, 03 June 2010 06:04

yasirshakil
Messages: 2
Registered: June 2010
Location: Karachi, Pakistan

Junior Member

first connect to sysdab

SQL>conn / as sysdba
then type

SQL>drop user sunil cascade;

then user will remove from data base
then connect to sunil

SQL>conn sunil/sunil

oracle will not allow use to conn to sunil/sunil means your user is deleted

Report message to a moderator

Re: droped user in my query result? [message #459107 is a reply to message #459091]

Thu, 03 June 2010 07:14

cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) You do realise this thread is 5 years old?
2) The problem the OP was having is that he got an error when he tried to drop the user, you've posted nothing that would help there.

Report message to a moderator

Previous Topic:	MAINTAINING PARTITIONS
Next Topic:	Oracle 11g installation error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 13:22:36 CDT 2024