Oracle-L: Re: c

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 16 Aug 2002 08:23:40 -0800
Message-ID: <F001.004B7CD4.20020816082340@fatcity.com>

RE: c_USER#No, it'll be the same whether it's locally or dictionary managed tablespace.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message ----- From: BigP To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 14, 2002 3:53 PM Subject: 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: Igor Neyman
  INET: ineyman_at_perceptron.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 Fri Aug 16 2002 - 11:23:40 CDT

Re: c_USER#