Solved

Unlock user in Oracle10g

Posted on 2012-04-07
25
978 Views
Last Modified: 2012-05-01
How can unlock users in oracle10g?Tell me various possible ways?
0
Comment
Question by:vpkamble_400
  • 8
  • 4
  • 3
  • +4
25 Comments
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37819353
ones you login with sys account put below command.
alter user username account unlock;
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37819360
0
 
LVL 1

Expert Comment

by:Faher
ID: 37819427
Lets suppose you have a user HR which account is locked, you can unlock that user by following command.

NOte: you need to connect to oracle database via sqlplus using system or sys user

alter user hr account unlock;

or

alter user hr identified by password account unlock; (using this command you are setting new password for that user and unlocking its account
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vpkamble_400
ID: 37819485
when i run command  "alter user sysdba account unlock;"
 it give me error
"SP2-0734: unknown command beginning "lter user ..." - rest of line ignored.
SQL>  alter user sysdba account unlock;
 alter user sysdba account unlock
*
ERROR at line 1:
ORA-01031: insufficient privileges"
0
 
LVL 1

Accepted Solution

by:
Faher earned 350 total points
ID: 37819543
sysdba is not any account its privileges. sys user has sysdba pribileges only and this account can not be locked or unlocked as this user is always unlocked because this is super user like root in unix and administrator in windows environment

which user you want to unlock ?
first check which user account account is locked by executing below query

select username from dba_users where account_status='LOCKED';

if there is any user whos account is locked you can unlock that user by executing below command. (lets suppor user abc account is locked)

alter user abc account unlock;


NOTE: Above all command will be executed by sys or system user .

you can check current user by typing command show user on sqlplus
0
 

Author Comment

by:vpkamble_400
ID: 37821069
i am unable to login in sysdba.In user name i type sysdba password sysdba and hotstring is left blank then i am unable to login.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37821072
- sysdba is not a user. you can login as a SYS user using sysdba privileges as follows in command prompt:

sqlplus sys/<password> as sysdba
0
 

Author Comment

by:vpkamble_400
ID: 37821141
i am still unable to do that?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37821172
- can you share the error? it might be related to privileges.

- kindly noted that when using command prompt, you need to run it as Administrator (right click on command prompt icon and choose to run as administrator). also add the OS user you login as, in ORA_DBA group - (on windows) Administrative Tool> Local Users and Groups> Groups> ORA_DBA.

- then try the following:

sqlplus sys/<password>@<youroraclesid> as sysdba
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37821347
Some more information on question will help us to resolve your problem quickly author..

Which OS are you using??

What is the version of oracle(this doesn't matter much, just for information)

By Which user have you logged into your OS??

(If it is sysdba, then you don't have to reset the password), can i know the reason why you want to alter sys privileges?

irrespective of your OS, the command expert OPZaharin suggested will work with a little bit modifications(if you are connected to the same machine where in the database is hosted)

sqlplus "/ as sysdba"

here you can issue any user you want(except sysdba as sysdba is a role not a user)

alter user system identified by "<password>"
0
 

Author Comment

by:vpkamble_400
ID: 37843785
1)I AM USING WIN XP
2)ORACLE 10G
3)I AM LOGGING BY USER SCOTT
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37845211
- scott in default doesnt have sysdba privileged. to login as sysdba you need to use SYS user account. and scott doesnt have the privileged to unlock user. you can login as SYSTEM user to unlock user. in command prompt run the following:

sqlplus system/<thesystempassword>


- then in sqlplus execute unlock user statement. if you want to unlock scott user, replace <username> with scott:
alter user <username> account unlock;
0
 

Author Comment

by:vpkamble_400
ID: 37846541
But dont know the SYSTEM user password
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37849977
then go to command prompt

ie. click on start and click on run and type

sqlplus "/ as sysdba"

after this execute the command

alter user <username> account unlock;
0
 

Expert Comment

by:Sherif_Khider
ID: 37854594
logon to the database using sys as sysdba

then the only way is

alter user <username> account unlock;
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37854648
expert Sherif_Khider.. the comment which you had posted has already been suggested.. please don't post duplicate information...
0
 

Author Comment

by:vpkamble_400
ID: 37856834
how can i do in win7..
0
 

Author Comment

by:vpkamble_400
ID: 37856838
i upgrade my os i am using Win7..
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37857121
OS really has nothing to do with the question.

The alter user syntax above is for sqlplus (or sql developer/toad/???).  What OS is running doens't matter.
0
 

Author Comment

by:vpkamble_400
ID: 37861274
sqlplus "/ as sysdba"
this no working ..
i run command prompt then run this command because in win7 this command not working..
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37861601
Define 'not working'.  Are you getting an error message?   Are you getting "command not found"? what?
0
 

Expert Comment

by:Sherif_Khider
ID: 37861781
Dear expert wasimibm the new information in my post is that is the only way , thanks for your attention
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37876125
shouldn't there be a split here..!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question