Query for active tablespaces

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Wed, 10 Oct 2007 14:08:11 GMT
Message-ID: <f15Pi.5881$j14.5857@trnddc06>

Does anyone have a query to show which tablespaces are currently being used by a session? What I'm using now is pretty basic and I don't think it will work if any of the segments are partitioned.

The oracle version is 9.2. Here's the query I'm using now.

SELECT DISTINCT s.tablespace_name

           FROM v$access a, dba_segments s, v$session sx
          WHERE


/* joins */

                s.owner = a.owner
            AND s.segment_name = a.OBJECT
            AND s.segment_type = a.TYPE
            AND a.SID = sx.SID


/* filters */

            AND sx.status = 'ACTIVE';

TIA Received on Wed Oct 10 2007 - 09:08:11 CDT