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
