Path: text.usenetserver.com!out01a.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx02.iad01.newshosting.com!newshosting.com!130.81.64.211.MISMATCH!cycny01.gnilink.net!spamkiller.gnilink.net!gnilink.net!trnddc06.POSTED!c015d11f!not-for-mail
From: Chuck <skilover_nospam@bluebottle.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Query for active tablespaces
X-Enigmail-Version: 0.95.3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Lines: 18
Message-ID: <f15Pi.5881$j14.5857@trnddc06>
Date: Wed, 10 Oct 2007 14:08:11 GMT
NNTP-Posting-Host: 68.238.252.159
X-Complaints-To: abuse@verizon.net
X-Trace: trnddc06 1192025291 68.238.252.159 (Wed, 10 Oct 2007 10:08:11 EDT)
NNTP-Posting-Date: Wed, 10 Oct 2007 10:08:11 EDT
Xref: usenetserver.com comp.databases.oracle.server:436170
X-Received-Date: Wed, 10 Oct 2007 10:08:11 EDT (text.usenetserver.com)

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