Re: Which tables are in which dbspace?
> >How can we find out which tables are in which dbspaces, with SQL or
> >onstat or whatever!
> >
> >Of course it should be documented somewhere but there's a gap between
> >theory and practise.
> >
> >Using OL 7.30 on SOLARIS 2.6.
>
> SELECT tabname, dbspace
> FROM systables, sysfragments
> WHERE systables.tabid = sysfragments.tabid
> ORDER BY tabname
Assuming that you are looking only for fragmented tables, that would work. If
you are looking also for non-fragmented tables, try:
SELECT t.tabname, d.name
FROM systables t,
sysmaster:sysdbspaces d
WHERE d.dbsnum = trunc(t.partnum / 1048576)
UNION
SELECT t.tabname, f.dbspace
FROM sysfragments f,
systables t
WHERE fragtype = "T"
AND t.tabid = f.tabid
ORDER BY 1;
Mark Collins
mcollins@us.dhl.com
Dilbert is a documentary.