Allows a security administrator or a DBA to display partition information (meta data) about the primary database. It returns a result set that is ordered by a particular column based on the type of statement. The columns are determined by the options specified.

The SHOW PARTITION FULL statement also specifies if a partition is a read-only partition or a read-write partition. The READ_ONLY column with YES as its value indicates that the partition is marked as a read-only partition. With NO as its value, the READ_ONLY column indicates that the partition is marked as a read-write partition.

The SHOW PARTITION statement uses the following syntax:

Syntax

SHOW { ALL PARTITION | PARTITION FOR partition_name }
 [table_option]
 [show_options]
 [ COLUMN SIZE size]

Parameters

table_option

Uses the following syntax:

FOR TABLE table_name
FOR TABLE table_name

Returns information about all the partitions defined for the specified table.

show_options

Uses the following syntax:

FULL | PRO_NAME | TABLE | PRO_AREA_NAME
FULL

Returns all the information about all the partitions defined for the specified table.

PRO_NAME

Returns only the names of the partitions defined for the specified table.

PRO_AREA_NAME

Returns information about the names of the storage areas used for each partition. It does not return any information about the partition key columns.

Examples

The following example returns the basic information about all the partitions in the database:

SHOW ALL PARTITION

The following example returns all the partition names defined in the database:

SHOW ALL PARTITION PRO_NAME;

The following example returns all the information about all partitions defined in the database:

SHOW ALL PARTITION FULL;

The following example returns the basic information about all the partitions defined on the table Pub.Customer:

SHOW ALL PARTITION FOR TABLE Pub.Customer;

The following example returns all the information about all partitions created on the table Pub.Customer. It also limits the column values in the result set to not more than 30 characters.

SHOW ALL PARTITION FOR TABLE Pub.Customer FULL COLUMN SIZE 3;

The following example returns a list of partitioned tables:

SHOW ALL PARTITION TABLE

The following example returns information about partitions for table Pub.tpcustomer including names of the storage areas containing data, indexes, and LOB columns:

SHOW ALL PARTITION FOR TABLE Pub.tpcustomer PRO_AREA_NAME