Oracle-L: RE: c

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 13 Aug 2002 16:28:32 -0800
Message-ID: <F001.004B3C97.20020813162832@fatcity.com>

Good point!

If the cluster contains also the current disk usage as part of table TSQ$ then this means that Oracle has to keep updating the cluster everytime a user adds or releases disk space, right?

Waleed

-----Original Message-----
Sent: Tuesday, August 13, 2002 7:48 PM
To: Multiple recipients of list ORACLE-L

>-----Original Message-----
>From: BigP [ mailto:big_planet_2000_at_hotmail.com
<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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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 - 19:28:32 CDT

RE: c_USER#