duplicate dba_users
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: duplicate dba_users

  1. #1
    Join Date
    Jun 2002
    Posts
    22

    Smile 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

  2. #2
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Question

    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

  3. #3
    Join Date
    Jun 2002
    Posts
    22

    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

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Please post (cut and paste from your SQL*Plus session) the following:

    select username from dba_users;

  5. #5
    Join Date
    Jun 2002
    Posts
    22

    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

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    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.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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

  9. #9
    Join Date
    Feb 2002
    Posts
    70
    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

  10. #10
    Join Date
    Apr 2002
    Posts
    291
    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
  •  



Click Here to Expand Forum to Full Width