Solved

Unlock user in Oracle10g

Posted on 2012-04-07
25
931 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Query Syntax 6 85
oracle 10G 5 48
Oracle SQL queries -- Challenging question 13 64
query returning everything 11 68
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now