| 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 -> DBA_USERS Oracle 8.0.5 for Digital UNIX
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,
(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
---------- ------------------------------
0 DEFAULT
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.
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.
---------- --------------------------------
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)
And this is the problem:
SQLWKS> select
username,user_id,account_status,default_tablespace,temporary_tablespace,created
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
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
HELP ME! Thanks
Kekko (from Rome - Italy) Received on Thu Jul 08 1999 - 12:06:42 CDT
 |
 |