oncheck -pt and -pT: Display tblspaces for a Table or Fragment

The oncheck -pt and oncheck -pT options print a tblspace report for a specific table or fragment. The only difference between these options is that oncheck -pT prints more information, including some index-specific information.

>>-oncheck------------------------------------------------------>

>----+- -pt-+--database--+-------------------------------------+---><
     '- -pT-'            '-:--+--------+--table--+-----------+-'     
                              '-owner.-'         '-,frag_dbs-'

Table 1. Options of the oncheck -pt and oncheck -pT commands
Element	Purpose	Key Considerations
`database`	Specifies the name of a database that you want to check for consistency	Syntax must conform to the Identifier segment; see Identifier.
`frag_dbs`	Specifies the name of a dbspace that contains a fragment you want to check for consistency	The dbspace must exist and contain the fragment that you want to check for consistency. Syntax must conform to the Identifier segment; see Identifier.
`owner`	Specifies the owner of a table	You must specify the current owner of the table. Syntax must conform to the Owner Name segment; see Owner name.
`table`	Specifies the name of the table that you want to check for consistency	The table must exist. Syntax must conform to the Identifier segment; see Identifier.

The -pt option prints a tblspace report for the table or fragment with the specified name and database. If you do not specify a table, the option displays this information for all tables in the database. The report contains general allocation information, including the maximum row size, the number of keys, the number of extents, their sizes, the pages allocated and used per extent, the current serial value, and the date that the table was created. The -pt output prints the page size of the tblspace, the number of pages (allocated, used, and data) in terms of logical pages.

The TBLspace Flags field shows information about the tblspace configuration, including whether the tblspace is used for Enterprise Replication or time series data.

The Extents fields list the physical address for the tblspace tblspace entry for the table and the address of the first page of the first extent. The extent list shows the number of logical and physical pages in every extent.

The -pT option prints the same information as the -pt option. In addition, the -pT option displays:

Index-specific information
Page-allocation information by page type (for dbspaces)
The number of any compressed rows in a table or table fragment and the percentage of table or table-fragment rows that are compressed
If table or fragment rows are not compressed, the "Compressed Data Summary" section does not appear in the output.

Plan when you want to run the -pT option, because it forces a complete scan of partitions.

Output for both -pt and -pT contains listings for Number of pages used. The value shown in the output for this field is never decremented because the disk space allocated to a tblspace as part of an extent remains dedicated to that extent even after you free space by deleting rows. For an accurate count of the number of pages currently used, see the detailed information about tblspace use (organized by page type) that the -pT option provides.

Example of oncheck -pt Output

The following example shows output of the oncheck -pt command:

TBLspace Report for testdb:tab1

Physical Address               2:10
Creation date                  10/07/2004 17:01:16
TBLspace Flags                 801        Page Locking
                                              TBLspace use 4 bit bit-maps
Maximum row size               14        
Number of special columns      0         
Number of keys                 0         
Number of extents              1         
Current serial value           1         
Pagesize (k)                   4         
First extent size              4         
Next extent size               4         
Number of pages allocated      340       
Number of pages used           337       
Number of data pages           336       
Number of rows                 75806     
Partition partnum              2097154   
Partition lockid               2097154   

Extents                       
       Logical Page     Physical Page        Size Physical Pages
                 0             2:106         340        680

Example of oncheck -pT Output

The following example shows output of the oncheck -pT command:

TBLspace Report for database_a:nilesh.table_1a

                  Table fragment partition dbspace1 in DBspace dbspace1

    Physical Address               3:5
    Creation date                  03/21/2009 15:35:47
    TBLspace Flags                 8000901    Page Locking
                                              TBLspace contains VARCHARS
                                              TBLspace use 4 bit bit-maps
                                              TBLspace is compressed
    Maximum row size               80
    Number of special columns      1
    Number of keys                 0
    Number of extents              1
    Current serial value           100001
    Current SERIAL8 value          1
    Current BIGSERIAL value        1
    Current REFID value            1
    Pagesize (k)                   2
    First extent size              8
    Next extent size               8
    Number of pages allocated      24
    Number of pages used           22
    Number of data pages           14
    Number of rows                 500
    Partition partnum              3145730
    Partition lockid               3145730

    Extents
         Logical Page     Physical Page        Size Physical Pages
                    0           3:16053          24         24

Type                  Pages      Empty  Semi-Full       Full  Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free                      9
    Bit-Map                   1
    Index                     0
    Data (Home)              14
    Data (Remainder)          0          0          0          0          0
                     ----------
    Total Pages              24

    Unused Space Summary

        Unused data bytes in Home pages                1177
        Unused data bytes in Remainder pages              0

    Home Data Page Version Summary

                 Version                                 Count

                       0 (current)                         14

    Compressed Data Summary

    Number of compressed rows and percentage of compressed rows  500 100.00

Note: oncheck -p[tT] now indicates the last time each index fragment was used for a query. This access time is stored on the partition page on disk, it will survive an instance restart.