Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query for active tablespaces

Re: Query for active tablespaces

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Wed, 17 Oct 2007 15:21:53 GMT
Message-ID: <lMpRi.4818$GR1.636@trnddc08>


gazzag wrote:

> On 10 Oct, 15:08, Chuck <skilover_nos..._at_bluebottle.com> 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
> 
> Erm... why?
> 

Here's one reason. Say I want to move a datafile but do it online with minimal impact. If someone is running selects against it I can still put the tablespace in readonly mode, copy the datafile, then take a few ms to rename it, then delete the original. In such a case it would be useful to know if it's being accessed and especially if an object in it is being changed, so I can coordinate my plans with the user. Received on Wed Oct 17 2007 - 10:21:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US