Site icon IT Tutorial

How to List and Analyze Partitions of Table Order by High Value Column in Oracle Database

I will explain How to List and Analyze Table Partitions Order by High Value Column in Oracle Database in this post. let’s review the List and Analyze Table Partitions in Oracle.

 

List and Analyze Table Partitions in Oracle

 

If you want to learn more details about Partitioning, read the following article.

Partitioning in Oracle Database

 

 

List Table Partitions in Oracle

You can list all partition-level partitioning informations, partition storage parameters, and partition statistics using dba_tab_partitions views.

 

SQL> select * from dba_tab_partitions;

 

dba_tab_partitions views’ columns are as follows.

 

 

SQL> desc dba_tab_partitions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 COMPOSITE                                          VARCHAR2(3)
 PARTITION_NAME                                     VARCHAR2(128)
 SUBPARTITION_COUNT                                 NUMBER
 HIGH_VALUE                                         LONG
 HIGH_VALUE_LENGTH                                  NUMBER
 PARTITION_POSITION                                 NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENT                                         NUMBER
 MAX_EXTENT                                         NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(7)
 COMPRESSION                                        VARCHAR2(8)
 COMPRESS_FOR                                       VARCHAR2(30)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 IS_NESTED                                          VARCHAR2(3)
 PARENT_TABLE_PARTITION                             VARCHAR2(128)
 INTERVAL                                           VARCHAR2(3)
 SEGMENT_CREATED                                    VARCHAR2(4)
 INDEXING                                           VARCHAR2(4)
 READ_ONLY                                          VARCHAR2(4)
 INMEMORY                                           VARCHAR2(8)
 INMEMORY_PRIORITY                                  VARCHAR2(8)
 INMEMORY_DISTRIBUTE                                VARCHAR2(15)
 INMEMORY_COMPRESSION                               VARCHAR2(17)
 INMEMORY_DUPLICATE                                 VARCHAR2(13)
 CELLMEMORY                                         VARCHAR2(24)
 INMEMORY_SERVICE                                   VARCHAR2(12)
 INMEMORY_SERVICE_NAME                              VARCHAR2(1000)
 MEMOPTIMIZE_READ                                   VARCHAR2(8)
 MEMOPTIMIZE_WRITE                                  VARCHAR2(8)

SQL>

 

You can display object-level partitioning information for the partitioned tables accessible to the current user using ALL_PART_TABLES view.

SQL> select * from all_part_tables;

 

all_part_tables views’ columns are as follows.

 

SQL> DESC ALL_PART_TABLES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 PARTITIONING_TYPE                                  VARCHAR2(9)
 SUBPARTITIONING_TYPE                               VARCHAR2(9)
 PARTITION_COUNT                                    NUMBER
 DEF_SUBPARTITION_COUNT                             NUMBER
 PARTITIONING_KEY_COUNT                             NUMBER
 SUBPARTITIONING_KEY_COUNT                          NUMBER
 STATUS                                             VARCHAR2(8)
 DEF_TABLESPACE_NAME                                VARCHAR2(30)
 DEF_PCT_FREE                                       NUMBER
 DEF_PCT_USED                                       NUMBER
 DEF_INI_TRANS                                      NUMBER
 DEF_MAX_TRANS                                      NUMBER
 DEF_INITIAL_EXTENT                                 VARCHAR2(40)
 DEF_NEXT_EXTENT                                    VARCHAR2(40)
 DEF_MIN_EXTENTS                                    VARCHAR2(40)
 DEF_MAX_EXTENTS                                    VARCHAR2(40)
 DEF_MAX_SIZE                                       VARCHAR2(40)
 DEF_PCT_INCREASE                                   VARCHAR2(40)
 DEF_FREELISTS                                      NUMBER
 DEF_FREELIST_GROUPS                                NUMBER
 DEF_LOGGING                                        VARCHAR2(7)
 DEF_COMPRESSION                                    VARCHAR2(8)
 DEF_COMPRESS_FOR                                   VARCHAR2(30)
 DEF_BUFFER_POOL                                    VARCHAR2(7)
 DEF_FLASH_CACHE                                    VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                               VARCHAR2(7)
 REF_PTN_CONSTRAINT_NAME                            VARCHAR2(128)
 INTERVAL                                           VARCHAR2(1000)
 AUTOLIST                                           VARCHAR2(3)
 INTERVAL_SUBPARTITION                              VARCHAR2(1000)
 AUTOLIST_SUBPARTITION                              VARCHAR2(3)
 IS_NESTED                                          VARCHAR2(3)
 DEF_SEGMENT_CREATION                               VARCHAR2(4)
 DEF_INDEXING                                       VARCHAR2(3)
 DEF_INMEMORY                                       VARCHAR2(8)
 DEF_INMEMORY_PRIORITY                              VARCHAR2(8)
 DEF_INMEMORY_DISTRIBUTE                            VARCHAR2(15)
 DEF_INMEMORY_COMPRESSION                           VARCHAR2(17)
 DEF_INMEMORY_DUPLICATE                             VARCHAR2(13)
 DEF_READ_ONLY                                      VARCHAR2(3)
 DEF_CELLMEMORY                                     VARCHAR2(24)
 DEF_INMEMORY_SERVICE                               VARCHAR2(12)
 DEF_INMEMORY_SERVICE_NAME                          VARCHAR2(1000)

