Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 12.13 Table Partitioning Views

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

12.13 Table Partitioning Views

The following Oracle-compatible catalog views provide access to information relating to table partitioning.

12.13.1 ALL_PART_TABLES

The following table lists the information available in the ALL_PART_TABLES view:

Column

Type

Description

owner

name

The owner of the table.

table_name

name

The name of the table.

schema_name

name

The schema in which the table resides.

partitioning_type

text

RANGE or LIST

subpartitioning_type

text

RANGE, LIST or NONE

partition_count

bigint

The number of partitions.

def_subpartition_count

integer

The default subpartition count - this will always be 0.

partitioning_key_count

integer

The number of columns listed in the partition by clause.

subpartitioning_key_count

integer

The number of columns in the subpartition by clause.

status

character varying(8)

Provided for Oracle Compatibility - this column will always be VALID.

def_tablespace_name

character varying(30)

Provided for Oracle Compatibility - this column will always be NULL.

def_pct_free

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_pct_used

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_ini_trans

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_max_trans

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_initial_extent

character varying(40)

Provided for Oracle Compatibility - this column will always be NULL.

def_next_extent

character varying(40)

Provided for Oracle Compatibility - this column will always be NULL.

def_min_extents

character varying(40)

Provided for Oracle Compatibility - this column will always be NULL.

def_max_extents

character varying(40)

Provided for Oracle Compatibility - this column will always be NULL.

def_pct_increase

character varying(40)

Provided for Oracle Compatibility - this column will always be NULL.

def_freelists

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_freelist_groups

numeric

Provided for Oracle Compatibility - this column will always be NULL.

def_logging

character varying(7)

Provided for Oracle Compatibility - this column will always be YES

def_compression

character varying(8)

Provided for Oracle Compatibility - this column will always be NONE

def_buffer_pool

character varying(7)

Provided for Oracle Compatibility - this column will always be DEFAULT

ref_ptn_constraint_name

character varying(30)

Provided for Oracle Compatibility - this column will always be NULL

interval

character varying(1000)

Provided for Oracle Compatibility - this column will always be NULL

12.13.2 ALL_TAB_PARTITIONS

The following table lists the information available in the ALL_TAB_PARTITIONS view:

Column

Type

Description

table_owner

name

The owner of the table.

table_name

name

The name of the table.

schema_name

name

The schema in which the table resides.

composite

text

YES if the table is subpartioned; NO if it is not subpartitioned.

partition_name

name

The name of the partition.

subpartition_count

bigint

The number of subpartitions for this partition.

high_value

text

The partition limit for RANGE partitions, or the partition value for LIST partitions.

high_value_length

integer

The length of high_value.

partition_position

integer

The ordinal position of this partition.

tablespace_name

name

The tablespace in which this partition resides.

pct_free

numeric

Provided for Oracle Compatibility - this column will always be 0.

pct_used

numeric

Provided for Oracle Compatibility - this column will always be 0.

ini_trans

numeric

Provided for Oracle Compatibility - this column will always be 0.

max_trans

numeric

Provided for Oracle Compatibility - this column will always be 0.

initial_extent

numeric

Provided for Oracle Compatibility - this column will always be NULL.

next_extent

numeric

Provided for Oracle Compatibility - this column will always be NULL.

min_extent

numeric

Provided for Oracle Compatibility - this column will always be 0.

max_extent

numeric

Provided for Oracle Compatibility - this column will always be 0.

pct_increase

numeric

Provided for Oracle Compatibility - this column will always be 0.

freelists

numeric

Provided for Oracle Compatibility - this column will always be NULL.

freelist_groups

numeric

Provided for Oracle Compatibility - this column will always be NULL.

logging

character varying(7)

Provided for Oracle Compatibility - this column will always be YES.

compression

character varying(8)

Provided for Oracle Compatibility - this column will always be NONE.

num_rows

numeric

The approx. number of rows in this partition.

blocks

integer

The approx. number of blocks in this partition.

empty_blocks

numeric

Provided for Oracle Compatibility - this column will always be NULL.

avg_space

numeric

Provided for Oracle Compatibility - this column will always be NULL.

chain_cnt

numeric

Provided for Oracle Compatibility - this column will always be NULL.

avg_row_len

numeric

Provided for Oracle Compatibility - this column will always be NULL.

sample_size

