-
duplicate dba_users
hello,
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?
Regards,
Narender Reddy M.
naren
-
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
Very puzzled,
clio_usa - OCP 8/8i/9i DBA
Visit our Web Site
-
duplicate dba_users
dear cliO_usa,
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.
regards,
naren
naren
-
Please post (cut and paste from your SQL*Plus session) the following:
select username from dba_users;
-
duplicate dba_users
dear stecal,
please find the list of users from dba_users table
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
NRS
ITMSINT
ITFXMGR
DBSNMP
ITMSTEST
SYS
SYSTEM
OUTLN
NRS
ITMSINT
ITFXMGR
DBSNMP
ITMSTEST
ITFX
C
RMATCH
REDI
RNRS
ITFX
C
RMATCH
REDI
RNRS
total 26 rows but actual 13 users are only exists.
if I create any user also it is getting created twice.
regards,
Naren
naren
-
I can see this happening:
Code:
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"
-
Hi
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.
I hope this will help in solving the problem...
Thanks,
________________
ShanDJ
-
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> create user raju identified by raju;
User created.
SQL> grant connect to raju;
Grant succeeded.
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
TRACESVR
ESTORE
OAS_PUBLIC
CMS2ADM
WTADMIN
SPOTLIGHT
WVAHOSP
USERNAME
------------------------------
RTSDEMO
IMAGE
ABC
REP
RAJU
16 rows selected.
SQL> create user "raju" identified by "raju"
2 /
User created.
SQL> grant connect to "raju";
Grant succeeded.
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
TRACESVR
ESTORE
OAS_PUBLIC
CMS2ADM
WTADMIN
SPOTLIGHT
WVAHOSP
USERNAME
------------------------------
RTSDEMO
IMAGE
ABC
REP
RAJU
raju
17 rows selected.
SQL> conn raju/raju@hpdevl;
Connected.
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
SQL> create user "raju" identified by raju;
User created.
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
OUTLN
DBSNMP
TRACESVR
ESTORE
OAS_PUBLIC
CMS2ADM
WTADMIN
SPOTLIGHT
WVAHOSP
USERNAME
------------------------------
RTSDEMO
IMAGE
ABC
REP
RAJU
raju
17 rows selected.
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???
Can any one explain me?
Thanks in advance
PNRDBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|