Find out how is table partitioned

Question

Is there any way to find out, whether is table partitioned by Hash, Range or List in Oracle? I could not find such info in metadata tables.

Thanks for help.

Answer 1

You haven't searched metadata tables enough.

Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 
Connected as xxx@yyy

SQL> select owner, table_name, partitioning_type, subpartitioning_type
SQL> from all_part_tables
SQL> where owner = 'SYS'
SQL>     and rownum <= 10
SQL> ;

OWNER                                                                            TABLE_NAME                                                                       PARTITIONING_TYPE SUBPARTITIONING_TYPE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
SYS                                                                              AQ$_SUBSCRIBER_LWM                                                               HASH              NONE
SYS                                                                              WRH$_FILESTATXS                                                                  RANGE             NONE
SYS                                                                              WRH$_SQLSTAT                                                                     RANGE             NONE
SYS                                                                              WRH$_SYSTEM_EVENT                                                                RANGE             NONE
SYS                                                                              WRH$_WAITSTAT                                                                    RANGE             NONE
SYS                                                                              WRH$_LATCH                                                                       RANGE             NONE
SYS                                                                              WRH$_LATCH_CHILDREN                                                              RANGE             NONE
SYS                                                                              WRH$_LATCH_PARENT                                                                RANGE             NONE
SYS                                                                              WRH$_LATCH_MISSES_SUMMARY                                                        RANGE             NONE
SYS                                                                              WRH$_EVENT_HISTOGRAM                                                             RANGE             NONE
10 rows selected

SQL> 

You should be better off with using the user_part_tables instead of all_part_tables, and then please omit the owner = 'SYS' predicate from the where clause.