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 vote208 views1 answer
Full syntax inner join
SQL
0 vote297 views1 answer
Python get backup of sql
SQL
1 vote212 views1 answer
Mysql select utc time in eastern time
SQL
0 vote289 views1 answer
Oracle convert int to date
SQL
34 votes236 views5 answers
Oracle alter table add column
SQL
6 votes220 views1 answer
Update left join mysql
SQL
0 vote186 views0 answer
How to connect to datbase in springboot
SQL
5 votes239 views1 answer
Invalid column name sql
SQL
0 vote218 views1 answer
Create table if not exist with exceptions
SQL
4 votes293 views2 answers
Sql display today's date
SQL