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.
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 )