Re: DBA_USERS Oracle 8.0.5 for Digital UNIX

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 9 Jul 1999 00:00:54 +0200
Message-ID: <931471227.23182.0.pluto.d4ee154e@news.demon.nl>

Now we're talking...
The problem is in the user_astatus_map as this shows all duplicate = primary keys, apparently there's no unique key on the status#. If you have a support contract you should verify with Oracle this is a = bug asap.
This is a new dictionary object, so that probably explains it.

Hth,

Sybrand Bakker, Oracle DBA

Kekko <dini.f_at_adr.it> wrote in message = news:3784DAA2.1D1B98BF_at_adr.it...
This is the creaption script of the view in the subject: create or replace view DBA_USERS

(USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, =
EXPIRY_DATE,

          DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, 
          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, NULL), 
         decode(u.astatus, 
                1, u.exptime, 
                2, u.exptime, 
                5, u.exptime,

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, NULL), 
         decode(u.astatus, 
                1, u.exptime, 
                2, u.exptime, 
                5, u.exptime, 
                6, u.exptime, 
  create or replace view DBA_USERS 


      (USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, LOCK_DATE, =


EXPIRY_DATE, 
          DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, 
          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, 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, '', NULL, 
                  decode(pr.limit#, 2147483647, NULL, 
                   decode(pr.limit#, 0, 
                     decode(dp.limit#, 2147483647, NULL, u.ptime + 
                       dp.limit#/86400), 
                     u.ptime + pr.limit#/86400)))), 
         dts.name, tts.name, u.ctime, p.name, 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

Than these are the contents of the table = user$,profname$,profile$,ts$,user_astatus_map

SQLWKS> select user#,name,type#,datats#,tempts#,ctime,ptime from = user$;

  USER#      NAME                           TYPE#      DATATS#    =
TEMPTS#    CTIME                PTIME 
  ---------- ------------------------------ ---------- ---------- =
---------- -------------------- -------------------- 
           0 SYS                                     1          0        =
  2 16-APR-99            07-MAY-99 
           1 PUBLIC                                  0          0        =
  0 16-APR-99            00-000-00 
           2 CONNECT                                 0          0        =
  0 16-APR-99            00-000-00 
           3 RESOURCE                                0          0        =
  0 16-APR-99            00-000-00 
           4 DBA                                     0          0        =
  0 16-APR-99            00-000-00 
           5 SYSTEM                                  1          3        =
  2 16-APR-99            21-APR-99 
           6 SELECT_CATALOG_ROLE                     0          0        =
  0 16-APR-99            00-000-00 
           7 EXECUTE_CATALOG_ROLE                    0          0        =
  0 16-APR-99            00-000-00 
           8 DELETE_CATALOG_ROLE                     0          0        =
  0 16-APR-99            00-000-00 
          20 CKR_SYSCASE                             0          0        =
  0 16-APR-99            00-000-00 
          21 CED                                     1         11        =
  2 16-APR-99            16-APR-99 
          53 _NEXT_USER                              0          0        =
  0 16-APR-99 
          23 REPO                                    1          7        =
  2 16-APR-99            16-APR-99 
          47 EXP_FULL_DATABASE                       0          0        =
  0 15-JUN-99            00-000-00 
          48 IMP_FULL_DATABASE                       0          0        =
  0 15-JUN-99            00-000-00 
          51 SYSCASE                                 1          9        =
  2 05-JUL-99            05-JUL-99 
          27 CASE001                                 1          9        =
  2 19-APR-99            19-APR-99 
          28 CASE002                                 1          9        =
  2 19-APR-99            19-APR-99 
          29 CED1                                    1         11        =
  2 28-APR-99            28-APR-99 
          42 AQ_ADMINISTRATOR_ROLE                   0          0        =
  0 03-JUN-99            00-000-00 
          43 AQ_USER_ROLE                            0          0        =
  0 03-JUN-99            00-000-00 
          44 SNMPAGENT                               0          0        =
  0 03-JUN-99            00-000-00 
          45 DBSNMP                                  1          0        =
  0 03-JUN-99            03-JUN-99 
          52 DES2000_OWNER                           0          0        =
  0 05-JUL-99            00-000-00 
          49 RECOVERY_CATALOG_OWNER                  0          0        =
  0 15-JUN-99            00-000-00

  25 rows selected.
  SQLWKS> select * from profname$;
  PROFILE# NAME

------------------------------ 0 DEFAULT 1 row selected. SQLWKS> select * from profile$; PROFILE# RESOURCE# TYPE# LIMIT#
---------- ---------- ---------- 0 0 0 2147483647 0 1 0 2147483647 0 2 0 2147483647 0 3 0 2147483647 0 4 0 2147483647 0 5 0 2147483647 0 6 0 2147483647 0 7 0 2147483647 0 8 0 2147483647 0 9 0 0 0 0 1 2147483647 0 1 1 2147483647 0 2 1 2147483647 0 3 1 2147483647 0 4 1 2147483647 0 5 1 2147483647 0 6 1 2147483647 17 rows selected. SQLWKS> select ts#,name,owner#,online$ from ts$; TS# NAME OWNER# ONLINE$
------------------------------ ---------- ---------- 0 SYSTEM 0 1 1 RBS 0 1 2 TEMP 0 1 3 TOOLS 0 1 4 USERS 0 1 5 DESI2001_TSD 0 3 6 DESI2001_TSI 0 3 7 ENTE0001_TSD 0 1 8 INST_TBS_NAME 0 3 9 DES2 0 1 10 DES2_I 0 1 11 CED00001_TSD 0 1 12 EXEPL001_TSD 0 1 13 rows selected. SQLWKS> select * from user_astatus_map; 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 2 EXPIRED(GRACE) 4 LOCKED(TIMED) 8 LOCKED 5 EXPIRED (TIMED) 6 EXPIRED(GRACE) (TIMED) 9 EXPIRED 10 EXPIRED(GRACE) 18 rows selected.

  And this is the problem:
  SQLWKS> select =
username,user_id,account_status,default_tablespace,temporary_tablespace,c= reated from dba_users;
  USERNAME USER_ID ACCOUNT_STATUS DEFAULT_TABLESP = TEMPORARY_TABLE CREATED

---------- --------------- --------------- = --------------- -------------------- SYS 0 OPEN SYSTEM TEMP = 16-APR-99 SYSTEM 5 OPEN TOOLS TEMP = 16-APR-99 CED 21 OPEN CED00001_TSD TEMP = 16-APR-99 REPO 23 OPEN ENTE0001_TSD TEMP = 16-APR-99 SYSCASE 51 OPEN DES2 TEMP = 05-JUL-99 CASE001 27 OPEN DES2 TEMP = 19-APR-99 CASE002 28 OPEN DES2 TEMP = 19-APR-99 CED1 29 OPEN CED00001_TSD TEMP = 28-APR-99 DBSNMP 45 OPEN SYSTEM SYSTEM = 03-JUN-99 SYS 0 OPEN SYSTEM TEMP = 16-APR-99 SYSTEM 5 OPEN TOOLS TEMP = 16-APR-99 CED 21 OPEN CED00001_TSD TEMP = 16-APR-99 REPO 23 OPEN ENTE0001_TSD TEMP = 16-APR-99 SYSCASE 51 OPEN DES2 TEMP = 05-JUL-99 CASE001 27 OPEN DES2 TEMP = 19-APR-99 CASE002 28 OPEN DES2 TEMP = 19-APR-99 CED1 29 OPEN CED00001_TSD TEMP = 28-APR-99 DBSNMP 45 OPEN SYSTEM SYSTEM = 03-JUN-99 18 rows selected.

While the all_users is correct:

SQLWKS> select * from all_users;
USERNAME USER_ID CREATED

---------- -------------------- SYS 0 16-APR-99 SYSTEM 5 16-APR-99 CED 21 16-APR-99 REPO 23 16-APR-99 SYSCASE 51 05-JUL-99 CASE001 27 19-APR-99 CASE002 28 19-APR-99 CED1 29 28-APR-99 DBSNMP 45 03-JUN-99 9 rows selected.

HELP ME! Thanks

Kekko (from Rome - Italy) Received on Thu Jul 08 1999 - 17:00:54 CDT