duplicate dba_users

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.

Quote:

---

Dear friend,

I have re-run the catalog.sql and catproc.sql on my test server. But no use.

regards,

Naren

---

Naren :

Can u post the text of the view DBA_USERS.

Code:

---

Select text from dba_views where view_name='DBA_USERS';

---

Try creating a test view as under :

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

---

Even then if u get duplicate records then yes ur base tables has duplicate records........

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