Printable View
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.
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
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
Please post (cut and paste from your SQL*Plus session) the following:
select username from 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
I can see this happening:
Try select username, user_id from dba_users;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
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$; ?
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,
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
Hi Narender,
You can find the duplicates by connecting to the each user , ofcourse a bit tidious job,but no otherway. Here definitely you can't connect for each and every user in the similar fashion, the main descrepancy is using " ". So, for which ever user you are not using " " to connect , is an original one. That's all.
Gurus, please correct me if i'm wrong.
Thanks
Got the solution? here is a bit risky one if its not on a production...
SQL> create user RAJU identified by raju;
User created.
SQL> create user "RAJU " identified by raju;
User created.
So, which ever user connects with a " " and a blank space or double, then its a duplicate one.
Thanks
Dear Friends,
It is not the point that it is creating duplicate users. It is creating duplicate row for each user.
If you create a user it will show the duplicate rows in the dba_users.
where as in sys.user$ it is only one user.
If i check even the sys.user_astatus_map it is showing the duplicate users.
Now can you please help how to remove the duplicate users from dba_users view.
I have one way as follows :
take the full ecport of the production database.
and recreate the database on test server. import the database.
check the consistency report and then take the coldbackup of the test server and restore it on the live server.
can anyone suggest any other method if this not ok. or any modification to the above method.
regards,
naren
Your data dictionary is messed up. There is nothing wrong with your actual data, it's just that dictionary views are broken. Rerun catalog.sql as sys and you'll be fine.
Dear friend,
I have re-run the catalog.sql and catproc.sql on my test server. But no use.
regards,
Naren
malladi,
Please run the following SQL against your database and post the results...
select substr(username||' - '||user_id,1,30) "USERNAME - USERID"
,to_char(created,'YYYY-MM-DD:HH24:MI:SS') "CREATED"
from dba_users
order by username,created
dear alapps,
please find the result of the query which you have asked me to run.
USERNAME - USERID CREATED
------------------------------ -------------------
C - 22 2002-08-19:03:28:47
C - 22 2002-08-19:03:28:47
DBSNMP - 16 2002-08-19:02:57:02
DBSNMP - 16 2002-08-19:02:57:02
ITFX - 18 2002-08-19:03:28:47
ITFX - 18 2002-08-19:03:28:47
ITFXMGR - 44 2002-12-07:11:48:46
ITFXMGR - 44 2002-12-07:11:48:46
ITMSINT - 21 2002-08-19:03:28:47
ITMSINT - 21 2002-08-19:03:28:47
ITMSTEST - 41 2002-10-04:20:43:01
USERNAME - USERID CREATED
------------------------------ -------------------
ITMSTEST - 41 2002-10-04:20:43:01
NRS - 19 2002-08-19:03:28:47
NRS - 19 2002-08-19:03:28:47
OUTLN - 11 2002-08-19:02:48:01
OUTLN - 11 2002-08-19:02:48:01
REDI - 38 2002-08-20:18:02:20
REDI - 38 2002-08-20:18:02:20
RMATCH - 37 2002-08-20:18:01:58
RMATCH - 37 2002-08-20:18:01:58
RNRS - 39 2002-08-20:18:02:31
RNRS - 39 2002-08-20:18:02:31
USERNAME - USERID CREATED
------------------------------ -------------------
SYS - 0 2002-08-19:02:47:46
SYS - 0 2002-08-19:02:47:46
SYSTEM - 5 2002-08-19:02:47:46
SYSTEM - 5 2002-08-19:02:47:46
26 rows selected.
regards,
naren
Did you say the table SYS.USER_ASTATUS_MAP shows duplicate values? Can you post the result of this query?
SELECT * FROM SYS.USER_ASTATUS_MAP;
Apparently this table does not have a primary key, so it can realy accept duplicate rows with the same status#. If you happen to have duplicate rows in there then it is normal your view DBA_USERS returns duplicate rows. If this is the case, you can safely delete duplicates from USER_ASTATUS_MAP.
Naren :Quote:
Dear friend,
I have re-run the catalog.sql and catproc.sql on my test server. But no use.
regards,
Naren
Can u post the text of the view DBA_USERS.
Try creating a test view as under :Code:Select text from dba_views where view_name='DBA_USERS';
Even then if u get duplicate records then yes ur base tables has duplicate records........Code:create or replace view Test_Users
(USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE,
DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE,
INITIAL_RSRC_CONSUMER_GROUP,EXTERNAL_NAME)
as
select u.name, u.user#, u.password,
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(u.astatus,
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.ptime, '', to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name, u.defschclass, u.ext_username
from sys.user$ u, sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
Try checking in the text of the view DBA_USERS, if no redudent data is present in Base table SYS.USER$, then there shud be som kinda query in the view which is creating dupilcate records.....like if u have UNION ALL and so.....
U said u have rerun the catlog.sql & u also tell that SYS.USER$ has UNIQUE record then try checking the catlog.sql script it self if sombody have modified..........
Regards
Abhay.
dear jmodic
please find the result of the query as follows :
select * from sys.user_astatus_map
SQL> /
STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
0 OPEN
1 EXPIRED
STATUS# STATUS
---------- --------------------------------
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED (TIMED)
6 EXPIRED(GRACE) (TIMED)
9 EXPIRED
10 EXPIRED(GRACE)
18 rows selected.
select username, usre_id from dba_users;
1* select username, user_id from dba_users
SQL> /
USERNAME USER_ID
------------------------------ ----------
SYS 0
SYSTEM 5
OUTLN 11
NRS 19
ITMSINT 21
ITFXMGR 44
DBSNMP 16
ITMSTEST 41
SYS 0
SYSTEM 5
OUTLN 11
USERNAME USER_ID
------------------------------ ----------
NRS 19
ITMSINT 21
ITFXMGR 44
DBSNMP 16
ITMSTEST 41
ITFX 18
C 22
RMATCH 37
REDI 38
RNRS 39
ITFX 18
USERNAME USER_ID
------------------------------ ----------
C 22
RMATCH 37
REDI 38
RNRS 39
26 rows selected.
you will find the duplicates in both the tables with.
regards,
Naren
Of course, it makes sence perfectly. FYI, DBA_USERS is not a table, it is a view which among others joins table user$ to table user_astatus_map - so if you have double entries in user_astatus_map it is normal for DBA_USERS to show duplicate users!
Simply delete all duplicate rows from user_astatus_map and DBA_USERS will stop showing duplicate users.
dear jmodic,
I will test on the test server and get back to you.
Regards,
Naren