RE: Finding dbs for table
Excellent, 2 months of lurking on this news group and at last a question
I can answer (or at least one that other people have already answered)
I refer you to some previous submissions of the same question:-
`oncheck -pe`, `-pt`, `-pT` etc should get you that info
or from Mark Collins mcollins@us.dhl.com :-
Within isql, run the following:
database sysmaster;
select dbsname, tabname, trunc(start/1048576) chunk
from sysextents
order by 4, 2;
If you know the number of the chunk you're looking for, add "where
trunc(start/1048576) = chknum".
or
from Jake Salomon:-
For non fragmented tables, I ran the following query:
select tabname,
te_extnum,
trunc((te_physaddr / 1048576)) chunk_num,
te_pagenum,
te_size
from systables st, sysmaster:systabextents te
where te.te_partnum = st.partnum
and st.tabid >= 100
order by tabname, te_extnum
Now, if I want to check on a particular chunk number, I can add the
following to the where clause:
and trunc((te_physaddr / 1048576)) = 11
I might even remove the "and tabid >= 100" filter.
Of course, this query tells only which tables in the current database
have pieces in which chunk. A more general version that crosses
database lines might be:
select dbsname,
-- owner,
tabname,
te_extnum,
trunc((te_physaddr / 1048576)) chunk_num
-- te_pagenum,
-- te_size
from sysmaster:systabnames st, sysmaster:systabextents te
where te.te_partnum = st.partnum
order by chunk_num, dbsname, tabname, te_extnum
As I said earlier, this is designed for non-fragmented tables. However,
since each fragment of a table is its own tblspace and has it own
partition number, the above query would still work. I have no
fragmented tables at my site; perhaps someone with fragmented tables can
try the above. Perhaps play with the "order by" to make the association
clear.
Any takers?
--
-- Jake (In pursuit of undomesticated aquatic avians)
>----------
>From: Zeljan Silje[SMTP:zeljan@laus.hr]
>> Hi,
>
>> I am looking for a way to find in what dbspace (or chunk) is particular
>> table.
> Can anybody help me?
Paul Matthews