Re: REP: Locally Managed Tablespaces

From: Jared Still <jkstill_at_cybcon.com>
Date: Tue, 24 Apr 2001 18:42:36 -0700
Message-ID: <F001.002F1872.20010424172025@fatcity.com>

On Tuesday 24 April 2001 14:24, Henry Poras wrote:
> Isn't it also in sys.uet$? Oops, that's for dictionary managed, not locally
> managed. You're right. But isn't there a way to see locally managed
> extents?

Yes, they show up in dba_extents, but it doesn't come from sys.uet$.

Here's the view as of 8.1.7. Extents in LMT's are retrieved by the second SELECT statement in the SQL.

select ds.owner, ds.segment_name, ds.partition_name,

        ds.segment_type, ds.tablespace_name, e.ext#, 
        f.file#, e.block#, 
        e.length * ds.blocksize, e.length, e.file#

from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f where e.segfile# = ds.relative_fno

        and e.segblock# = ds.header_block
        and e.ts# = ds.tablespace_id
        and e.ts# = f.ts#
        and e.file# = f.relfile#
        and bitand(NVL(ds.segment_flags,0), 1) = 0

union all -- get the LMT extents
select /*+ ordered use_nl(e) use_nl(f) */

        ds.owner, ds.segment_name, ds.partition_name, 
        ds.segment_type, ds.tablespace_name,
        e.ktfbueextno, f.file#, e.ktfbuebno,
        e.ktfbueblks * ds.blocksize, 
        e.ktfbueblks, e.ktfbuefno

from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f where e.ktfbuesegfno = ds.relative_fno

        and e.ktfbuesegbno = ds.header_block
        and e.ktfbuesegtsn = ds.tablespace_id
        and e.ktfbuesegtsn = f.ts#
        and e.ktfbuefno = f.relfile#
        and bitand(NVL(ds.segment_flags, 0), 1) = 1

/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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 Apr 24 2001 - 20:42:36 CDT