Oracle-L: Re: c

From: BigP <big_planet_2000_at_hotmail.com>
Date: Wed, 14 Aug 2002 11:53:32 -0800
Message-ID: <F001.004B5208.20020814115332@fatcity.com>

RE: c_USER#So do you guys think , locally managed tablespace can get rid of this contention , at present it's dictionary managed tablespace . -bp

Original Message ----- From: Jacques Kilchoer To: 'ORACLE-L_at_fatcity.com' Cc: 'big_planet_2000_at_hotmail.com' Sent: Tuesday, August 13, 2002 3:46 PM Subject: RE: c_USER#

>-----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: BigP
  INET: big_planet_2000_at_hotmail.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 Wed Aug 14 2002 - 14:53:32 CDT

Re: c_USER#