Home » SQL & PL/SQL » SQL & PL/SQL » Drop user based on userid OR change the username (Oracle 10g windows)
Drop user based on userid OR change the username [message #392032] Mon, 16 March 2009 02:19 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi,all.

inorder to change the schema`s name we have to create one more schema and dump the data to that new schema.drop the previous schema.this is the procedure i know. but today while reading some Faq`s on a website i found this

How to change username

there he did like this

update user$ set <user_name>= <new_user_name> where user_id=<user_id> ;
commit;

but later he found that`s it is not perminent.

i know that no one has the rights to change the dictionary tables.


he changed the password also.

but when he is trying to connect using the new name


Quote:
Hi,
I am changing the name of user in USER$ table.

SQL> select user#,name from user$ where name like 'SCO%';

USER#      NAME
---------- ------------------------------
59         SCOTT
64         SCOTT1

SQL> update user$ set name='HERMESNEW' where user#=64;

1 row updated.

SQL> commit;

Commit complete.

SQL> select user#,name from user$ where name like 'SCO%';

USER#      NAME
---------- ------------------------------
59         SCOTT

SQL> select user#,name from user$ where name like 'HER%';

USER#      NAME
---------- ------------------------------
65         HERMES
64         HERMESNEW

SQL> alter user hermesnew identified by chirag;

User altered.

SQL> conn hermesnew/chirag
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> select username from dba_users;
SP2-0640: Not connected
SQL> conn / as sysdba
Connected.

SQL> select username from dba_users where username like 'HE%';

USERNAME
------------------------------
HERMES


This seems not to be permanent. Which view will help me to change it
permanently?

Regards -- Chirag


the password of that user is changed right? then why its not showing the username when connected as sys?

i know that it`s not a properway.

but asking to know the right way.


and one more.................
is it possible to drop one user using his user_id?

why because if you have to user like one is with capital Alphabet and the other like small alphabet


not only the user any kind of object contain both small and CAPTITAL(MIXED MODE) as its name. then if you try to drop that it simply sho you the error.but still you can access that.

"object object_name does not exist"


and i wil show you that with an example whenever i am at home.

Thanks & regards

Sriram


[Updated on: Mon, 16 March 2009 05:13] by Moderator

Report message to a moderator

Re: Drop user based on userid OR change the username [message #392034 is a reply to message #392032] Mon, 16 March 2009 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Directly updating the dictionary is 1) not supported, 2) not wise, and in this case 3) wrong.
Username is not only in sys.user$ but in many other places.

Regards
Michel
Re: Drop user based on userid OR change the username [message #392039 is a reply to message #392034] Mon, 16 March 2009 02:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@Michel
thanks michel.

But i know thats not supported & wise.
Please read one more time.i choose import & export tools if that happens to me.but the issue is not mine.

What i am asking is how to drop an object if it is multi i mean if it is twice in the DB one like "abc" and another like "ABC". if you drop that it will drop "ABC" not "abc".

Thanks & regards
Sriram
Re: Drop user based on userid OR change the username [message #392040 is a reply to message #392039] Mon, 16 March 2009 02:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use double quotes around the object name to preserve case.
Re: Drop user based on userid OR change the username [message #392044 is a reply to message #392039] Mon, 16 March 2009 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If case is your problem why are you posting this bullshit of updating dictionary?
By the way CODE tags should be used for code.

Regards
Michel
Re: Drop user based on userid OR change the username [message #392073 is a reply to message #392044] Mon, 16 March 2009 04:59 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
(a Chinese proverb) :
"He who asks a question may appear to be a fool, he who does not ask will remain a fool forever" "No one is born genious."




Mr.Michel
Quote:
If case is your problem why are you posting this bullshit of updating dictionary?


Please once again read the post.That's an article published at one website,that's not a code to use code tags.i clearly mentioned that.
mine is just to drop the object using its object_id so is there any way to do like that? and why i posted that bulshit is just for knowing what are base tables participated or involved internally,in that operation(creation of user)
so i posted the content in quote tags.

@frank
thanks for your reply.thank you very much.


thnaks & regards
Sriram.
Re: Drop user based on userid OR change the username [message #392077 is a reply to message #392073] Mon, 16 March 2009 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That's an article published at one website,that's not a code to use code tags

You can use QUOTE and CODE tags if they apply but this is code so at least code tags apply.
You can also post the reference of the article.

Quote:
mine is just to drop the object

So why posting stuff about dropping user and not directly post your question?

Quote:
why i posted that bulshit is just for knowing what are base tables participated or involved internally,in that operation(creation of user)

And what this to do with: "mine is just to drop the object"?
Mixing things in a single post just leads to confusion and in addition you have not the answer you expected as we focus on something else.

Quote:
so i posted the content in quote tags.

And I modified it to make it more readable.

Regards
Michel

[Updated on: Mon, 16 March 2009 05:12]

Report message to a moderator

Re: Drop user based on userid OR change the username [message #392083 is a reply to message #392077] Mon, 16 March 2009 05:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Mon, 16 March 2009 05:12[quote title=Quote:
mine is just to drop the object

So why posting stuff about dropping user and not directly post your question?



Quote:
so i posted the content in quote tags
And I modified it to make it more readable.

Regards
Michel

.


And I Asked:
Please once again read the post.That's an article published at one website,that's not a code to use code tags.i clearly mentioned that.
mine is just to drop the object using its object_id so is there any way to do like that?
not using object_name
Quote:
Mixing things in a single post just leads to confusion and in addition you have not the answer you expected as we focus on something else.


Ok.i will never do like this in future, i will make the future posts as readable. so can you just tell me the answers for both of those?
1) is it possible to drop an object using object_id?if so give me the hint
2) what are the internal process involved while creating user.
(like sys.user$,sys.resource_group_mapping$,sys.user_astatus_map ,sys.profile$)

Thanks & regards
Sriram.
Re: Drop user based on userid OR change the username [message #392087 is a reply to message #392083] Mon, 16 March 2009 05:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramoradba wrote on Mon, 16 March 2009 11:39

2) what are the internal process involved while creating user.
(like sys.user$,sys.resource_group_mapping$,sys.user_astatus_map ,sys.profile$)

You don't need to know. As long as you try to change stuff by updating meta tables, it's safer for you not to know.
Just use the commands that are meant to do (user-)maintenance, don't mess with Oracle's internal tables, or you will very soon break your database beyond repair.
Previous Topic: SCN Number
Next Topic: two-task save area overflow
Goto Forum:
  


Current Time: Fri Dec 09 21:15:23 CST 2016

Total time taken to generate the page: 0.05416 seconds