Any body can help me out how to eliminate the duplicate users from dba_users table.
I have checked the dba_users table and user_astatus_map table in both the tables there are duplicate rows.
In meta link says either remigrate the database or take export of the database and remove the duplicate rows from user_astatus_map.
Is there any other way where i can overcome this problem?
That would be very tricky thing to do. If you have the same usernames in DBA_USERS, even if you try to export - how do you know which of these users you will be getting the data from??
The more interesting thing is how did you do that? Oracle suppose to refuse to create new username is user or role with such name already exists:
SQL> create user system identified by manager;
create user system identified by manager
*
ERROR at line 1:
ORA-01920: user name 'SYSTEM' conflicts with another user or role name
I agree with your point but the other DBA has migrated from 7.3.4 to 8.1.7 few months back. When I have taken charge of the database. I came across this finding. Now tell me do you have any method to remove the duplicates from this table.
SQL> create user "Scott" identified by tiger;
User created.
SQL> select username from dba_users
2 where username like 'S%';
USERNAME
---------
SCOTT
SYS
SYSTEM
Scott
Try select username, user_id from dba_users;
The lower numbers for sys and system (probably like 0 and 5) are the "real" sys and system users.
Aside from the user_id, there is something else different between users whose names appear to be the same.
Do you have duplicate records in only DBA_USERS view or other dictionary tables/views also have similar problem?
What do you get for SELECT * FROM SYS.USER$; ?
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Is this a production database..is this databse very important..how did you end up with this sort of things ?they seem to very strange but very very interesting thing happened.How can we reproduce it ?
if its not so important you can try this simple hack ...be careful use it at your own risk though
seletct rowid,name from sys.user$
delete name from sys.user$
where rowid= rowid-from-above-query
but if this is a production system dont try my suggestion ..
regards
Hrishy
P.S:now i am expecting lotta of criticism for the above post ;-D
As Stecal said the users that are created have difference.
Eventhough both look alike, One of the user would have been created like
Create user "SYS" identified by xxxx;
This "SYS" is entirely different from the other user SYS.
Figure it out by accessing/logging into each user. I think you can not login into both users alike. Try this and let us know ur findings.
Hi Folks,
Can you please let me know how come Malladi got the output with duplicate names in similar case letters?? B'coz, as Stecal said, The duplicate name should be of different case letters, but malladi has got the output of having the duplicates also in the same case as if the original ones.
SQL> conn system/manager@hpdevl;
Connected.
SQL> drop user "raju";
User dropped.
SQL> create user "RAJU" identified by raju;
create user "RAJU" identified by raju
*
ERROR at line 1:
ORA-01920: user name 'RAJU' conflicts with another user or role name
Here it's not accepting me to create with the same case letters as "RAJU" where as its accepting a duplicate as "raju". Then how did he get the duplicate usernames also in the same case???
duplicate dba_users
Reply With Quote
Bookmarks