numeric

Provided for Oracle Compatibility - this column will always be NULL.

last_analyzed

timestamp without time zone

Provided for Oracle Compatibility - this column will always be NULL.

buffer_pool

character varying(7)

Provided for Oracle Compatibility - this column will always be NULL.

global_stats

character varying(3)

Provided for Oracle Compatibility - this column will always be YES.

user_stats

character varying(3)

Provided for Oracle Compatibility - this column will always be NO.

backing_table

regclass

OID of the backing table for this partition.

12.13.3 ALL_TAB_SUBPARTITIONS

The following table lists the information available in the ALL_TAB_SUBPARTITIONS view:

Column

Type

Description

table_owner

name

The name of the owner of the table.

table_name

name

The name of the table.

schema_name

name

The name of the schema in which the table resides.

partition_name

name

The name of the partition.

high_value

text

The subpartition limit for RANGE subpartitions, or the subpartition value for LIST subpartitions.

high_value_length

integer

The length of high_value.

subpartition_name

name

The name of the subpartition.

subpartition_position

integer

The ordinal position of this subpartition.

tablespace_name

name

The tablespace in which this subpartition resides.

pct_free

numeric

Provided for Oracle Compatibility - this column will always be 0.

pct_used

numeric

Provided for Oracle Compatibility - this column will always be 0.

ini_trans

numeric

Provided for Oracle Compatibility - this column will always be 0.

max_trans

numeric

Provided for Oracle Compatibility - this column will always be 0.

initial_extent

numeric

Provided for Oracle Compatibility - this column will always be NULL.

next_extent

numeric

Provided for Oracle Compatibility - this column will always be NULL.

min_extent

numeric

Provided for Oracle Compatibility - this column will always be 0.

max_extent

numeric

Provided for Oracle Compatibility - this column will always be 0.

pct_increase

numeric

Provided for Oracle Compatibility - this column will always be 0.

freelists

numeric

Provided for Oracle Compatibility - this column will always be NULL.

freelist_groups

numeric

Provided for Oracle Compatibility - this column will always be NULL.

logging

character varying(7)

Provided for Oracle Compatibility - this column will always be YES.

compression

character varying(8)

Provided for Oracle Compatibility - this column will always be NONE.

num_rows

numeric

The approx. number of rows in this subpartition.

blocks

integer

The approx. number of blocks in this subpartition.

empty_blocks

numeric

Provided for Oracle Compatibility - this column will always be NULL.

avg_space

numeric

Provided for Oracle Compatibility - this column will always be NULL.

chain_cnt

numeric

Provided for Oracle Compatibility - this column will always be NULL.

avg_row_len

numeric

Provided for Oracle Compatibility - this column will always be NULL.

sample_size

numeric

Provided for Oracle Compatibility - this column will always be NULL.

last_analyzed

timestamp without time zone

Provided for Oracle Compatibility - this column will always be NULL.

buffer_pool

character varying(7)

Provided for Oracle Compatibility - this column will always be NULL.

global_stats

character varying(3)

Provided for Oracle Compatibility - this column will always be YES.

user_stats

character varying(3)

Provided for Oracle Compatibility - this column will always be NO.

backing_table

regclass

OID of the backing table for this subpartition.

12.13.4 ALL_PART_KEY_COLUMNS

The following table lists the information available in the ALL_PART_KEY_COLUMNS view:

Column

Type

Description

owner

name

The name of the table owner.

name

name

The name of the table.

schema

name

The name of the schema on which the table resides.

object_type

character(5)

Provided for Oracle Compatibility - this column will always be TABLE.

column_name

name

The name of the partitioning key column.

column_position

integer

The position of this column within the partitioning key (the first column has a column position of 1, the second column has a column position of 2...)

12.13.5 ALL_SUBPART_KEY_COLUMNS

The following table lists the information available in the ALL_SUBPART_KEY_COLUMNS view:

Column

Type

Description

owner

name

The name of the table owner.

name

name

The name of the table.

schema

name

The name of the schema on which the table resides.

object_type

character(5)

Provided for Oracle Compatibility - this column will always be TABLE.

column_name

name

The name of the partitioning key column.

column_position

integer

The position of this column within the subpartitioning key (the first column has a column position of 1, the second column has a column position of 2...)

Previous PageTable Of ContentsNext Page