Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!19g2000hsx.googlegroups.com!not-for-mail From: "fitzjarrell@cox.net" Newsgroups: comp.databases.oracle.server Subject: Re: Query for active tablespaces Date: Wed, 10 Oct 2007 07:40:21 -0700 Organization: http://groups.google.com Lines: 27 Message-ID: <1192027221.744994.184830@19g2000hsx.googlegroups.com> References: NNTP-Posting-Host: 138.32.32.166 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1192027221 20757 127.0.0.1 (10 Oct 2007 14:40:21 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 10 Oct 2007 14:40:21 +0000 (UTC) In-Reply-To: User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ; Embedded Web Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: 19g2000hsx.googlegroups.com; posting-host=138.32.32.166; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:436172 X-Received-Date: Wed, 10 Oct 2007 10:40:21 EDT (text.usenetserver.com) On Oct 10, 9:08 am, Chuck wrote: > 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 Why wouldn't it work with partitioned tables? I'm having no problem returning accurate results in a database containing partitioned tables. David Fitzjarrell