----- Original Message -----
Sent: Tuesday, August 13, 2002 3:46
PM
Subject: RE: c_USER#
>-----Original Message-----
>From: BigP [mailto:[EMAIL PROTECTED]]
>
>I am finding SYS.C_USER# as
one of the hot blocks .
> I dont find any
documentation about it , can somebody shed
> some
light on it . why it is contention ?
SYS.C_USER# is the name of the cluster that contains the USER$
table (list of usernames in the database) and the TSQ$ table (list of
tablespace quotas for each user). See below for information from the database
showing how those tables are related. Are you adding users / changing
tablespace quotas at an unusual rate?
SQL> select owner, table_name from dba_tables
2 where cluster_owner = 'SYS' and cluster_name =
'C_USER#' ;
OWNER
TABLE_NAME
------------------------------
------------------------------
SYS
USER$
SYS
TSQ$
SQL> set long 6000
SQL> select
view_name, text
2 from dba_views
3 where owner = 'SYS' and view_name in
('DBA_USERS', 'DBA_TS_QUOTAS') ;
VIEW_NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
DBA_TS_QUOTAS
select ts.name,
u.name,
q.blocks
* ts.blocksize,
decode(q.maxblocks, -1, -1, q.maxblocks * ts.blocksize),
q.blocks, q.maxblocks
from sys.tsq$ q, sys.ts$ ts, sys.user$ u
where q.ts# = ts.ts#
and q.user# =
u.user#
and q.maxblocks != 0
DBA_USERS
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
SQL>
