Oracle list partitioned tables

How to see all table partition in oracle

SELECT * FROM ALL_TAB_PARTITIONS;

Oracle list partitioned tables

-- List of partitioned tables
SELECT DISTINCT P.TABLE_OWNER, P.TABLE_NAME, count(*) AS NB_PARTITIONS, 
	SP.NB_SUBPARTITIONS
FROM DBA_TAB_PARTITIONS P
LEFT JOIN (SELECT DISTINCT TABLE_OWNER, TABLE_NAME, count(*) AS NB_SUBPARTITIONS
           FROM DBA_TAB_SUBPARTITIONS
           GROUP BY TABLE_OWNER, TABLE_NAME, PARTITION_NAME
          ) SP
ON P.TABLE_OWNER = SP.TABLE_OWNER AND P.TABLE_NAME = SP.TABLE_NAME
WHERE P.TABLE_OWNER NOT IN ('SYS', 'SYSTEM')
GROUP BY P.TABLE_OWNER, P.TABLE_NAME, SP.NB_SUBPARTITIONS
ORDER BY P.TABLE_OWNER, P.TABLE_NAME;

Similar codes

0 vote155 views1 answer
Implicit inner join table alias
SQL
3 votes147 views1 answer
Sql server version query
SQL
4 votes154 views2 answers
Postgresql default value
SQL
8 votes163 views1 answer
Oracle current date plus 1 month
SQL
1 vote166 views1 answer
Mysql how to truncate table with foreign keys
SQL
0 vote180 views1 answer
Select count concat string sql server
SQL
0 vote155 views1 answer
Sql include rows with 0 values
SQL
32 votes157 views6 answers
Insert into values select
SQL
2 votes150 views2 answers
Mysql get time from datetime
SQL
0 vote160 views3 answers
Sql query checker
SQL