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: joel garry <joel-garry_at_home.com>
Date: Wed, 17 Oct 2007 18:22:57 -0700
Message-ID: <1192670577.670731.40320@k35g2000prh.googlegroups.com>


On Oct 17, 8:21 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> 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.

See http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm#6884 you can just wait till he's done and not even bother him, unless he keeps blocking you. And even then, perhaps you know enough about the system to understand what is blocking, and make the decision without him.

I can understand you want to be proactive here, but like the others said, there's lots going on, like transactions that will block you even though the objects are not now currently being accessed. Note that trick about selecting from all the tables - you don't want to get caught by things like delayed block cleanout.

jg

--
@home.com is bogus.
Wouldn't believe it in a Die Hard movie.
http://www.ocregister.com/news/home-emami-county-1894171-ellis-system
Received on Wed Oct 17 2007 - 20:22:57 CDT

Original text of this message

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