Re: Tables on a particular chunk.
Viegas, John Paul wrote:
>
> I'm looking for a query which will list all the tables which spill
> over onto a particular chunk. I am able to list all tables on that
> dbspace, but this dbspace has two chunks to it, and I want to know
> which tables are on the second chunk. Any help would be greatly
> appreciated.
Cosmo replied:
> `oncheck -pe`, `-pt`, `-pT` etc should get you that info.
Jake here:
oncheck -pe can be filtered to tell us what tables sit in which dbspace.I don't see how I can easily get the chunk number containing which part
of the table.
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)
+---------------------------------------------------------------+
|Insofar as manifestations of functional deficiencies are agreed|
|by any and all concerned parties to be imperceivable, and are |
|so stipulated, it is incumbent upon said heretofore mentioned |
|parties to exercise the deferment of otherwise pertinent |
|maintenance procedures. |
+------------------- A Legal Minded Engineer (hardyharhar.com) -+