Oracle-L: RE: c

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 13 Aug 2002 15:48:21 -0800
Message-ID: <F001.004B3C4E.20020813154821@fatcity.com>

>-----Original Message-----
>From: BigP [mailto:big_planet_2000_at_hotmail.com]
>
>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>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Aug 13 2002 - 18:48:21 CDT

RE: c_USER#