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

1 vote136 views1 answer
Mysql collation
SQL
38 votes258 views6 answers
Ubuntu install mysql client
SQL
0 vote185 views2 answers
Sql max count
SQL
12 votes176 views4 answers
How to take database dump in postgresql
SQL
3 votes204 views2 answers
Mysql select database
SQL
1 vote225 views1 answer
Sql where max date
SQL
1 vote185 views1 answer
Select not matching data from two tables
SQL
0 vote187 views1 answer
Use where instead of join
SQL
16 votes245 views4 answers
Find duplicates mysql
SQL
0 vote229 views2 answers
How to truncate all table in mysql workbench
SQL