SQL>

 

You can list partitions according to partitioning type and schema name as follows.

select * from ALL_PART_TABLES where partitioning_type='RANGE' and owner='SCHEMA_NAME' and interval is null;

select * from ALL_PART_TABLES where partitioning_type='REFERENCE' and owner='SCHEMA_NAME';

 

If you want to learn more details about Partitioning types , read the following article.

Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database

 

 

You can display subpartitions name, name of the table and partition to which it belongs, and its storage attributes using ALL_TAB_SUBPARTITIONS view.

SQL> select * from all_tab_subpartitions;

 

all_part_tables views’ columns are as follows.

SQL> DESC ALL_TAB_SUBPARTITIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                               NOT NULL VARCHAR2(128)
 TABLE_NAME                                NOT NULL VARCHAR2(128)
 PARTITION_NAME                                     VARCHAR2(128)
 SUBPARTITION_NAME                                  VARCHAR2(128)
 HIGH_VALUE                                         LONG
 HIGH_VALUE_LENGTH                         NOT NULL NUMBER
 PARTITION_POSITION                                 NUMBER
 SUBPARTITION_POSITION                              NUMBER
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 PCT_FREE                                  NOT NULL NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                 NOT NULL NUMBER
 MAX_TRANS                                 NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENT                                         NUMBER
 MAX_EXTENT                                         NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 COMPRESSION                                        VARCHAR2(8)
 COMPRESS_FOR                                       VARCHAR2(30)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 INTERVAL                                           VARCHAR2(3)
 SEGMENT_CREATED                                    VARCHAR2(3)
 INDEXING                                           VARCHAR2(3)
 READ_ONLY                                          VARCHAR2(3)
 INMEMORY                                           VARCHAR2(8)
 INMEMORY_PRIORITY                                  VARCHAR2(8)
 INMEMORY_DISTRIBUTE                                VARCHAR2(15)
 INMEMORY_COMPRESSION                               VARCHAR2(17)
 INMEMORY_DUPLICATE                                 VARCHAR2(13)
 INMEMORY_SERVICE                                   VARCHAR2(12)
 INMEMORY_SERVICE_NAME                              VARCHAR2(1000)
 CELLMEMORY                                         VARCHAR2(24)
 MEMOPTIMIZE_READ                                   VARCHAR2(8)
 MEMOPTIMIZE_WRITE                                  VARCHAR2(8)

SQL>

 

 

 

You can List Partitions of any Table Order by High Value Column as follows.

SELECT * from (
SELECT PARTITION_NAME,
extractvalue
( dbms_xmlgen.getxmltype
( 'select high_value
from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
'//text()' ) as high_value 
FROM DBA_TAB_PARTITIONS t
WHERE TABLE_NAME = 'TABLE_NAME')
order by high_value desc;

 

Script output is as follows.

 

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

Exit mobile version