| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: DBA_USERS Oracle 8.0.5 for Digital UNIX
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)
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,
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)
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
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
While the all_users is correct:
SQLWKS> select * from all_users;
USERNAME USER_ID CREATED
HELP ME! Thanks
Kekko (from Rome - Italy) Received on Thu Jul 08 1999 - 17:00:54 CDT
 |
 |